MySQL Bugs: #48297: Schema name is ignored when LOAD DATA is written into binlog, replication aborts

Bug #48297	Schema name is ignored when LOAD DATA is written into binlog, replication aborts
Submitted:	26 Oct 3:35	Modified:	11 Nov 16:30
Reporter:	Elena Stepanova
Status:	Closed
Category:	Server: Replication	Severity:	S2 (Serious)
Version:	5.1.40, 5.1.41-bzr, 5.5.0	OS:	Any
Assigned to:	Luís Soares	Target Version:	5.1.41+, 5.0+
Tags:	regression
Triage:	Triaged: D2 (Serious)

Description:
The behavior changed between 5.1.39 and 5.1.40.

With MBR or SBR, if LOAD DATA statement populates a table using a fully qualified table
name, the schema name is not written into the binlog. When the current database is
different from the table's schema, it causes replication failures or populating a wrong
table.

Initial statement (as written in general log):

load data local infile 'infile.txt' into table db1.t

5.1.39 binlog:

load data LOCAL INFILE '/tmp/SQL_LOAD_MB-1-4f' INTO  table db1.t

5.1.40 binlog:

LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-50' INTO TABLE `t` FIELDS TERMINATED BY '\t'
ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (i)

How to repeat:
# rpl_load_infile

--source include/master-slave.inc
--source include/have_binlog_format_mixed_or_statement.inc

--disable_warnings
drop database if exists db1;
drop database if exists db2;
--enable_warnings

create database db1;
create database db2;

use db1;

--exec perl -e 'print "1\n2\n"' > infile.txt

create table t ( i int );

use db2;

load data local infile 'infile.txt' into table db1.t;

--echo # Master:
select * from db1.t;

connection slave;
sleep 1;
--echo # Slave:

use db1;
select * from t;

--vertical_results

show slave status;

# end of rpl_load_infile

Verified just as described with recent 5.1.41 from bzr on Mac OS X.

See possibly-related Bug#48216

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88312

3199 Luis Soares	2009-10-27
      BUG#48297: Schema name is ignored when LOAD DATA is written into 
      binlog, replication aborts
      
      In SBR or MBR, the schema name is not being written to the binlog
      when executing a LOAD DATA statement. This becomes a problem when
      the current database (lets call it db1) is different from the
      table's schema (lets call it db2). For instance, take the
      following statements:
        
        use db1;
        load data local infile 'infile.txt' into table db2.t
      
      Should this statement be logged without t's schema (db2), when
      replaying it, one can get db1.t populated instead of db2.t (if
      db1.t exists). On the other hand, if there is no db1.t at all,
      replication will stop.
      
      We fix this by always logging the table (in load file) with fully
      qualified name when its schema is different from the current
      database or when no default database was selected.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88395

3199 Luis Soares	2009-10-27
      BUG#48297: Schema name is ignored when LOAD DATA is written into 
      binlog, replication aborts
      
      In SBR or MBR, the schema name is not being written to the binlog
      when executing a LOAD DATA statement. This becomes a problem when
      the current database (lets call it db1) is different from the
      table's schema (lets call it db2). For instance, take the
      following statements:
        
        use db1;
        load data local infile 'infile.txt' into table db2.t
      
      Should this statement be logged without t's schema (db2), when
      replaying it, one can get db1.t populated instead of db2.t (if
      db1.t exists). On the other hand, if there is no db1.t at all,
      replication will stop.
      
      We fix this by always logging the table (in load file) with fully
      qualified name when its schema is different from the current
      database or when no default database was selected.

Queued in mysql-5.1-bugteam and mysql-pe.

Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source
revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41)
(pib:13)

Documented bugfix in the 5.1.41 changelog as follows:

        When using statement-based or mixed-format replication, the
        database name was not written to the binary log when executing a
        LOAD DATA statement. This caused problems when the table being
        loaded belonged to a database other than the current database;
        data could be loaded into the wrong table (if a table having the
        same name existed in the current database) or replication could
        fail (if no table having that name existed in the current
        database). Now a table referenced in a LOAD DATA statement is
        logged using its fully qualified name when the database to which
        it belongs is not the current database.

        This issue occurred in MySQL 5.1.40 only.

Also added a note about this (including a workaround) to
http://dev.mysql.com/doc/refman/5.1/en/load-data.html.

Closed.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version
source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha)
(pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version
source revid:alik@sun.com-20091105110316-pn162zoddxj89lbh) (merge vers: 5.5.0-beta)
(pib:13)

Note: Probably doesn't need to be documented in 5.5.0 (first release of series), but what
about 6.0.14?

If so, set back to Documenting and I'll take care of it; otherwise, you can just close
it, noting that no changelog entry was necessary for that version.

Thanks!

Discussed with Luis, added entries to 5.5.0 and 6.0.14 changelogs.