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 Manuel Oliveira Soares CPU Architecture:Any
Tags: disabled

[18 May 2009 16:53] Hema Sridharan
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;
[18 May 2009 17:23] Valeriy Kravchuk
I think this is a kind of/related to bug #989. Please, check.
[29 May 2009 11:17] 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/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.
[26 Jun 2009 14:27] 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/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.
[29 Jun 2009 8:46] Luis Manuel Oliveira Soares
Pushed to mysql-azalea-bugfixing.
[3 Jul 2009 6:13] Bugs System
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)
[9 Jul 2009 7:34] Bugs System
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)
[9 Jul 2009 13:31] Jon Stephens
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.
[9 Jul 2009 13:48] Jon Stephens
Deleted changelog entry per discussion with Davi; was test case fix only.
[8 Mar 2010 18:05] 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/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.
[24 Mar 2010 8:14] Bugs System
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)
[24 Mar 2010 8:17] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100324081159-5b8juv8ldiqwce8v) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (pib:16)
[24 Mar 2010 16:45] Paul Dubois
No changelog entry needed.

Setting report to Need Merge pending push of Celosia to release tree.
[4 Aug 2010 8:03] Bugs System
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)
[4 Aug 2010 8:19] Bugs System
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)
[4 Aug 2010 23:21] Paul Dubois
No changelog entry needed.
[6 Aug 2010 9:26] Jon Stephens
Setting back to Closed -- see previous comments from Paul and me.