| Bug #48847 | bin logging does not quote column names or removes quotes | ||
|---|---|---|---|
| Submitted: | 17 Nov 2009 18:59 | Modified: | 6 Jan 2010 23:04 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1,mysql-6.0-codebase-bugfixing | OS: | Any |
| Assigned to: | Luis Soares | CPU Architecture: | Any |
[17 Dec 2009 21:44]
Tim Chambers
Bug also present on 5.1.41-log. Master server (5.1.41-log) replicates to slave server (5.1.39-log). Bug does not appear to be present with (5.1.39-log) master server.
[6 Jan 2010 23:04]
Luis Soares
This is a duplicate of BUG#49479. I have run the test provided by Matthias in the original report. I named the test file: rpl_backticks.test . The run I did was based on: tree : mysql-5.1-bugteam revid: dao-gang.qu@sun.com-20091231040419-i5dnn06ahs256qcy timestamp: Thu 2009-12-31 12:04:19 +0800 compiled: ./BUILD/compile-pentium64-debug-max Without the patch for BUG#49479: $ ./mtr --mysqld=--binlog-format=statement rpl_backticks.test [...] rpl.rpl_backticks [ fail ] [...] 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 'decimal)' at line 1' on query. Default database: 'test'. Query: 'LOAD DATA INFILE '../../tmp/SQL_LOAD-2-1-1.data' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (decimal)' [...] Same tree patched with patch for BUG#49479: worker[1] Using MTR_BUILD_THREAD 305, with reserved ports 13050..13059 stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; DROP TABLE IF exists t1; CREATE TABLE t1 AS SELECT CAST(10.0 AS DECIMAL(3)) AS `decimal` ; SELECT * FROM t1 INTO OUTFILE '<outfile>' ; DELETE FROM t1; LOAD DATA INFILE '<outfile>' REPLACE INTO TABLE t1 ; SELECT * FROM t1; decimal 10 DROP TABLE t1; rpl.rpl_backticks [ pass ] 73 ------------------------------------------------------------ The servers were restarted 0 times Spent 0.073 of 5 seconds executing testcases All 1 tests were successful.
Description: My command line: ---------------- ./mysql-test-run --mem --mysqld=--binlog-format=statement rpl_ml10 My script rpl_ml10.test: ------------------------ --source include/master-slave.inc --disable_warnings DROP TABLE IF exists t1; --enable_warnings CREATE TABLE t1 AS SELECT CAST(10.0 AS DECIMAL(3)) AS `decimal` ; let $outfile=$MYSQLTEST_VARDIR/rpl_ml10.out; --error 0,1 remove_file $outfile; --replace_result $outfile <outfile> eval SELECT * FROM t1 INTO OUTFILE '$outfile' ; DELETE FROM t1; --sync_slave_with_master connection master; --replace_result $outfile <outfile> eval LOAD DATA INFILE '$outfile' REPLACE INTO TABLE t1 ; --sync_slave_with_master connection master; SELECT * FROM t1; # Cleanup DROP TABLE t1; remove_file $outfile; Result on mysql-6.0-codebase-bugfixing revno: 3692 2009-10-31 11:36:55 +0300 -------------------------------------- CURRENT_TEST: main.rpl_ml10 ..... === SHOW SLAVE STATUS === ... Last_Errno 1064 Last_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 'decimal)' at line 1' on query. Default database: 'test'. Query: 'LOAD DATA INFILE '../../tmp/SQL_LOAD- 2-1-1.data' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (decimal)' ... ======================== analyze: sync_with_master mysqltest: At line 22: sync_slave_with_master failed: 'select master_pos_wait('master-bin.000001', 701, 300)' returned NULL indicating slave SQL thread failure .... SHOW BINLOG EVENTS IN 'master-bin.000001'; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 4 Format_desc 1 107 Server ver: 6.0.14-alpha-debug-log, Binlog ver: 4 master-bin.000001 107 Query 1 193 use `test`; DROP TABLE IF exists t1 master-bin.000001 193 Query 1 319 use `test`; CREATE TABLE t1 AS SELECT CAST(10.0 AS DECIMAL(3)) AS `decimal` master-bin.000001 319 Query 1 396 use `test`; DELETE FROM t1 master-bin.000001 396 Begin_load_query 1 422 ;file_id=1;block_len=3 master-bin.000001 422 Execute_load_query 1 701 use `test`; LOAD DATA INFILE '/work2/6.0/mysql-6.0-codebase-bugfixing1/mysql-test/var/rpl_ml10.out' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (decimal) ;file_id=1 1. The broken replication is no surprise because the last statement in master.bin does not have backticks around the column name. This can be revealed by checking *without* any replication LOAD DATA INFILE '<outfile>' REPLACE INTO TABLE `t1` (`decimal`) ; versus LOAD DATA INFILE '<outfile>' REPLACE INTO TABLE `t1` (decimal) ; 2. The manual says in http://dev.mysql.com/doc/refman/5.4/en/ replication-features-reserved-words.html that using keywords for identifiers might cause problems if master and slave run with different versions. This is here not the case. It also mentions ... In any SQL statements using these database object names, set the names off using backtick characters (`). ... Well, the binary logging generates the column names etc. 3. Replacing within the script eval LOAD DATA ... REPLACE INTO TABLE t1 ; by eval LOAD DATA ... REPLACE INTO TABLE t1 (`decimal`); does not help because the binary logging removes my backticks. mysql-5.1-bugteam revno: 3200 2009-11-13 shows the same problem. mysql-5.0- bugteam does not have this bug. Workaround: Do not use reserved words for column names. Unfortunately RQG "loves" such coulmn names. How to repeat: See above