MySQL Bugs: #54872: MBR: replication failure caused by using tmp table inside transaction

Bug #54872	MBR: replication failure caused by using tmp table inside transaction
Submitted:	28 Jun 2010 19:42	Modified:	10 Aug 2010 14:28
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.5-m3	OS:	Any
Assigned to:	Alfranio Junior	CPU Architecture:	Any
Tags:	regression

Description:
In MBR, changes made to a (non-transactional) temporary table inside a transaction are not written into binary log if the transaction is rolled back. It easily leads to replication failure if the table is used later.

This is a regression from 5.5.4. 
The difference is that in 5.5.4 the event was written into the binary log right away in the middle of transaction, regardless of binlog_direct_non_transactional_updates value, while in 5.5.5 it is not, also regardless the variable value.

It does affect RBR where the temporary table is completely ignored.

It also does not happen with SBR where the event is written into the binlog after the transaction is rolled back.

How to repeat:
# Run as  perl ./mtr --mysqld=--binlog_format=mixed <test name>
--source include/master-slave.inc
--source include/have_innodb.inc
--source include/have_binlog_format_mixed.inc

# Setting the variable just in case, although in 5.5.5 it does
# not have any effect for MIXED
SET binlog_direct_non_transactional_updates=1;

CREATE TEMPORARY TABLE tmp ( i INT ) ENGINE = MyISAM;
CREATE TABLE tin ( i INT NOT NULL PRIMARY KEY ) ENGINE = InnoDB;
INSERT INTO tmp VALUES (1),(1);

START TRANSACTION;
DELETE FROM tmp;
--disable_warnings
ROLLBACK;
--enable_warnings

INSERT INTO tin SELECT * FROM tmp;

SHOW BINLOG EVENTS;
--sync_slave_with_master

--connection master
DROP TABLE tin;
--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/112496

3079 Alfranio Correia	2010-06-29
      BUG#54872 MBR: replication failure caused by using tmp table inside transaction
      
      This bug is a regression caused by BUG#51894, which makes temporary tables to go
      into the trx-cache if there is an on-going. The patch for BUG#51894 ignores that
      the trx-cache may have updates to temporary non-transactional tables that must
      be written to the binary log while rolling back the transaction.
      
      In this patch, we fix this problem by writing the content of the trx-cache to
      the binary log while rolling back a transaction if a non-transactional table was
      updated and the binary logging format is STATEMENT or MIXED, i.e.
        thd->is_current_stmt_binlog_format_row() == FALSE.

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

3079 Alfranio Correia	2010-06-30
      BUG#53259 Unsafe statement binlogged in statement format w/MyIsam temp tables
      BUG#54872 MBR: replication failure caused by using tmp table inside transaction 
            
      Changed criteria to classify a statement as unsafe in order to reduce the
      number of spurious warnings. So a statement is classified as unsafe when
      there is on-going transaction at any point of the execution if:
      
      1. The mixed statement is about to update a transactional table and
      a non-transactional table.
      
      2. The mixed statement is about to update a temporary transactional
      table and a non-transactional table.
            
      3. The mixed statement is about to update a transactional table and
      read from a non-transactional table.
      
      4. The mixed statement is about to update a temporary transactional
      table and read from a non-transactional table.
      
      5. The mixed statement is about to update a non-transactional table
      and read from a transactional table when the isolation level is
      lower than repeatable read.
      
      After updating a transactional table if:
      
      6. The mixed statement is about to update a non-transactional table
      and read from a temporary transactional table.
       
      7. The mixed statement is about to update a non-transactional table
       and read from a temporary transactional table.
      
      8. The mixed statement is about to update a non-transactionala table
         and read from a temporary non-transactional table.
           
      9. The mixed statement is about to update a temporary non-transactional
      table and update a non-transactional table.
           
      10. The mixed statement is about to update a temporary non-transactional
      table and read from a non-transactional table.
           
      11. A statement is about to update a non-transactional table and the
      option variables.binlog_direct_non_trans_update is OFF.
      
      The reason for this is that locks acquired may not protected a concurrent
      transaction of interfering in the current execution and by consequence in
      the result. So the patch reduced the number of spurious unsafe warnings.
      
      Besides we fixed a regression caused by BUG#51894, which makes temporary
      tables to go into the trx-cache if there is an on-going transaction. In
      MIXED mode, the patch for BUG#51894 ignores that the trx-cache may have
      updates to temporary non-transactional tables that must be written to the
      binary log while rolling back the transaction.
            
      So we fix this problem by writing the content of the trx-cache to the
      binary log while rolling back a transaction if a non-transactional
      temporary table was updated and the binary logging format is MIXED.

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)

Documented bugfix int he 5.5.6 changelog as follows:

        When using mixed-format replication, changes made to a
        non-transactional temporary table within a transaction were not
        written into the binary log when the transaction was rolled
        back. This could lead to a failure in replication if the
        temporary table was used again afterwards.

Set NM status, waiting for merge to next-mr tree.

Already merged to next-mr; closed.

Set wrong status previously, closing.