MySQL Bugs: #56118: STOP SLAVE does not wait till trx with CREATE TMP TABLE ends, replication aborts

Bug #56118	STOP SLAVE does not wait till trx with CREATE TMP TABLE ends, replication aborts
Submitted:	19 Aug 2010 13:12	Modified:	15 Nov 2010 13:59
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.49, 5.5.6-m3, 5.6.99	OS:	Any
Assigned to:	Libing Song	CPU Architecture:	Any

Description:
If a transaction (as a group of binlog events) includes only changes on transactional changes and CREATE TEMPORARY TABLE, STOP SLAVE does not wait till the end of the group, apparently assuming that it can be safely rolled back and re-applied. But it's not true for CREATE, so when after START SLAVE the group is re-processed, replication breaks with ER_TABLE_EXISTS_ERROR.

Reproducible on previous versions also.

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

DROP DATABASE IF EXISTS slave_stop;
CREATE DATABASE slave_stop;
USE slave_stop;
CREATE TABLE slave_stop.t_data ( num INT ) ENGINE = InnoDB;

BEGIN;
REPLACE t_data VALUES (0);
CREATE TEMPORARY TABLE operations ( op VARCHAR(16) ) ENGINE = InnoDB;
let $num=5000;
--echo # Doing $num INSERTs on master
--echo # It can be replaced by any transactional sequence or a statement
--echo # long enough for wait condition to catch; except for using SLEEP(),
--echo # as it might make STOP SLAVE hang (see bug#56096)
--disable_query_log
while($num)
{
	eval INSERT INTO t_data VALUES ($num);
	--dec $num
}
--enable_query_log
DROP TEMPORARY TABLE operations;
COMMIT;

--connection slave

let $show_statement= SHOW PROCESSLIST;
let $field= Info;
let $condition= LIKE 'INSERT%';
--source include/wait_show_condition.inc

STOP SLAVE;
--echo # On slave: COUNT is 0 - changes were rolled back
SELECT COUNT(*) FROM slave_stop.t_data;
--echo # (it could also be seen by comparing Relay_Log_Pos values)
--echo # ... but the temporary table is still open:
SHOW STATUS LIKE '%temp%';

START SLAVE;

let $show_statement= SHOW SLAVE STATUS;
let $field= Slave_SQL_Running;
let $condition= = 'No';
--source include/wait_show_condition.inc

if ($found)
{
	--vertical_results
	SHOW SLAVE STATUS;
	--echo # ^^^^
	--echo # If we tried sync_slave_with_master here, it would have failed,
	--echo # but it would produce a lot of noise. Instead we just
	--echo # waited for slave SQL thread to abort.
}	

# Cleanup 
--connection master
DROP DATABASE slave_stop;

--exit

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

3506 Li-Bing.Song@sun.com	2010-09-21
      Bug#56118 STOP SLAVE does not wait till trx with CREATE TMP TABLE ends,
                replication aborts
      
      When recieving a 'SLAVE STOP' command, slave SQL thread will roll back the
      transaction and stop immidiately if there is only transactional table updated,
      even through 'CREATE|DROP TEMPOARY TABLE' statement are in it. But These
      statements can never be rolled back. It will abort SQL thread with an error
      that the table already exists or doesn't exist, when it restarts and executes
      the whole transaction again.
      
      After this patch, SQL thread always waits till the transaction ends and then stops,
      if 'CREATE|DROP TEMPOARY TABLE' statement are in it.
     @ mysql-test/extra/rpl_tests/rpl_stop_slave.test
        Auxiliary file which is used to test this bug.
     @ mysql-test/suite/rpl/t/rpl_stop_slave.test
        Test case for this bug.
     @ sql/slave.cc
        Checking if OPTION_KEEP_LOG is set. If it is set, SQL thread should wait
        until the transaction ends.
     @ sql/sql_parse.cc
        Add a debug point for testing this bug.

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

