MySQL Bugs: #49132: Replication failure on temporary table + DDL

Bug #49132	Replication failure on temporary table + DDL
Submitted:	26 Nov 2009 10:50	Modified:	15 Mar 2010 4:56
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S2 (Serious)
Version:	next-mr, 6.0-codebase, 5.1	OS:	Any
Assigned to:	Daogang Qu	CPU Architecture:	Any

Description:
Issuing DROP EVENT before trying to manipulate a temporary table causes row-based replication to break with:

091126 13:33:05 [ERROR] Slave SQL: Error 'Table 'test.t1' doesn't exist' on query. Default database: 'test'. Query: 'DELETE FROM t1', Error_code: 1146

How to repeat:
--source include/master-slave.inc
--disable_abort_on_error
SET GLOBAL BINLOG_FORMAT = 'row';
--connect (c6, localhost, root, , test)
CREATE TEMPORARY TABLE t1 (f1 integer);
DROP EVENT IF EXISTS e1;
DELETE FROM t1;
--connection default
--sync_slave_with_master

This issue also happens with ALTER EVENT

Non-event DDLs also cause the same issue:

--source include/master-slave.inc
--disable_abort_on_error
SET GLOBAL BINLOG_FORMAT = 'row';
--connect (c1, localhost, root, , test)
CREATE TEMPORARY TABLE IF NOT EXISTS temp1 ( f1 int );

DELIMITER |;
CREATE PROCEDURE p1() BEGIN SELECT 1 ;  END |
DELIMITER ;|

UPDATE  temp1 SET f1 = 1;

--connection default
--sync_slave_with_master

see also bug#49137

Exists in 5.1

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/95348

3271 Dao-Gang.Qu@sun.com	2009-12-22
      Bug #49132  	Replication failure on temporary table + DDL
      
      In RBR, DDL statement will change binlog format to non row-based 
      format, and then manipulating a temporary table can not reset binlog 
      format to row-based format rightly. So that the manipulated statement  
      is binlogged with statement-based format.
      
      To fix the problem, restore the state of binlog format after the DDL 
      statement is binlogged.
     @ mysql-test/suite/rpl/r/rpl_failure_on_tmp_table_and_DDL.result
        Test result of the bug#49132.
     @ mysql-test/suite/rpl/t/rpl_failure_on_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement 
        before trying to manipulate a temporary table causes row-based 
        replication to break with error 'table does not exist'.

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/95771

3271 Dao-Gang.Qu@sun.com	2009-12-29
      Bug #49132  	Replication failure on temporary table + DDL
      
      In RBR, DDL statement will change binlog format to non row-based 
      format, and then manipulating a temporary table can not reset binlog 
      format to row-based format rightly. So that the manipulated statement  
      is binlogged with statement-based format.
      
      To fix the problem, restore the state of binlog format after the DDL 
      statement is binlogged.
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Correct the test result, all the above binlog event
        should be row-based after the bug49132 is fixed IN RBR.
     @ mysql-test/suite/rpl/r/rpl_failure_on_tmp_table_and_DDL.result
        Test result of the bug#49132.
     @ mysql-test/suite/rpl/t/rpl_failure_on_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement 
        before trying to manipulate a temporary table causes row-based 
        replication to break with error 'table does not exist'.

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/95939

3302 Dao-Gang.Qu@sun.com	2010-01-05
      Bug #49132  	Replication failure on temporary table + DDL
      
      In RBR, DDL statement will change binlog format to non row-based
      format, and then manipulating a temporary table can not reset binlog
      format to row-based format rightly. So that the manipulated statement
      is binlogged with statement-based format.
      
      To fix the problem, restore the state of binlog format after the DDL
      statement is binlogged.
     @ mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL.test
        Added test file to execute DDL statement when a temporary table is open.
     @ mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL_ACL.test
        Added the test file to verify if executing ACL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist'.
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Correct the test result, all the above binlog event
        should be row-based after the bug49132 is fixed IN RBR.
     @ mysql-test/suite/ndb/r/ndb_tmp_table_and_DDL.result
        Test result for bug#49132 base on ndb engine.
     @ mysql-test/suite/ndb/t/ndb_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist' base on ndb engine.
     @ mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result
        Test result for bug#49132 base on myisam engine.
     @ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist' base on myisam engine.
     @ sql/event_db_repository.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/events.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sp.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sql_acl.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sql_udf.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.

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/96858

3302 Dao-Gang.Qu@sun.com	2010-01-14
      Bug #49132  	Replication failure on temporary table + DDL
      
      In RBR, DDL statement will change binlog format to non row-based
      format before it is binlogged, but the binlog format was not be
      restored, and then manipulating a temporary table can not reset binlog
      format to row-based format rightly. So that the manipulated statement
      is binlogged with statement-based format.
      
      To fix the problem, restore the state of binlog format after the DDL
      statement is binlogged.
     @ mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist'.
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Correct the test result, all the above binlog event
        should be row-based after the bug49132 is fixed IN RBR.
     @ mysql-test/suite/ndb/r/ndb_tmp_table_and_DDL.result
        Test result for bug#49132 base on ndb engine.
     @ mysql-test/suite/ndb/t/ndb_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist' base on ndb engine.
     @ mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result
        Test result for bug#49132 base on myisam engine.
     @ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist' base on myisam engine.
     @ sql/event_db_repository.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/events.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sp.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sql_acl.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sql_udf.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.

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/97489

