From: Tatjana Azundris Nuernberg Date: May 12 2011 4:56am Subject: bzr commit into mysql-5.5-bugteam branch (tatjana.nuernberg:3229) Bug#60580 Bug#11902767 List-Archive: http://lists.mysql.com/commits/137161 X-Bug: 60580,11902767 Message-Id: <201105120456.p4C4uwW0024812@acsmt358.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============8564918581198826240==" --===============8564918581198826240== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///Users/tnurnberg/forest/11902767_/55-11902767_/ based on revid:dao-gang.qu@stripped 3229 Tatjana Azundris Nuernberg 2011-05-12 Bug#11902767/Bug#60580: Statement improperly replicated crashes slave SQL thread If LOAD DATA INFILE featured a SET clause, the name=value pairs would be regenerated using item::print. Unfortunately, that code is mostly optimized for EXPLAIN EXTENDED output and such, and can not be relied on to return valid SQL. We now name each value its original, user-supplied form and use that to create LOAD DATA INFILE statements for statement-based replication. @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result minor change in syntactic sugar @ mysql-test/suite/rpl/r/rpl_loaddatalocal.result add test case @ mysql-test/suite/rpl/t/rpl_loaddatalocal.test add test case @ sql/sql_load.cc Do not try to item::print values in LOAD DATA INFILE's SET clause; they might not even be valid SQL at this point. Use our saved version instead. @ sql/sql_yacc.yy If LOAD DATA INFILE has SET name=val clauses, tag the individual val-parts with the user's version so we can later replicate that, rather than the smashed pieces we'd get from item::print once the optimizer's through with our poor values. modified: mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result mysql-test/suite/rpl/r/rpl_loaddatalocal.result mysql-test/suite/rpl/t/rpl_loaddatalocal.test sql/sql_load.cc sql/sql_yacc.yy === modified file 'mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result' --- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result 2010-09-17 20:22:34 +0000 +++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result 2011-05-12 04:56:41 +0000 @@ -698,7 +698,7 @@ master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Intvar # # INSERT_ID=10 -master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @b) SET `b`=((@b) + `bug27417`(2)) ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @b) SET `b`= @b + bug27417(2) ;file_id=# master-bin.000001 # Query # # ROLLBACK /* the output must denote there is the query */; drop trigger trg_del_t2; @@ -950,7 +950,7 @@ master-bin.000001 # User var # # @`b`=_l master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci -master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @b) SET `b`=((@b) + `bug27417`(2)) ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @b) SET `b`= @b + bug27417(2) ;file_id=# master-bin.000001 # Query # # ROLLBACK drop trigger trg_del_t2; drop table t1,t2,t3,t4,t5; === modified file 'mysql-test/suite/rpl/r/rpl_loaddatalocal.result' --- a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result 2010-12-19 17:07:28 +0000 +++ b/mysql-test/suite/rpl/r/rpl_loaddatalocal.result 2011-05-12 04:56:41 +0000 @@ -78,4 +78,31 @@ LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/b DROP TABLE t1; SET SESSION sql_mode=@old_mode; [slave] + +Bug #60580/#11902767: +"statement improperly replicated crashes slave sql thread" + +[master] +CREATE TABLE t1(f1 INT, f2 INT); +CREATE TABLE t2(f1 INT, f2 TIMESTAMP); +INSERT INTO t2 VALUES(1, '2011-03-22 21:01:28'); +INSERT INTO t2 VALUES(2, '2011-03-21 21:01:28'); +INSERT INTO t2 VALUES(3, '2011-03-20 21:01:28'); +CREATE TABLE t3 AS SELECT * FROM t2; +CREATE VIEW v1 AS SELECT * FROM t2 +WHERE f1 IN (SELECT f1 FROM t3 WHERE (t3.f2 IS NULL)); +SELECT 1 INTO OUTFILE 'MYSQLD_DATADIR/bug60580.csv' FROM DUAL; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug60580.csv' INTO TABLE t1 (@f1) SET f2 = (SELECT f1 FROM v1 WHERE f1=@f1); +SELECT * FROM t1; +f1 f2 +NULL NULL +[slave] +SELECT * FROM t1; +f1 f2 +NULL NULL +[master] +DROP VIEW v1; +DROP TABLE t1, t2, t3; +[slave] include/rpl_end.inc +# End of 5.1 tests === modified file 'mysql-test/suite/rpl/t/rpl_loaddatalocal.test' --- a/mysql-test/suite/rpl/t/rpl_loaddatalocal.test 2010-12-19 17:15:12 +0000 +++ b/mysql-test/suite/rpl/t/rpl_loaddatalocal.test 2011-05-12 04:56:41 +0000 @@ -164,4 +164,55 @@ SET SESSION sql_mode=@old_mode; sync_slave_with_master; connection master; + +--echo +--echo Bug #60580/#11902767: +--echo "statement improperly replicated crashes slave sql thread" +--echo + +--echo [master] +connection master; +let $MYSQLD_DATADIR= `select @@datadir`; + +CREATE TABLE t1(f1 INT, f2 INT); +CREATE TABLE t2(f1 INT, f2 TIMESTAMP); + +INSERT INTO t2 VALUES(1, '2011-03-22 21:01:28'); +INSERT INTO t2 VALUES(2, '2011-03-21 21:01:28'); +INSERT INTO t2 VALUES(3, '2011-03-20 21:01:28'); + +CREATE TABLE t3 AS SELECT * FROM t2; + +CREATE VIEW v1 AS SELECT * FROM t2 + WHERE f1 IN (SELECT f1 FROM t3 WHERE (t3.f2 IS NULL)); + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval SELECT 1 INTO OUTFILE '$MYSQLD_DATADIR/bug60580.csv' FROM DUAL; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug60580.csv' INTO TABLE t1 (@f1) SET f2 = (SELECT f1 FROM v1 WHERE f1=@f1); + +SELECT * FROM t1; + +sleep 1; + +--echo [slave] +sync_slave_with_master; + +SELECT * FROM t1; + +--remove_file $MYSQLD_DATADIR/bug60580.csv + +--echo [master] +connection master; + +DROP VIEW v1; +DROP TABLE t1, t2, t3; + +--echo [slave] +sync_slave_with_master; + +connection master; --source include/rpl_end.inc + +--echo # End of 5.1 tests === modified file 'sql/sql_load.cc' --- a/sql/sql_load.cc 2010-12-07 16:11:13 +0000 +++ b/sql/sql_load.cc 2011-05-12 04:56:41 +0000 @@ -1,4 +1,4 @@ -/* Copyright (c) 2000, 2010 Oracle and/or its affiliates. All rights reserved. +/* Copyright (c) 2000, 2011 Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -743,8 +743,7 @@ static bool write_execute_load_query_log pfields.append("`"); pfields.append(item->name); pfields.append("`"); - pfields.append("="); - val->print(&pfields, QT_ORDINARY); + pfields.append(val->name); } } === modified file 'sql/sql_yacc.yy' --- a/sql/sql_yacc.yy 2010-11-11 17:11:05 +0000 +++ b/sql/sql_yacc.yy 2011-05-12 04:56:41 +0000 @@ -1,4 +1,4 @@ -/* Copyright (c) 2000, 2010 Oracle and/or its affiliates. All rights reserved. +/* Copyright (c) 2000, 2011 Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -11587,7 +11587,23 @@ field_or_var: opt_load_data_set_spec: /* empty */ {} - | SET insert_update_list {} + | SET load_data_set_list {} + ; + +load_data_set_list: + load_data_set_list ',' load_data_set_elem + | load_data_set_elem + ; + +load_data_set_elem: + simple_ident_nospvar equal remember_name expr_or_default remember_end + { + LEX *lex= Lex; + if (lex->update_list.push_back($1) || + lex->value_list.push_back($4)) + MYSQL_YYABORT; + $4->set_name($3, (uint) ($5 - $3), YYTHD->charset()); + } ; /* Common definitions */ --===============8564918581198826240== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/tatjana.nuernberg@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: tatjana.nuernberg@stripped\ # vtbfeg9nps96sraf # target_branch: file:///Users/tnurnberg/forest/11902767_/55-\ # 11902767_/ # testament_sha1: 2d4007ea51ebc982e8b7e358b17782120fff2e37 # timestamp: 2011-05-12 05:56:53 +0100 # base_revision_id: dao-gang.qu@stripped\ # ohzfrfknpqtvhmcw # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWXTJmbwABvHfgHV0XP///39n /+7////+YA9HdbRPvXW20daVtpcho5ywdOd3X1WJPNtU65LW9dO2r6UHHLhJIUyamwlH6KfoAym1 U3ppqm8Sn6iZAZPUekZqG0nqZMjygkoUwZGgmQNU2mp6T0anpMU2oaeo2oPSY1PSAAA0EoQ01FPF Jp5R6aR6TT0Q00Bpo00ANPUaAGgAAwoimI00eoG0jIAAAAAAAAANAG1IieVPR6lPZANNAmKeoDTQ ANAAAAAAJIgQJiZBNGmTQjQZGpqeUHpojRkZPKaAA9Q6qjFKC+Y4oDgOHHLm/z3+NHwLXI9jFmPX 9E0Nrd8b1Qw7qe6brOf3dtHXjniaRN2z73cCSI7m26MsXJi1valkoFTBrQnUKUICrbBpIppkJWoY hAX8YixFMgCRJAOEmYoK9ptKpdLl016atZK1c689ubfCVJamXCmoRTjXKE/yBJlJNUeg1rIorgHe GxsgoQBrzDmG6NgUBHLoEnsA/j+ivWjtVaYGYGYGYz/2IsicTxKdXhzmHUpJs/4njNMq5sEC7Nko faKus8ZMk6ssLLosk1vGaIoxE5IXTYbvWBX6RwHtNAfOvynxIEYEouH6jMHhHiuBnE8cbzGa7PlI VfUe83+4nHhHoMbHCPjM3m5A3HYMF4MBpGE8gVnG+cvKGQw3Sgovker0UMzz1vUUnM2bjWlNMHeb OF4KXA0kK3r+fJEnZGo9+ndt3lOZn04S+3+byFIwubBJRKO83Uwa1CJYplmOdqWltYpEoql7EM7f bKAu8XITl5AcYdTOguEVxfma192+7G0tdlmbGh7f4LBhRJNTPGeF7Mm7IdPX8PazhTL6PpsW/X7i Xmho8Ue6+WEfOIZgD4elxfeJh4dXyID/J2PazltG8RgHnUOGPkWxBlEyAs2WHB7Qk8xiGB4j0ntH Ry7LquRM/SVLh6djcbNnuplH6CUmCUd5pSKiokG7T0k3PALpic4ctfmrjyApxb86nVpQLyr4xIcF 408XZO4z0xH5oOC9S2XJ963qm1Zp8oX5lGLDGOFQSV4tokyv1ddRvUTecj3hrMzrurrZGxE0yS2z R1mSJlckonUrlxMYyYpajjQ82Fp40eWzR2oykYSMSePOiRaQT45VIwM26sUDN0MukYQ3NIhJJzNg kaCAnQT6rKWTJlQmSoJhIYCUT4CjMTCa0OrqFWgrFOYwk6SeatERIuxGFbqg7CW2ImaEsrkKsSch 1qbmvP1mQd7cQhJZKmQUWJEf6CghysgayhI/FjwIExYCVfvYS2sKKShSYmHCSUDQwSazM3D83r1L F8MySeKBsNHXkzVt1Spw45Y69a0VZ1HkLpJNMkEm+3KNsdDrvOSeGJgqOSdjVkfOnHS5d5HLg7LT tvUDoFEWoU71swhV1HYMaEYlJG9g/Qhw02OSTiY16qYQGaZri8kAkA2gxpzWsnYOg1MA53icSzkL mqEqDSLzQtxaxQYShCYGreiiOmNSJCRmc8qTuojouIcg2yYMVsFloFuc1WHFRrYg0FJPPtXGa9eo 7V0KuRU5Vaei11BbdcCrFm0g0vPyGoK2Zg8BbrCHbFB1NxtOGqcaYlAOsheZxwnuI+R2MjL9RWE5 WVNY0yBM4qWUaTmQ3K0pNpBbpVMyyKbVDONc5K8lEmqsZgSfQV6zIiUz0nLRMhaJU8vXSuLWlppK TIYFCXP33GphRGM8GqbHSYIZh54TKd+zRKtS5b62+C/pqhuxZ8hcjG0tBJWJ6crCwmNJgyd5NdOT LtnfbjWeAlArw04LwF5D06VxEyhysjpuZpEDQUNRadNKgvbzLdjLeVO73NoT6TpR4KOMnnYeXsyD dQxEqis4PJqbGVA2USqK+9XGJtMO9btq43WCXUaeYZc26QmIkfVAcunAkceYnPeWrYWmY2xOWqJV ALSRAuNkBdr6sLqppUc8Ld3XUs6z1xL6hz5hNZaSpeKRsijKLBeY4vJMRJ95DgYGs3DT2WzLiegt ezLGwghME1Ae4dOVPWSNNZMMigrCJEn5RJxWFuBLNXnRhPT/5u6FhPtEunDyHr2GrIWnU4oBBpK4 qpiPSbs6lCSMr2so0xKzEVq0ZEohlSVFkJYijmkBZYPbKIXe3+6tm4yqqrxjy4M7I8xkIUskRYa2 Y3RFSRbv5IKoqiszGgQxzIcnjy3zo45mCVHw856jyejmtQHjWUEfv6wHCM3xP+O4UB5QHpEHSB/I PVWkZkyGZfegkgKSbq5cDQWDgVESooEAL+5Abwg9BEw0khhFrC6zEBxwYSYC9xK4+4kGPzIiVZG8 TBAWg1CkfIiBQLIh8RiqpMyHEn+QvmcwMIgRM7QrIAZiYQYCGHziEz/o4zmWwgIxF9IyYQ0BL8bD UJkrWOgsFXDnEqzcEiJ+AfJmb5zS0JhAWm4otYLIRaILWmQKgwIn7YiVIioVwDA0CWgGiokJwcRG AiYhahu4N4oKDJckiY9ojSKAODBMULAJAmEaBgV8A5itygLovMhQFgSBSgynYZypAkpSGriiGGGO wyCs4jMMfjQIo6zcrnQBHxTPE5DI4svKhgHjDdKHDArpdOJCHFsH68HYsY3RIENA4yVoKhwjltDS No5SKuxrZBySYew9VYJp9XevWdx9CmXh7KZoC7iSQsPcjWWbPwkaxIsiH4IifcOBDvHBPKRZQS2k ekUAHyruXcamnGIkqcXvENQ9EyQ6Yx8ACmpEy1E82Q1nw95LhjSIxUOa6sG2QyRNymkme4QscHMS 5cqNWBSiSib5pkk/koBZmLq9od9285hm1plHm05jYozi3KpdJULOrRKFvcXeWyznsyrPFMBlQZTM XiR3t8G5FZimb+qslRKutAbdHSMAiUWmvSarzcIiFqLDzhRG84kIlUjvhfDlrLyE6W3f6rQQU00U 4kNakjTmnyiHhBFVGR0rKDXRtG3QdHsZxhrRnKFIZIiRFAY0d08mN+m6w0KxJl6y4kxValtJqidC 2DQIK4sOMDmaI4fAcSJ4G0kcJfhpl4EK5dhmMCMBSVdNzbFngReoqDelvimzdThxfeNRXto8GUh2 kKyzZCNtpVNdyuQh0yPRPQjuNpyIVlvf7KranWZqOwsXE8OJouRmlSpi5k+wINmhWgQ2sbRi4bKk /Xjfe2KU0eRBPYsZTJKqbt9poX00GNZ2KfUaOakhvs0tg4lcYlT4mgnyaGm3Ow1c6kwTJlWPHIap XOeyOYJRWzJzkoMS2Px8VThlDnCNxgwQSRW0AqGwIHBrFB6i7JjAUEhmTix00Aho7zTACZ7WhUkS 3eTWS7TpHowtYkmROXhdAbwiefoMFoxWbWbeWnp7HKyBM8CBOJ11HlXD1jwKxXXHISzsPVaVCgSq 3uR0YkMiVHaTnDNqVBaiNF12rmUIUq3WV1ZQHVrnkWteqCN4lUgJrxUSqsXlTAUvluFlqO7iefI4 pkRGHcE7lbga4uybxVqdLYvSqkLVfKy0cbSFkxkRjQklOrYywy22D80laYTuIsesetIGz7InPX+/ QK04spCFIN3QLI2+IGswnIMUQlU8DBmbo5ddWaqxMGMS2ZduNM9W9GoDShwrV5wvamTwajR460OK HGCimz2gGrStulzVxrYFdqeokVvAvXn2K5de5Kh2DjDDJhHW42AavRtAps61Pix/yxFgkyGOO1WI Ii4X8q+6gwmBDICuFTLofu8P5hzp49DhlGKHRXtINyI5R3/Na4SlEXh5FmNJfKAxFcRY6q7CqXMH J+8BRVBMk95BXKVVUzGe8HqgtXB6Q5KwWg0Kh3NupPGpjkLnb+kxwVMa4ityGXqvRFXLnoTKr0PB 65fYQkdKeg3fvVICrAK4XnAyJbcAbhEXLoFGUtAUWuwj0JnJTiaoZNrgEKJ0VIknw44KnYu8S8Tp zKwxTJhhhZ3Rw9klzwHFXDpZJZ15G3YCEoaix5DeJEWQsgeGW07JBUuZeSpUqAV6K2QN7YLBUyoi Z8SClX9g/Nw+kEZgVyC654qleLJ9HurUpZrWvDj5lr4VbgRPRLCBbwahiWliOW+AmXB2WnvovALS diJ3Llkdz67i4jgV8wYSkmGmQUrQblmhInVciKtndgCVo3jrtXNYi5bORkvNYXsNqm0HZibJuwck wiLFfV95MkK6gZJa9msjZwFRkKjKQphoY10iGTktL3LAVg/TJxYLBIJ7iJlpXjfj01qYORgAdpnd dYFGcCY8jZbkFM0613TKgMc1TEI5zEaiSmLHLRkndKSjB06kO4Wxq0iUZbxSRzm2W1etrhm+1zfI fDUL2ePGRRHO00WB7pVmAEkaTUyMV7gicuSiauCFhVG222225DPKLkQMRJAytlxN0CbwGogkhkzC mVEzojqSLby4CJQooqAvUtE9iGMSr4cSrfyL692EiLzxVGqkShOI35SRSTsYsSNLLQCXvGOB2LP2 zstViGBvARFEREGrYRD4sFxzCZG36/YJSKHBb19eh47hycAbaPBMKByZA5gV5qi1kIgodVZRDETg woMeKrThyI9PHt1nmIKSA6LVinr1uAcsJ02SyQYOZyFI5JzX6/QnnY0PKPzr3QSUg47wtSlqviq+ WeUUlK87i1iWuzpcTGTsZh6ycaMzjWaSb0G56JRXLsxX63yA2xpTUlSxUE7zUoK8Y50lqOidlyBr dBsK6NuJd8Zz+NRHx0mkuF1UsrwtBhOd4FfJ5HJTcH0dQYF1rw7Ck7oUPcPHOOSwmWZDwk4Vrcj0 rjUFgUrepRaeoOdMbPdG3VRlAzKWlxbmFZ+A4fv+LuSKcKEg6ZMzeA== --===============8564918581198826240==--