Bug #51839 mixup of DDL causes slave to stop
Submitted: 8 Mar 2010 19:12 Modified: 4 Aug 2010 12:18
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.6.99-m4 OS:Any
Assigned to: Daogang Qu CPU Architecture:Any
Tags: temporary

[8 Mar 2010 19:12] Matthias Leich
Description:
My script:
----------
--source include/master-slave.inc
--disable_abort_on_error

CREATE TABLE t1 ( col1 BIGINT, pk INT, PRIMARY KEY (pk));
SET SESSION BINLOG_FORMAT = ROW;
CREATE TEMPORARY TABLE t2 ( col1 BIGINT, pk INT , PRIMARY KEY (pk));
LOCK TABLE t1 WRITE;
CREATE FUNCTION f1 () RETURNS TINYINT RETURN 13 ;
UPDATE t2 SET col1 = pk ;
--sync_slave_with_master

Result on 5.6.99-m4-log
mysql-next-mr revno: 3115 2010-03-06
------------------------------------
./mysql-test-run --mem --mysqld=--binlog_format=mixed rpl_ml13
...
main.rpl_ml13                            [ fail ]
...
=== SHOW SLAVE STATUS ===
...
Last_Errno  1146
Last_Error  Error 'Table 'test.t2' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t2 SET col1 = pk'

Observations:
-------------
The bad effect disappears in case
- SET SESSION BINLOG_FORMAT = ROW;
  gets replaced by
  SET SESSION BINLOG_FORMAT = <mixed_or_statement>
or
- t2 is a non temporary table
or
- LOCK TABLE or CREATE FUNCTION is removed

How to repeat:
See above
[8 Mar 2010 20:32] Matthias Leich
The observed bad effect does not happen on
5.1.46-debug-log
mysql-5.1 revno: 3366 2010-03-04
[9 Apr 2010 3:47] 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/105265

3008 Dao-Gang.Qu@sun.com	2010-04-09
      Bug #51839   mixup of DDL causes slave to stop
      
      The 'ER_LOCK_OR_ACTIVE_TRANSACTION' error happened and returned
      if a table is locked when creating a routine. The state of binlog
      format can't be restored before the return. So the following DDL
      or DML statements are binlogged with a wrong binlog format, which
      causes the slave to stop.
      
      The problem can be resolved by restoring the binlog format before
      the return. But it's better to grab an exclusive MDL lock before
      clear the current binlog format as the lock grab is not affected
      by binlog format. The same way is taken to open a proc table for
      update.
     @ mysql-test/suite/rpl/r/rpl_mixed_ddl_mixup.result
        The test result of BUG #51839.
     @ mysql-test/suite/rpl/t/rpl_mixed_ddl_mixup.test
        Added test file to verify if the execution of a DML statement 
        after create a routine when a table is locked will cause the
        slave to stop.
     @ sql/sp.cc
        Grab an exclusive MDL lock and open a proc table for update before
        clear the current binlog format when creating a routine.
[9 Apr 2010 10:02] 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/105285

3008 Dao-Gang.Qu@sun.com	2010-04-09
      Bug #51839   mixup of DDL causes slave to stop
      
      The 'ER_LOCK_OR_ACTIVE_TRANSACTION' error happened and returned
      if a table is locked when creating a routine. The state of binlog
      format can't be restored before the return. So the following DDL
      or DML statements are binlogged with a wrong binlog format, which
      causes the slave to stop.
      
      The problem can be resolved by grabbing an exclusive MDL lock firstly
      instead of clearing the current binlog format. So that the binlog
      format will not be affected when the lock grab returns directly with
      an error. The same way is taken to open a proc table for update.
     @ mysql-test/suite/rpl/r/rpl_mixed_ddl_mixup.result
        The test result of BUG #51839.
     @ mysql-test/suite/rpl/t/rpl_mixed_ddl_mixup.test
        Added test file to verify if the execution of a DML statement 
        after create a routine when a table is locked will cause the
        slave to stop.
     @ sql/sp.cc
        Grab an exclusive MDL lock and open a proc table for update firstly
        instead of clearing the current binlog format.
[16 Apr 2010 9:56] 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/105838

3008 Dao-Gang.Qu@sun.com	2010-04-16
      Bug #51839   mixup of DDL causes slave to stop
      
      The 'ER_LOCK_OR_ACTIVE_TRANSACTION' error happened and returned
      if a table is locked when creating a routine. The state of binlog
      format can't be restored before the return. So the following DDL
      or DML statements are binlogged with a wrong binlog format, which
      causes the slave to stop.
      
      The problem can be resolved by grabbing an exclusive MDL lock firstly
      instead of clearing the current binlog format. So that the binlog
      format will not be affected when the lock grab returns directly with
      an error. The same way is taken to open a proc table for update.
     @ mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result
        Test Result for bug#51839
     @ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test
        Added test to make sure the binlog format is not changed
        after a execution of DDL with a table locked.
     @ sql/sp.cc
        Grab an exclusive MDL lock and open a proc table for update firstly
        instead of clearing the current binlog format.
[20 Apr 2010 3:06] 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/106061