3316 Dao-Gang.Qu@sun.com	2010-01-20
      Bug #49132  	Replication failure on temporary table + DDL
      
      In RBR, DDL statement will change binlog format to non row-based
      format before it is binlogged, but the binlog format was not be
      restored, and then manipulating a temporary table can not reset binlog
      format to row-based format rightly. So that the manipulated statement
      is binlogged with statement-based format.
      
      To fix the problem, restore the state of binlog format after the DDL
      statement is binlogged.
     @ mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist'.
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Correct the test result, all the above binlog event
        should be row-based after the bug49132 is fixed IN RBR.
     @ mysql-test/suite/ndb/r/ndb_tmp_table_and_DDL.result
        Test result for bug#49132 base on ndb engine.
     @ mysql-test/suite/ndb/t/ndb_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist' base on ndb engine.
     @ mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result
        Test result for bug#49132 base on myisam engine.
     @ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist' base on myisam engine.
     @ sql/event_db_repository.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/events.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sp.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sql_acl.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sql_udf.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.

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/97817

3330 Dao-Gang.Qu@sun.com	2010-01-22
      Bug #49132  	Replication failure on temporary table + DDL
      
      In RBR, DDL statement will change binlog format to non row-based
      format before it is binlogged, but the binlog format was not be
      restored, and then manipulating a temporary table can not reset binlog
      format to row-based format rightly. So that the manipulated statement
      is binlogged with statement-based format.
      
      To fix the problem, restore the state of binlog format after the DDL
      statement is binlogged.
     @ mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist'.
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Correct the test result, all the above binlog event
        should be row-based after the bug49132 is fixed IN RBR.
     @ mysql-test/suite/ndb/r/ndb_tmp_table_and_DDL.result
        Test result for bug#49132 base on ndb engine.
     @ mysql-test/suite/ndb/t/ndb_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist' base on ndb engine.
     @ mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result
        Test result for bug#49132 base on myisam engine.
     @ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist' base on myisam engine.
     @ sql/event_db_repository.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/events.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sp.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sql_acl.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sql_udf.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.

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/97998

3843 Dao-Gang.Qu@sun.com	2010-01-25 [merge]
      Bug #49132  	Replication failure on temporary table + DDL
            
      In RBR, DDL statement will change binlog format to non row-based
      format before it is binlogged, but the binlog format was not be
      restored, and then manipulating a temporary table can not reset binlog
      format to row-based format rightly. So that the manipulated statement
      is binlogged with statement-based format.
            
      To fix the problem, restore the state of binlog format after the DDL
      statement is binlogged.
     @ mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist'.
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Correct the test result, all the above binlog event
        should be row-based after the bug49132 is fixed in RBR.
     @ mysql-test/suite/ndb/r/ndb_tmp_table_and_DDL.result
        Test result for bug#49132 base on ndb engine.
     @ mysql-test/suite/ndb/t/ndb_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist' base on ndb engine.
     @ mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result
        Test result for bug#49132 base on myisam engine.
     @ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test
        Added the test file to verify if executing DDL statement before
        trying to manipulate a temporary table causes row-based replication
        to break with error 'table does not exist' base on myisam engine.
     @ sql/event_db_repository.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/events.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sp.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sql_acl.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.
     @ sql/sql_udf.cc
        Added code to restore the state of binlog format after the DDL
        statement is binlogged.

Pushed into mysql-5.1-bugteam and mysql-pe.

Pushed into 5.1.44 (revid:joro@sun.com-20100204101444-2j32mhqroo0iiio6) (version source revid:dao-gang.qu@sun.com-20100122093821-sph2m5jo2piqgp4e) (merge vers: 5.1.43) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100130200931-zh341ku5wbr8e8yt) (pib:16)

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)

Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alik@sun.com-20100130182706-k2z4hajy6a5j3ssd) (merge vers: 5.5.2-m2) (pib:16)

Documented bugfix in the 5.1.44, 5.5.2, and 6.0.14 changelogs, as follows;

        When logging in row-based mode, DDL statements are actually
        logged as statements; however, statements that affected
        temporary tables and followed DDL statements failed to reset the
        binary log format to ROW, with the result that these statements
        were logged using the statement-based format. Now the state of
        binlog_format is restored after a DDL statement has been written
        to the binary log.

Closed.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100213083436-9pesg4h55w1mekxc) (version source revid:luis.soares@sun.com-20100211135109-t63avry9fqpgyh78) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100213083327-cee4ao3jpg33eggv) (version source revid:luis.soares@sun.com-20100211135018-1f9dbghg0itszigo) (pib:16)

Already documented fix in the 6.0.14 changelog; re-closing.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100213160132-nx1vlocxuta76txh) (merge vers: 5.5.99-m3) (pib:16)

Already documented fix in the 5.5.2 changelog; no new changelog entries required.

Closed.

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)

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)

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)

No additional changelog entries needed. Setting back to Closed state.