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:
None 
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
Triage: Triaged: D2 (Serious)

[10 Aug 2010 10:51] Libing Song
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;
[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.