| Bug #55876 | Table map events of 'CREATE TABLE SELECT' are disorderly, it interrupts replicat | ||
|---|---|---|---|
| Submitted: | 10 Aug 2010 10:51 | Modified: | 22 Mar 2011 12:45 |
| Reporter: | Libing Song | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.6.99 | OS: | Any |
| Assigned to: | Libing Song | CPU Architecture: | Any |
| Tags: | 5.5, regression | ||
[10 Aug 2010 17:14]
Sveta Smirnova
Thank you for the report. Verified as described.
[14 Sep 2010 2:50]
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/118134 3287 Dao-Gang.Qu@sun.com 2010-09-14 Bug #55876 Table map events of 'CREATE TABLE SELECT' are disorderly, it interrupts replicat When store function which inserts rows into other tables is called in sub stmt of 'CREATE TABLE SELECT' stmt, table map events of the 'CREATE TABLE SELECT' stmt are disorderly. The reason is the 'CREATE TABLE SELECT' stmt is written into transaction catch when creating a table with MyISAM engine. But the table map of the table created is written into stmt catch. To fix the problem, write the table map of the table created by 'CREATE TABLE SELECT' stmt into transaction catch. @ mysql-test/suite/rpl/r/rpl_row_create_select.result Test result for BUG#55876 @ mysql-test/suite/rpl/t/rpl_row_create_select.test Added test file to verify if table map events of 'CREATE TABLE SELECT' are orderly and then replication will work fine. @ sql/handler.cc Added code to write the table map of the table created by 'CREATE TABLE SELECT' stmt into transaction catch. @ sql/sql_class.h Added code to make main_lex to be available out the class. So that we can get it in sub stmt.
[20 Sep 2010 3:08]
Daogang Qu
The bug is not exist as SELECT clause in 'CREATE TABLE ... SELECT' statement call store functions to modify other tables is forbade in WL5576.
[30 Dec 2010 7:50]
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/127695 3449 Li-Bing.Song@sun.com 2010-12-30 WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables BUG#39804 and BUG#55876 are fixed by this worklog. In 'CREATE TABLE ... SELECT' statement, SELECT clause could call store functions to modify other tables. It made CTS's behavior towards complex and not understandable. It also caused some binlogging problem. So the behavior modifying other tables is prohibited in this worklog. In this patch, code is added to check if there is any other table owning WRITE lock just after all tables have been locked. CREATE TABLE ... SELECT will fail and print an error immediately if any other table has WRITE lock. @ mysql-test/include/unsafe_binlog.inc The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/create.result Added test to verify WL#5576. @ mysql-test/r/func_rollback.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/mdl_sync.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/unsafe_binlog_innodb.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/suite/innodb/r/innodb.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/suite/innodb/t/innodb.test The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/t/create.test Added test to verify WL#5576. @ mysql-test/t/func_rollback.test The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/t/mdl_sync.test 'FOR UPDATE' is disallowed in CREATE TABLE ... SELECT statement after WL#5576. Using a new method to lock the table. @ sql/share/errmsg-utf8.txt Added a new error for WL#5576. @ sql/sql_parse.cc Implemented WL#5576.
[30 Dec 2010 8:04]
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/127696 3449 Li-Bing.Song@sun.com 2010-12-30 WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables BUG#39804 and BUG#55876 are fixed by this worklog. In 'CREATE TABLE ... SELECT' statement, SELECT clause could call store functions to modify other tables. It made CTS's behavior towards complex and not understandable. It also caused some binlogging problem. So the behavior modifying other tables is prohibited in this worklog. In this patch, code is added to check if there is any other table owning WRITE lock just after all tables have been locked. CREATE TABLE ... SELECT will fail and print an error immediately if any other table has WRITE lock. @ mysql-test/include/unsafe_binlog.inc The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/create.result Added test to verify WL#5576. @ mysql-test/r/func_rollback.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/mdl_sync.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/unsafe_binlog_innodb.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/suite/innodb/r/innodb.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/suite/innodb/t/innodb.test The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/t/create.test Added test to verify WL#5576. @ mysql-test/t/func_rollback.test The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/t/mdl_sync.test 'FOR UPDATE' is disallowed in CREATE TABLE ... SELECT statement after WL#5576. Using a new method to lock the table. @ sql/share/errmsg-utf8.txt Added a new error for WL#5576. @ sql/sql_parse.cc Implemented WL#5576.
[30 Dec 2010 8:09]
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/127697 3449 Li-Bing.Song@sun.com 2010-12-30 WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables BUG#39804 and BUG#55876 are fixed by this worklog. In 'CREATE TABLE ... SELECT' statement, SELECT clause could call store functions to modify other tables. It made CTS's behavior towards complex and not understandable. It also caused some binlogging problem. So the behavior modifying other tables is prohibited in this worklog. In this patch, code is added to check if there is any other table owning WRITE lock just after all tables have been locked. CREATE TABLE ... SELECT will fail and print an error immediately if any other table has WRITE lock. @ mysql-test/include/unsafe_binlog.inc The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/create.result Added test to verify WL#5576. @ mysql-test/r/func_rollback.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/mdl_sync.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/unsafe_binlog_innodb.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/suite/innodb/r/innodb.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/suite/innodb/t/innodb.test The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/t/create.test Added test to verify WL#5576. @ mysql-test/t/func_rollback.test The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/t/mdl_sync.test 'FOR UPDATE' is disallowed in CREATE TABLE ... SELECT statement after WL#5576. Using a new method to lock the table. @ sql/share/errmsg-utf8.txt Added a new error for WL#5576. @ sql/sql_parse.cc Implemented WL#5576.
[30 Dec 2010 8:12]
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/127698 3449 Li-Bing.Song@sun.com 2010-12-30 WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables BUG#39804 and BUG#55876 are fixed by this worklog. In 'CREATE TABLE ... SELECT' statement, SELECT clause could call store functions to modify other tables. It made CTS's behavior towards complex and not understandable. It also caused some binlogging problem. So the behavior modifying other tables is prohibited in this worklog. In this patch, code is added to check if there is any other table owning WRITE lock just after all tables have been locked. CREATE TABLE ... SELECT will fail and print an error immediately if any other table has WRITE lock. @ mysql-test/include/unsafe_binlog.inc The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/create.result Added test to verify WL#5576. @ mysql-test/r/func_rollback.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/mdl_sync.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/r/unsafe_binlog_innodb.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/suite/innodb/r/innodb.result The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/suite/innodb/t/innodb.test The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/t/create.test Added test to verify WL#5576. @ mysql-test/t/func_rollback.test The CREATE TABLE ... SELECT with a store function which updates other tables fails with a new error added by WL#5576. @ mysql-test/t/mdl_sync.test 'FOR UPDATE' is disallowed in CREATE TABLE ... SELECT statement after WL#5576. Using a new method to lock the table. @ sql/share/errmsg-utf8.txt Added a new error for WL#5576. @ sql/sql_parse.cc Implemented WL#5576.
[21 Feb 2011 6:06]
Libing Song
It was fixed by the patch for WL#5576. Pushed to mysql-trunk. http://lists.mysql.com/commits/131701
[22 Mar 2011 12:45]
Jon Stephens
Fixed by WL#5576, q.v. for docs info. Closed.
Description: When store function which insert rows into other tables is called, table map events of the statement are disorderly. The creating table's table map event is binlogged before the 'CREATE TABLE' statement. Because of this the following row events generates an error that 'Table 'test.t1' doesn't exist'. I did not verify 'UPDATE' and 'DELECT' statement. Here is the events: ------------------- master-bin.000001 521 Query 1 589 BEGIN master-bin.000001 589 Table_map 1 630 table_id: 43 (test.t1) master-bin.000001 630 Table_map 1 671 table_id: 41 (test.t2) master-bin.000001 671 Write_rows 1 705 table_id: 41 flags: STMT_END_F master-bin.000001 705 Query 1 774 COMMIT master-bin.000001 774 Query 1 842 BEGIN master-bin.000001 842 Query 1 991 use `test`; CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, UNIQUE KEY `a` (`a`) ) ENGINE=MyISAM master-bin.000001 991 Table_map 1 1032 table_id: 42 (test.t3) master-bin.000001 1032 Write_rows 1 1066 table_id: 42 master-bin.000001 1066 Write_rows 1 1100 table_id: 43 flags: STMT_END_F master-bin.000001 1100 Xid 1 1127 COMMIT /* xid=28 */ How to repeat: source include/master-slave.inc; source include/have_innodb.inc; source include/have_binlog_format_row.inc; CREATE TABLE t2(a INT) ENGINE=MYISAM; CREATE TABLE t3(a INT) ENGINE=INNODB; delimiter |; CREATE FUNCTION f1() RETURNS INT BEGIN insert into t3 values(1); insert into t2 values(1); return 1; END| delimiter ;| let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1); CREATE TABLE t1(UNIQUE(a)) ENGINE=MYISAM SELECT f1() as a; eval SHOW BINLOG EVENTS IN '$binlog_file' FROM $binlog_start; select * from t1; sync_slave_with_master; select * from t1; exit;