Bug #49479 Slave stops with syntax error: LOAD DATA event without escaped field names
Submitted: 6 Dec 2009 0:37 Modified: 15 Mar 2010 4:57
Reporter: Luis Soares Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1, next-mr OS:Any
Assigned to: Luis Soares
Tags: regression
Triage: Triaged: D2 (Serious)

[6 Dec 2009 0:37] Luis Soares
Description:
If a table contains a field name that must be escaped (eg, `key`
INT), loading data into that table, using LOAD DATA INFILE, will
break replication.

If logging in mixed or statement mode, the master will binlog a
LOAD DATA event without escaping the field names. Consequently,
the slave will stop with a syntax error:

Last_SQL_Error	Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, c2)' at line 1' on query. Default database: 'test'. Query: 'LOAD DATA INFILE '../../tmp/SQL_LOAD-2-1-1.data' REPLACE INTO  TABLE `t` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (key, c2)'

Examining the master binlog, we find the following event:

LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-7f' REPLACE INTO TABLE `t` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (key, c2)

Notice that the field names are not escaped as they should be,
especially for the "key" field which should have been written as
`key`.

I found this in:

  - tree: mysql-5.1-bugteam
  - revid: davi.arnaut@sun.com-20091204153658-09w7l2edgszt8qe7

Running the test case (see how to repeat) on mtr, reports MySQL version:

  - MySQL Version 5.1.42

How to repeat:
1. bzr clone -r revid:davi.arnaut@sun.com-20091204153658-09w7l2edgszt8qe7 $YOUR_LOCAL_MYSQL_REPO/mysql-5.1-bugteam
2. cd mysql-5.1-bugteam
3. ./BUILD/compile-pentium64-debug-max
4. cd mysql-test
5. create a test case with the following content:

-- source include/master-slave.inc
-- source include/have_binlog_format_mixed_or_statement.inc
CREATE TABLE t (`key` INT, c2 int);

LOAD DATA INFILE '../../std_data/loaddata5.dat' REPLACE INTO TABLE `t`;
DROP TABLE t;

-- sync_slave_with_master

-- exit

6. ./mtr <your_test_file_name>

After this, your test case should fail with the slave sql thread 
exhibiting a syntax error.

Suggested fix:
=== modified file 'sql/sql_load.cc'
--- sql/sql_load.cc	2009-11-28 04:43:16 +0000
+++ sql/sql_load.cc	2009-12-05 02:04:32 +0000
@@ -640,7 +640,11 @@
       if (n++)
         pfields.append(", ");
       if (item->name)
+      {
+        pfields.append("`");
         pfields.append(item->name);
+        pfields.append("`");
+      }
       else
         item->print(&pfields, QT_ORDINARY);
     }
@@ -660,7 +664,9 @@
       val= lv++;
       if (n++)
         pfields.append(", ");
+      pfields.append("`");
       pfields.append(item->name);
+      pfields.append("`");
       pfields.append("=");
       val->print(&pfields, QT_ORDINARY);
     }
[6 Dec 2009 1:10] 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/92988

3247 Luis Soares	2009-12-06
      BUG#49479: Slave stops with syntax error: LOAD DATA event without 
      escaped field names
      
      When in mixed or statement mode, the master logs LOAD DATA
      queries by resorting to an Execute_load_query_log_event. This
      event does not contain the original query, but a rewritten
      version of it, which includes the table field names. However, the
      rewrite does not escape the field names. If these names match a
      reserved keyword, then the slave will stop with a syntax error
      when executing the event.
      
      We fix this by escaping the fields names as it happens already
      for the table name.
     @ mysql-test/extra/rpl_tests/rpl_loaddata.test
        Added test case for the reported bug.
     @ mysql-test/r/mysqlbinlog.result
        Changed result to support escaped field name.
     @ mysql-test/suite/binlog/r/binlog_killed_simulate.result
        Changed result to support escaped field name.
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Changed result to support escaped field name.
     @ mysql-test/suite/binlog/r/binlog_stm_blackhole.result
        Changed result to support escaped field name.
     @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
        Changed result to support escaped field name.
     @ mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result
        Changed result to support escaped field name.
     @ mysql-test/suite/rpl/r/rpl_loaddata.result
        Added result for new test. 
        Changed show slave status positions which are now different because of
        extra escape character in field names.
     @ mysql-test/suite/rpl/r/rpl_loaddata_fatal.result
        Changed show slave status positions which are now different because of
        extra escape character.
     @ mysql-test/suite/rpl/r/rpl_loaddata_map.result
        Changed result to support escaped field name.
     @ mysql-test/suite/rpl/r/rpl_stm_log.result
        Changed result to support escaped field name.
     @ mysql-test/t/mysqlbinlog.test
        Changed positions which is now different because of extra escape 
        character in field names.
     @ sql/sql_load.cc
        Appended escape characters before and after field names.
[7 Dec 2009 13:33] Sveta Smirnova
Thank you for the report.

Verified as described.
[6 Jan 2010 23:05] Luis Soares
BUG#48847 was marked as duplicate of this one.
[7 Jan 2010 17:09] Valerii Kravchuk
Bug #50117 was marked as a duplicate of this one.
[15 Jan 2010 9:00] Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:luis.soares@sun.com-20100107103427-brluy5ifty82u796) (merge vers: 5.1.42) (pib:16)
[19 Jan 2010 10:09] Jon Stephens
Documented bugfix in the 5.1.43 changelog as follows;

        A LOAD DATA INFILE statement that loaded data into a 
        table having a column name that must be escaped (such as 
        `key` INT), caused replication to fail when logging in mixed or 
        statement mode. In such cases, the master wrote the LOAD DATA 
        event to the binary log without escaping the field names.

Set NDI state, waiting for merges to other trees.
[5 Feb 2010 11:49] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100119163614-172adculixyu26j5) (pib:16)
[5 Feb 2010 11:55] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alik@sun.com-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)
[5 Feb 2010 12:01] Bugs System
Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alexey.kopytov@sun.com-20100115112653-e3a24041ag1cv6v3) (merge vers: 5.5.1-m2) (pib:16)
[8 Feb 2010 15:52] Jon Stephens
Also documented bugfix in the 5.5.2 and 6.0.14 changelogs.

Closed.
[12 Mar 2010 14:12] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:28] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:43] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[15 Mar 2010 4:57] Jon Stephens
No additional changelog entries needed. Setting back to Closed state.