MySQL Bugs: #53421: Part of transaction not written in binlog after deadlock, replication breaks

Bug #53421	Part of transaction not written in binlog after deadlock, replication breaks
Submitted:	4 May 2010 20:28	Modified:	13 Jul 2010 9:11
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.5.5-m3, 5.6.99-m4	OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any
Tags:	regression

Description:
In the test case below, one connection (master1) starts a transaction, creates a temporary table using a select statement, and attempts to insert into the temporary table while selecting from another InnoDB table. Due to concurrent DML activity in another connection (master), the first insert hits a deadlock. The next one succeeds, and the transaction gets committed.

After the transaction is committed, the second insert is written into the binary log, but the part before deadlock is not logged, including CREATE TEMPORARY TABLE statement, and replication aborts with error 1146 (table does not exist). 

The problem is reproducible with MBR and SBR on current mysql-trunk and mysql-next-mr. 
Not reproducible on 5.5.3-m3, 5.5.4-m3, current mysql-5.1.

How to repeat:
# MTR test

--source include/master-slave.inc
--source include/have_binlog_format_mixed_or_statement.inc
--source include/have_innodb.inc

connection master;

--disable_warnings
DROP DATABASE IF EXISTS systest;
--enable_warnings
CREATE DATABASE systest;
USE systest;

CREATE TABLE tb1_eng1
(i1 int NOT NULL AUTO_INCREMENT PRIMARY KEY, f1 INT)
ENGINE = InnoDB;

INSERT INTO tb1_eng1 (f1) VALUES
(11),(12),(13),(14),(15);

USE systest;
SET AUTOCOMMIT = 0;

connection master1;
USE systest;
START TRANSACTION;
CREATE TEMPORARY TABLE t1_tmp ENGINE = InnoDB
AS SELECT f1 FROM tb1_eng1 WHERE 1 = 0;

connection master;
DELETE FROM tb1_eng1 WHERE i1 = 5;
UPDATE tb1_eng1 SET f1=122 WHERE i1=4;

connection master1;
send INSERT INTO t1_tmp (f1)
SELECT f1 FROM tb1_eng1 WHERE f1 IN ( 14, 16, 17 );

connection master;
--real_sleep 2
UPDATE tb1_eng1 SET f1=122 WHERE i1=2;
COMMIT;

connection master1;
--error ER_LOCK_DEADLOCK
reap;
INSERT INTO t1_tmp (f1) SELECT f1 FROM tb1_eng1  WHERE i1 = 1;
COMMIT;

--sync_slave_with_master

# EOF

Hi all,

Find a fix for this bug below:

=== modified file 'sql/sql_parse.cc'
--- sql/sql_parse.cc    2010-04-26 09:02:29 +0000
+++ sql/sql_parse.cc    2010-05-11 23:26:48 +0000
@@ -2655,6 +2655,10 @@
         */
         lex->unlink_first_table(&link_to_local);

+        /* So that CREATE TEMPORARY TABLE gets to binlog at commit/rollback */
+        if (create_info.options & HA_LEX_CREATE_TMP_TABLE)
+          thd->variables.option_bits|= OPTION_KEEP_LOG;
+
         /*
           select_create is currently not re-execution friendly and
           needs to be created for every execution of a PS/SP.

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

3038 Alfranio Correia	2010-05-12
      BUG#53421 Part of transaction not written in binlog after deadlock, replication breaks
      
      When a "CREATE TEMPORARY TABLE SELECT * FROM" was executed the OPTION_KEEP_LOG was
      not set into the thd->variables.option_bits. For that reason, if the transaction
      had updated only transactional engines and was rolled back at the end (.e.g due to
      a deadlock) the changes were not written to the binary log, including the creation
      of the temporary table.
      
      To fix the problem, we have set the OPTION_KEEP_LOG into the thd->variables.option_bits
      when a "CREATE TEMPORARY TABLE SELECT * FROM" is executed.

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

3086 Alfranio Correia	2010-06-01
      BUG#53421 Part of transaction not written in binlog after deadlock, replication
      breaks
            
      When a "CREATE TEMPORARY TABLE SELECT * FROM" was executed the OPTION_KEEP_LOG was
      not set into the thd->variables.option_bits. For that reason, if the transaction
      had updated only transactional engines and was rolled back at the end (.e.g due to
      a deadlock) the changes were not written to the binary log, including the creation
      of the temporary table.
            
      To fix the problem, we have set the OPTION_KEEP_LOG into the
      thd->variables.option_bits when a "CREATE TEMPORARY TABLE
      SELECT * FROM" is executed.

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

3088 Alfranio Correia	2010-06-01
      Post-merge fix for BUG#53421.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:marko.makela@oracle.com-20100601134335-ccthwwru23kn09qw) (merge vers: 5.1.48) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:marko.makela@oracle.com-20100601134335-ccthwwru23kn09qw) (pib:16)

Documented bugfix in the 5.5.5 changelog as follows:

        When a CREATE TEMPORARY TABLE ... SELECT statement was executed
        within a transaction that updated only transactional engines and
        was later rolled back (for example, due to a deadlock) the
        changes--including the creation of the temporary table--were not 
        written to the binary log, which caused subsequent updates to this 
        table to fail on the slave.

Closed.