MySQL Bugs: #44938: Replication fails when optimizing a table used by pending transaction.

Bug #44938	Replication fails when optimizing a table used by pending transaction.
Submitted:	18 May 2009 16:53	Modified:	6 Aug 2010 9:26
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	6.0.11,5.4	OS:	Linux
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	disabled

Description:
Create database and table(t1).
Start a transaction in the table(t1) in master.
Optimize the table t1 from another connection(master1)

At this point server will hang and will result in timeout. 
Optimize operations fails due to timeout and is not logged. The test hangs due to metadata locks because of pending transaction from another connection. Optimizing the table with pending transaction should not cause replication to fail.

How to repeat:
--source include/master-slave.inc

CREATE TABLE t1 ( a int ) ENGINE=Innodb;
BEGIN;
INSERT INTO t1 VALUES (1);

connection master1;
OPTIMIZE TABLE t1;

I think this is a kind of/related to bug #989. Please, check.

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

2858 Luis Soares	2009-05-29
      BUG#44938: Replication fails when optimizing a table used by pending
      transaction.
      
      The original version of rpl_failed_optimize checked that a failed
      optimize operation would not harm replication. It performed two
      checks:
       
        1. Check that failed optimize operations due to timeout (in this
           case, because it is optimizing a currently used table in other
           session), will not harm replication.
      
           After patch for BUG#989 was pushed, the test hangs here, due to
           meta data locks - optimize will block on mdl lock instead of
           innodb lock, causing the test operations on the other session not
           to execute. Consequently, the entire test hangs.
      
        2. Check that optimizing a non-existing table will not harm
           replication. 
      
      Due to the change in behavior introduced by patch for BUG#989, this
      fix addresses the blocking issue by removing item 1.
     @ mysql-test/extra/rpl_tests/rpl_failed_optimize.test
        Removed the blocking part of the test that has become deprecated after
        a change in behavior introduced by patch for BUG#989.
     @ mysql-test/suite/rpl/r/rpl_failed_optimize.result
        Updated the result file.
     @ mysql-test/suite/rpl/t/disabled.def
        Reenabled the test case.
     @ mysql-test/suite/rpl/t/rpl_failed_optimize-master.opt
        This server option does not make sense anymore after removal in the
        test case of the concurrent optimize.

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

2814 Luis Soares	2009-06-26
      BUG#44938: Replication fails when optimizing a table used by pending
      transaction.
            
      The original version of rpl_failed_optimize checked that a failed
      optimize operation would not harm replication. It performed two
      checks:
             
        1. Check that failed optimize operations due to timeout (in this
           case, because it is optimizing a currently used table in other
           session), will not harm replication.
            
           After patch for BUG#989 was pushed, the test hangs here, due to
           meta data locks - optimize will block on mdl lock instead of
           innodb lock, causing the test operations on the other session not
           to execute. Consequently, the entire test hangs.
            
        2. Check that optimizing a non-existing table will not harm
           replication. 
            
      Due to the change in behavior introduced by patch for BUG#989, this
      fix addresses the blocking issue by removing item 1.
     @ mysql-test/extra/rpl_tests/rpl_failed_optimize.test
        Removed the blocking part of the test that has become deprecated after
        a change in behavior introduced by patch for BUG#989.
     @ mysql-test/suite/rpl/r/rpl_failed_optimize.result
        Updated the result file.
     @ mysql-test/suite/rpl/t/disabled.def
        Reenabled the test case.
     @ mysql-test/suite/rpl/t/rpl_failed_optimize-master.opt
        This server option does not make sense anymore after removal in the
        test case of the concurrent optimize.

Pushed to mysql-azalea-bugfixing.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:luis.soares@sun.com-20090629083542-87rjmhmf34xzmvp3) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:luis.soares@sun.com-20090629083542-87rjmhmf34xzmvp3) (merge vers: 5.4.4-alpha) (pib:11)

Documented bugfix in the 5.4.4 changelog as follows:

        Running OPTIMIZE TABLE from one connection on a table in use by
        a transaction executed by another connection caused the server
        to hang, and replication to fail.

        See also Bug #989.

Deleted changelog entry per discussion with Davi; was test case fix only.

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

2967 Luis Soares	2010-03-08
      BUG#44938: Replication fails when optimizing a table used by pending
      transaction.
      
      Backporting cset from 6.0 revision id:
      luis.soares@sun.com-20090626142653-utpox17kwyk1ph7m
                         
      The original version of rpl_failed_optimize checked that a failed
      optimize operation would not harm replication. It performed two
      checks:
                           
        1. Check that failed optimize operations due to timeout (in this
           case, because it is optimizing a currently used table in other
           session), will not harm replication.
                          
           After patch for BUG#989 was pushed, the test hangs here, due to
           meta data locks - optimize will block on mdl lock instead of
           innodb lock, causing the test operations on the other session not
           to execute. Consequently, the entire test hangs.
                          
        2. Check that optimizing a non-existing table will not harm
           replication. 
                          
      Due to the change in behavior introduced by patch for BUG#989, this
      fix addresses the blocking issue by removing item 1.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100324081249-yfwol7qtcek6dh7w) (version source revid:alik@sun.com-20100324081113-kc7x1iytnplww91u) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100324081159-5b8juv8ldiqwce8v) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (pib:16)

No changelog entry needed.

Setting report to Need Merge pending push of Celosia to release tree.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (merge vers: 5.6.99-m4) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (merge vers: 5.6.99-m4) (pib:18)

No changelog entry needed.

Setting back to Closed -- see previous comments from Paul and me.