| 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: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1, next-mr | OS: | Any |
| Assigned to: | Luis Soares | CPU Architecture: | Any |
| Tags: | regression | ||
[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]
Valeriy 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.
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); }