MySQL Bugs: #53560: CREATE TEMP./DROP TEMP. are not binlogged correctly after a failed statement

Bug #53560	CREATE TEMP./DROP TEMP. are not binlogged correctly after a failed statement
Submitted:	11 May 2010 11:52	Modified:	14 Oct 2010 12:30
Reporter:	Alfranio Tavares Correia Junior	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.44	OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any
Tags:	binlog, statement

Description:
If there is an on-going transaction and a temporary table is created or dropped, any failed statement that follows the "create" or "drop commands" triggers a rollback and by consequence the slave will go out sync because the binary log will have a wrong sequence of events.

How to repeat:
--- TEST CASE ---

CREATE TABLE t_myisam (id INT, name VARCHAR(64), PRIMARY KEY (id)) engine= MyIsam;
INSERT INTO t_myisam (id) VALUES(1);
CREATE TABLE t_innodb (id INT, name VARCHAR(64)) engine= Innodb;
INSERT INTO t_innodb (id) VALUES(1);

BEGIN;
INSERT INTO t_innodb(id) VALUES(2);
INSERT INTO t_myisam(id) VALUES(3);
CREATE TEMPORARY TABLE x (id INT);
--error 1062
INSERT INTO t_myisam(id) VALUES(4),(1);
INSERT INTO t_innodb(id) VALUES(5);
COMMIT;

--- RESULT ---

SELECT * FROM t_innodb;
id     name
1      NULL
2      NULL
5      NULL

SELECT * FROM t_innodb;
id     name
1      NULL
5      NULL

master-bin.000001       1189    Query   1       1257    BEGIN
master-bin.000001       1257    Query   1       1354    use `test`; INSERT INTO t_innodb(id) VALUES(2)
master-bin.000001       1354    Query   1       1451    use `test`; INSERT INTO t_myisam(id) VALUES(3)
master-bin.000001       1451    Query   1       1547    use `test`; CREATE TEMPORARY TABLE x (id INT)
master-bin.000001       1547    Query   1       1648    use `test`; INSERT INTO t_myisam(id) VALUES(4),(1)
master-bin.000001       1648    Query   1       1719    ROLLBACK
master-bin.000001       1719    Query   1       1787    BEGIN
master-bin.000001       1787    Query   1       1884    use `test`; INSERT INTO t_innodb(id) VALUES(5)
master-bin.000001       1884    Xid     1       1911    COMMIT /* xid=30 */

re:

--- RESULT ---

SELECT * FROM t_innodb;
id     name
1      NULL
2      NULL
5      NULL

SELECT * FROM t_innodb;
id     name
1      NULL
5      NULL

That is unlabeled. Is it intended to show the differing results of the test script on the master and slave, respectively?

Please, read the result set at "[11 May 13:52] Alfranio Correia" as

--- RESULT ---

#### MASTER #####
SELECT * FROM t_innodb;
id     name
1      NULL
2      NULL
5      NULL

#### SLAVE #####
SELECT * FROM t_innodb; # SLAVE
id     name
1      NULL
5      NULL

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

3386 Alfranio Correia	2010-05-19
      BUG#53560 CREATE TEMP./DROP TEMP. are not binglogged correctly after a failed statement
      
      This patch fixes two problems described as follows:
      
      1 - If there is an on-going transaction and a temporary table is created or
      dropped, any failed statement that follows the "create" or "drop commands"
      triggers a rollback and by consequence the slave will go out sync because
      the binary log will have a wrong sequence of events.
      
      To fix the problem, we changed the expression that evaluates when the
      cache should be flushed after either the rollback of a statment or
      transaction.
      
      2 - When a "CREATE TEMPORARY TABLE SELECT * FROM" was executed the
      OPTION_KEEP_LOG was not set into the thd->options. 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->options
      when a "CREATE TEMPORARY TABLE SELECT * FROM" is executed.
     @ sql/log.cc
        Reorganized the code based on the following functions:
        
        - bool ending_trans(const THD* thd, const bool all);
        - bool trans_has_updated_non_trans_table(const THD* thd);
        - bool trans_has_no_stmt_committed(const THD* thd, const bool all);
        - bool stmt_has_updated_non_trans_table(const THD* thd);
     @ sql/log.h
        Added functions to organize the code in log.cc.
     @ sql/log_event.cc
        Removed the OPTION_KEEP_LOG since it must be used only when
        creating and dropping temporary tables.
     @ sql/log_event_old.cc
        Removed the OPTION_KEEP_LOG since it must be used only when
        creating and dropping temporary tables.
     @ sql/sql_parse.cc
        When a "CREATE TEMPORARY TABLE SELECT * FROM" was executed the
        OPTION_KEEP_LOG was not set into the thd->options.
              
        To fix the problem, we have set the OPTION_KEEP_LOG into the
        thd->options when a "CREATE TEMPORARY TABLE SELECT * FROM"
        is executed.

Patch pushed to mysql-5.1-bugteam and a null-merge pushed to mysql-trunk-merge.

Pushed into 5.1.49 (revid:build@mysql.com-20100719143034-omcma40sblwmay3x) (version source revid:alfranio.correia@sun.com-20100627173142-5b1ap1lt37g9yqz3) (merge vers: 5.1.48) (pib:16)

Documented in the 5.1.49 changelog as follows:

        Two related issues involving temporary tables and transactions
        were introduced by a fix made in MySQL 5.1.37:
        
            1. When a temporary table was created or dropped within a
            transaction, any failed statement that following the CREATE
            TEMPORARY TABLE or DROP TEMPORARY TABLE statement triggered
            a rollback, which caused the slave diverge from the master.
            
            2. When a CREATE TEMPORARY TABLE ... SELECT * FROM ...
            statement was executed within a transaction in which only
            tables using transactional storage engines were used and the
            transaction was rolled back at the end, the
            changes--including the creation of the temporary
            table--were not written to the binary log.
            
        The current fix restores the correct behavior in both of these
        cases.
        
        Introduced by the fix for Bug#43929.

Set NM status, waiting for 5.5 merge.

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)

Also documented fix in the 5.5.6 changelog.

Closed.

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)

Already documented in 5.1.49 changelog, no new changelog entry required.

 
Setting back to Closed state.