Bug #48297 Schema name is ignored when LOAD DATA is written into binlog, replication aborts

• Submitted: 26 Oct 2009 2:35
• Modified: 15 Mar 2010 4:54
• Reporter: Elena Stepanova
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.1.40, 5.1.41-bzr, 5.5.0
• OS: Any
• Assigned to: Luis Soares
• CPU Architecture: Any
• Tags: regression

[26 Oct 2009 2:35] Elena Stepanova

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

[26 Oct 2009 5:04] Valeriy Kravchuk

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

[26 Oct 2009 17:27] Timothy Smith

See possibly-related Bug#48216

[27 Oct 2009 10:52] Bugs System

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.

[27 Oct 2009 15:16] Bugs System

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.

[2 Nov 2009 16:26] Luis Soares

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

[4 Nov 2009 9:24] Bugs System

Pushed into 5.1.41 (revid:[e-mail]-20091104092152-qz96bzlf2o1japwc) (version source revid:[e-mail]-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)

[4 Nov 2009 17:31] Jon Stephens

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.

[11 Nov 2009 6:49] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091110093407-rw5g8dys2baqkt67) (version source revid:[e-mail]-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)

[11 Nov 2009 6:58] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091109115615-nuohp02h8mdrz8m2) (version source revid:[e-mail]-20091105110316-pn162zoddxj89lbh) (merge vers: 5.5.0-beta) (pib:13)

[11 Nov 2009 15:13] Jon Stephens

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!

[11 Nov 2009 15:30] Jon Stephens

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

[7 Dec 2009 16:47] Paul DuBois

Noted in 5.1.40sp1 changelog.

[8 Dec 2009 9:30] Bugs System

Pushed into 5.1.43 (revid:[e-mail]-20091208092611-pbno5awyb0v38hs7) (version source revid:[e-mail]-20091208092611-pbno5awyb0v38hs7) (merge vers: 5.1.43) (pib:13)

[8 Dec 2009 15:05] Jon Stephens

Already noted in 5.1.41 changelog; re-closing.

[16 Dec 2009 8:35] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091216083311-xorsasf5kopjxshf) (version source revid:[e-mail]-20091215065750-5m04ogppd5l0pol5) (merge vers: 6.0.14-alpha) (pib:14)

[16 Dec 2009 8:42] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091216082430-s0gtzibcgkv4pqul) (version source revid:[e-mail]-20091211070127-kl8uvlrv9cr11kva) (merge vers: 5.5.0-beta) (pib:14)

[16 Dec 2009 8:48] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20091216083231-rp8ecpnvkkbhtb27) (version source revid:[e-mail]-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

[16 Dec 2009 16:05] Jon Stephens

Documented in 5.6.0 changelog; closed.

[18 Dec 2009 10:26] Bugs System

Pushed into 5.1.41-ndb-7.1.0 (revid:[e-mail]-20091218102229-64tk47xonu3dv6r6) (version source revid:[e-mail]-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

[18 Dec 2009 10:42] Bugs System

Pushed into 5.1.41-ndb-6.2.19 (revid:[e-mail]-20091218100224-vtzr0fahhsuhjsmt) (version source revid:[e-mail]-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

[18 Dec 2009 10:57] Bugs System

Pushed into 5.1.41-ndb-6.3.31 (revid:[e-mail]-20091218100616-75d9tek96o6ob6k0) (version source revid:[e-mail]-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

[18 Dec 2009 11:12] Bugs System

Pushed into 5.1.41-ndb-7.0.11 (revid:[e-mail]-20091218101303-ga32mrnr15jsa606) (version source revid:[e-mail]-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

[19 Dec 2009 11:40] Jon Stephens

No additional changelog entries needed. Setting back to Closed state.

[8 Mar 2010 0:05] Paul DuBois

5.6.0 changelog entry unneeded.

[12 Mar 2010 14:06] Bugs System

Pushed into 5.1.44-ndb-7.0.14 (revid:[e-mail]-20100312135944-t0z8s1da2orvl66x) (version source revid:[e-mail]-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

[12 Mar 2010 14:22] Bugs System

Pushed into 5.1.44-ndb-6.2.19 (revid:[e-mail]-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:[e-mail]-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

[12 Mar 2010 14:36] Bugs System

Pushed into 5.1.44-ndb-6.3.33 (revid:[e-mail]-20100312135724-xcw8vw2lu3mijrhn) (version source revid:[e-mail]-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

[15 Mar 2010 4:54] Jon Stephens

No additional changelog entries needed. Setting back to Closed state.