3506 Li-Bing.Song@sun.com	2010-10-11
      Bug#56118 STOP SLAVE does not wait till trx with CREATE TMP TABLE ends,
                replication aborts
      
      When recieving a 'SLAVE STOP' command, slave SQL thread will roll back the
      transaction and stop immidiately if there is only transactional table updated,
      even through 'CREATE|DROP TEMPOARY TABLE' statement are in it. But These
      statements can never be rolled back. Because the temporary tables to the user
      session mapping remain until 'RESET SLAVE', Therefore it will abort SQL thread
      with an error that the table already exists or doesn't exist, when it restarts
      and executes the whole transaction again.
      
      After this patch, SQL thread always waits till the transaction ends and then stops,
      if 'CREATE|DROP TEMPOARY TABLE' statement are in it.
     @ mysql-test/extra/rpl_tests/rpl_stop_slave.test
        Auxiliary file which is used to test this bug.
     @ mysql-test/suite/rpl/t/rpl_stop_slave.test
        Test case for this bug.
     @ sql/slave.cc
        Checking if OPTION_KEEP_LOG is set. If it is set, SQL thread should wait
        until the transaction ends.
     @ sql/sql_parse.cc
        Add a debug point for testing this bug.

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

3506 Li-Bing.Song@sun.com	2010-10-11
      Bug#56118 STOP SLAVE does not wait till trx with CREATE TMP TABLE ends,
                replication aborts
      
      When recieving a 'SLAVE STOP' command, slave SQL thread will roll back the
      transaction and stop immidiately if there is only transactional table updated,
      even through 'CREATE|DROP TEMPOARY TABLE' statement are in it. But These
      statements can never be rolled back. Because the temporary tables to the user
      session mapping remain until 'RESET SLAVE', Therefore it will abort SQL thread
      with an error that the table already exists or doesn't exist, when it restarts
      and executes the whole transaction again.
      
      After this patch, SQL thread always waits till the transaction ends and then stops,
      if 'CREATE|DROP TEMPOARY TABLE' statement are in it.
     @ mysql-test/extra/rpl_tests/rpl_stop_slave.test
        Auxiliary file which is used to test this bug.
     @ mysql-test/suite/rpl/t/rpl_stop_slave.test
        Test case for this bug.
     @ sql/slave.cc
        Checking if OPTION_KEEP_LOG is set. If it is set, SQL thread should wait
        until the transaction ends.
     @ sql/sql_parse.cc
        Add a debug point for testing this bug.

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

3527 Li-Bing.Song@sun.com	2010-10-13
      Bug#56118 STOP SLAVE does not wait till trx with CREATE TMP TABLE ends,
                replication aborts
      
      When recieving a 'SLAVE STOP' command, slave SQL thread will roll back the
      transaction and stop immidiately if there is only transactional table updated,
      even through 'CREATE|DROP TEMPOARY TABLE' statement are in it. But These
      statements can never be rolled back. Because the temporary tables to the user
      session mapping remain until 'RESET SLAVE', Therefore it will abort SQL thread
      with an error that the table already exists or doesn't exist, when it restarts
      and executes the whole transaction again.
      
      After this patch, SQL thread always waits till the transaction ends and then stops,
      if 'CREATE|DROP TEMPOARY TABLE' statement are in it.
     @ mysql-test/extra/rpl_tests/rpl_stop_slave.test
        Auxiliary file which is used to test this bug.
     @ mysql-test/suite/rpl/t/rpl_stop_slave.test
        Test case for this bug.
     @ sql/slave.cc
        Checking if OPTION_KEEP_LOG is set. If it is set, SQL thread should wait
        until the transaction ends.
     @ sql/sql_parse.cc
        Add a debug point for testing this bug.

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

