Bug #44938 Replication fails when optimizing a table used by pending transaction.
Submitted: 18 May 18:53 Modified: 9 Jul 15:31
Reporter: Hema Sridharan
Status: Closed
Category:Server: Replication Severity:S3 (Non-critical)
Version:6.0.11,5.4 OS:Linux
Assigned to: Luís Soares Target Version:5.4+
Tags: disabled
Triage: Triaged: D2 (Serious)

[18 May 18: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 19:23] Valeriy Kravchuk 
I think this is a kind of/related to bug #989. Please, check.
[29 May 13: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 16: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 10:46] Luís Soares 
Pushed to mysql-azalea-bugfixing.
[3 Jul 8: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 9: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 15: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 15:48] Jon Stephens 
Deleted changelog entry per discussion with Davi; was test case fix only.