3008 Dao-Gang.Qu@sun.com	2010-04-20
      Bug #51839   mixup of DDL causes slave to stop
      
      Stored routine DDL statements use statement-based replication
      regardless of the current binlog format. The problem here was
      that if a DDL statement failed during metadata lock acquisition
      or opening of mysql.proc, the binlog format would not be reset
      before returning. So the following DDL or DML statements are 
      binlogged with a wrong binlog format, which causes the slave 
      to stop.
      
      The problem can be resolved by grabbing an exclusive MDL lock firstly
      instead of clearing the current binlog format. So that the binlog
      format will not be affected when the lock grab returns directly with
      an error. The same way is taken to open a proc table for update.
     @ mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result
        Test Result for bug#51839
     @ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test
        Added test to make sure the binlog format is not changed
        after a execution of DDL with a table locked.
     @ sql/sp.cc
        Grab an exclusive MDL lock and open a proc table for update firstly
        instead of clearing the current binlog format.
[28 Apr 2010 3:30] 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/106744

3024 Dao-Gang.Qu@sun.com	2010-04-28
      Bug #51839   mixup of DDL causes slave to stop
      
      Stored routine DDL statements use statement-based replication
      regardless of the current binlog format. The problem here was
      that if a DDL statement failed during metadata lock acquisition
      or opening of mysql.proc, the binlog format would not be reset
      before returning. So the following DDL or DML statements are 
      binlogged with a wrong binlog format, which causes the slave 
      to stop.
      
      The problem can be resolved by grabbing an exclusive MDL lock firstly
      instead of clearing the current binlog format. So that the binlog
      format will not be affected when the lock grab returns directly with
      an error. The same way is taken to open a proc table for update.
     @ mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result
        Test Result for bug#51839
     @ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test
        Added test to make sure the binlog format is not changed
        after a execution of DDL with a table locked.
     @ sql/sp.cc
        Grab an exclusive MDL lock and open a proc table for update firstly
        instead of clearing the current binlog format.
[28 Apr 2010 3:41] 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/106745

3158 Dao-Gang.Qu@sun.com	2010-04-28 [merge]
      Bug #51839   mixup of DDL causes slave to stop
            
      Stored routine DDL statements use statement-based replication
      regardless of the current binlog format. The problem here was
      that if a DDL statement failed during metadata lock acquisition
      or opening of mysql.proc, the binlog format would not be reset
      before returning. So the following DDL or DML statements are 
      binlogged with a wrong binlog format, which causes the slave 
      to stop.
            
      The problem can be resolved by grabbing an exclusive MDL lock firstly
      instead of clearing the current binlog format. So that the binlog
      format will not be affected when the lock grab returns directly with
      an error. The same way is taken to open a proc table for update.
     @ mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result
        Test Result for bug#51839
     @ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test
        Added test to make sure the binlog format is not changed
        after a execution of DDL with a table locked.
     @ sql/sp.cc
        Grab an exclusive MDL lock and open a proc table for update firstly
        instead of clearing the current binlog format.
[28 Apr 2010 4:28] 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/106746

3843 Dao-Gang.Qu@sun.com	2010-04-28 [merge]
      Bug #51839   mixup of DDL causes slave to stop
            
      Stored routine DDL statements use statement-based replication
      regardless of the current binlog format. The problem here was
      that if a DDL statement failed during metadata lock acquisition
      or opening of mysql.proc, the binlog format would not be reset
      before returning. So the following DDL or DML statements are 
      binlogged with a wrong binlog format, which causes the slave 
      to stop.
            
      The problem can be resolved by grabbing an exclusive MDL lock firstly
      instead of clearing the current binlog format. So that the binlog
      format will not be affected when the lock grab returns directly with
      an error. The same way is taken to open a proc table for update.
     @ mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result
        Test Result for bug#51839
     @ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test
        Added test to make sure the binlog format is not changed
        after a execution of DDL with a table locked.
     @ sql/sp.cc
        Grab an exclusive MDL lock and open a proc table for update firstly
        instead of clearing the current binlog format.
[28 Apr 2010 6:20] Daogang Qu
The patch is pushed into mysql-trunk-bugfixing, mysql-next-mr-bugfixing and
mysql-6.0-codebase-bugfixing.
[7 May 2010 9:21] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100507091908-vqyhpwf2km0aokno) (version source revid:alik@sun.com-20100507091737-12vceffs11elb25g) (merge vers: 6.0.14-alpha) (pib:16)
[7 May 2010 9:22] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (version source revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (merge vers: 5.5.5-m3) (pib:16)
[7 May 2010 9:23] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100507091823-nzao4h3qosau4tin) (version source revid:alik@sun.com-20100507091720-ib9r8uny2aeazvas) (pib:16)
[8 May 2010 16:45] Paul DuBois
Noted in 5.5.5, 6.0.14 changelogs.

Stored routine DDL statements were written to the binary log using
statement-based format regardless of the current logging format.
[4 Aug 2010 8:08] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100507093958-2y0wy6svnc3zfgqb) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 8:24] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100507093958-2y0wy6svnc3zfgqb) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 12:18] Jon Stephens
Changelog entry added for 5.6.1. Closed.
[4 Aug 2010 14:03] Jon Stephens
5.6 version is 5.6.0 not 5.6.1 - fixed changelog entry.