3527 Li-Bing.Song@sun.com	2010-10-13
      Bug#56118 STOP SLAVE does not wait till trx with CREATE TMP TABLE ends,
                replication aborts
      
      When recieving a 'SLAVE STOP' command, slave SQL thread will roll back the
      transaction and stop immidiately if there is only transactional table updated,
      even through 'CREATE|DROP TEMPOARY TABLE' statement are in it. But These
      statements can never be rolled back. Because the temporary tables to the user
      session mapping remain until 'RESET SLAVE', Therefore it will abort SQL thread
      with an error that the table already exists or doesn't exist, when it restarts
      and executes the whole transaction again.
      
      After this patch, SQL thread always waits till the transaction ends and then stops,
      if 'CREATE|DROP TEMPOARY TABLE' statement are in it.
     @ mysql-test/extra/rpl_tests/rpl_stop_slave.test
        Auxiliary file which is used to test this bug.
     @ mysql-test/suite/rpl/t/rpl_stop_slave.test
        Test case for this bug.
     @ sql/slave.cc
        Checking if OPTION_KEEP_LOG is set. If it is set, SQL thread should wait
        until the transaction ends.
     @ sql/sql_parse.cc
        Add a debug point for testing this bug.

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

3530 Li-Bing.Song@sun.com	2010-10-16
      Bug#56118 STOP SLAVE does not wait till trx with CREATE TMP TABLE ends,
                replication aborts
      
      When recieving a 'SLAVE STOP' command, slave SQL thread will roll back the
      transaction and stop immidiately if there is only transactional table updated,
      even through 'CREATE|DROP TEMPOARY TABLE' statement are in it. But These
      statements can never be rolled back. Because the temporary tables to the user
      session mapping remain until 'RESET SLAVE', Therefore it will abort SQL thread
      with an error that the table already exists or doesn't exist, when it restarts
      and executes the whole transaction again.
      
      After this patch, SQL thread always waits till the transaction ends and then stops,
      if 'CREATE|DROP TEMPOARY TABLE' statement are in it.
     @ mysql-test/extra/rpl_tests/rpl_stop_slave.test
        Auxiliary file which is used to test this bug.
     @ mysql-test/suite/rpl/t/rpl_stop_slave.test
        Test case for this bug.
     @ sql/slave.cc
        Checking if OPTION_KEEP_LOG is set. If it is set, SQL thread should wait
        until the transaction ends.
     @ sql/sql_parse.cc
        Add a debug point for testing this bug.

Pushed into mysql-5.1-bugteam. Merged into mysql-5.5-bugteam and mysql-trunk-merge.

Documetned in the 5.1.53 and 5.5.8 changelogs as follows:

        When STOP SLAVE is issued, the slave SQL thread rolls back the
        current transaction and stops immediately if the transaction
        updates only tables which use transactional storage engines are
        updated. Previously, this occurred even when the transaction
        contained CREATE TEMPORARY TABLE statements, DROP TEMPORARY
        TABLE statements, or both, although these statements cannot be
        rolled back. Because temporary tables persist for the lifetime
        of a user session (in the case, the replication user), they
        remain until the slave is stopped or reset. When the transaction
        is restarted following a subsequent START SLAVE statement, the
        SQL thread aborts with an error that a temporary table to be
        created (or dropped) already exists (or does not exist, in the
        latter case).

        Following this fix, if an ongoing transaction contains CREATE
        TEMPORARY TABLE statements, DROP TEMPORARY TABLE statements, or
        both, the SQL thread now waits until the transaction ends, then
        stops.

Need Merge: waiting for push to trunk.

One thing which is not clear to me is the following scenario which I use a lot.

CREATE TEMPORARY TABLE ......

BEGIN WORK;

-- do some stuff with the temporary table

COMMIT;

-- now try to shutdown the server

This behaviour was quite frequent in my previous job using Sybase
and sould work fine with RBR.

However if the temporary table is referenced outside of the transaction, or if you aren't using explicit transactions then replication can break if you stop the slave prior to the client connection on the master being disconnected (when an EXPLICIT DROP TABLE IF EXISTS .... is written to the binlog.

It might be a good idea to mention that this may be an issue for people doing "batch" processing the way I describe if using SRB.

Simon Mudd:
What you said is the same of bug#56355.

Already documented in the 5.5.8 changelog; still waiting for merge to trunk.

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

Although this bug does not actually appear in a 5.6 release, I'm adding a changelog entry for 5.6.1 since there is a slight but significant change in behaviour with this fix.

Closed.

Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)

Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)