MySQL Bugs: #53259: Unsafe statement binlogged in statement format w/MyIsam temp tables

Bug #53259	Unsafe statement binlogged in statement format w/MyIsam temp tables
Submitted:	28 Apr 2010 16:46	Modified:	2 Sep 2010 14:13
Reporter:	Omer Barnir (OCA)	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:
The following sequence:
   create table tbl1(i int);
   create temporary table tmp1 (j int);
   insert into tmp1 values(1),(2);
   insert into tbl1 select * from tmp1;

returns a warning that the statement is unsafe when tables are MyISAM.

warning is not observed when using --default-storage-engine=innodb (i.e. both tables are created with innodb storage engine).

Problem was not observed in 5.5.2 or in 5.1

How to repeat:
1) Download the attached 'warn_bug.test' file and place it in the 't' directory 
   under 'mysql-test'

2) To see the warning run:
   perl ./mysql-test-run.pl warn_bug.test

To see warning is not observed when both engines are innodb:
   perl ./mysql-test-run.pl --mysqld=--default-storage-engine=innodb warn_bug.test
 

Suggested fix:
It is not clear why 'insert into tb1 select * from tmp1 should give a warning about statement being unsafe in the first place. But if there is a reason, it is not an engine dependent one.

test case

Attachment: warn_bug.test (application/octet-stream, text), 369 bytes.

Hi all,

The following behavior is not a bug:

INSERT INTO t_myisam SELECT * FROM t_innodb;
Warnings:
Note    1592    Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.

INSERT INTO t_innodb SELECT * FROM t_myisam;
Warnings:
Note    1592    Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.

INSERT INTO t_myisam SELECT * FROM t_temp;
Warnings:
Note    1592    Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.

INSERT INTO t_temp SELECT * FROM t_myisam;
Warnings:
Note    1592    Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements that read from both transactional (or a temporary table of any engine type) and non-transactional tables and write to any of them are unsafe.

INSERT INTO t_innodb SELECT * FROM t_temp;

INSERT INTO t_temp SELECT * FROM t_innodb;

----------

This was introduced after BUG#51894 where changes to temporary tables are 
always kept in the cache and flushed upon commit. This means that a
temporary behaves "like" a transactional table and any mixed-statement, i.e.
t_innodb + t_myisam or t_temp + t_myisam, is unsafe.

If a temporary table was not handled "as" a transactional table:

BEGIN;
  INSERT INTO t_innodb SELECT * FROM t_myisam_temp; /* M-Statement */
  DROP TEMPORARY TABLE t_myisam_temp;
COMMIT;
      
the execution above would produce the following events into 
the binary log
      
BEGIN;
  DROP TEMPORARY TABLE t_myisam_temp;
COMMIT;
      
BEGIN;
   INSERT INTO t_innodb SELECT * FROM t_myisam_temp; /* ---> ERROR <--- */
COMMIT;

Cheers.

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

3045 Alfranio Correia	2010-05-17
      BUG#53259 Unsafe statement binlogged in statement format w/MyIsam temp tables
      
      After this patch, when there is no on-going transaction temporary tables are
      handled according to the type of engine where they were created. However, when
      there is an on-going transaction temporary tables are handled as transactional
      tables in the sense that statements that access them are written temporary
      tables are written to the binary log upon committing the transaction, keeping
      the behavior after BUG#51894.
      
      So, when temporary tables are accessed the following cases generate warning
      messages:
      
      1. No on-going transaction:
      
        1.1. INSERT INTO innodb_t SELECT * FROM myisam_temporary;
        1.2. INSERT INTO myisam_temporary SELECT * FROM innodb_t;
        1.3. INSERT INTO myisam_t SELECT * FROM innodb_temporary;
        1.4. INSERT INTO innodb_temporary SELECT * FROM myisam_t;
      
      2. On-going transaction:
        2.1. INSERT INTO myisam_t SELECT * FROM myisam_temporary;
        2.2. INSERT INTO myisam_temporary SELECT * FROM myisam_t;

See also BUG#52984.

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

3073 Alfranio Correia	2010-06-24
      BUG#53259 Unsafe statement binlogged in statement format w/MyIsam temp tables
      
      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 an on-going transaction and:
      
        1. It is a mixed statement that updates both types of tables.
            
        2. It is a mixed statement that updates a transactional table and reads 
        from a non-transactional table.
      
        3. It is a mixed statement that updates a non-transactional table and
        reads from a transactional table when the isolation level is lower than
        serializability.
      
      Or when there is an on-going transaction and a transactional table was
      already updated and:
      
        4. It is a mixed statement that updates a non-transactional table and
        reads from a transactional table.
      
        5. It is a statement that updates a non-transactional table and the
        option variables.binlog_direct_non_trans_update is OFF.
      
      Temporary tables are handled as transactional tables in regarding to how
      they are written to binary log. See details below.
      
      In particular, the patch has changed what follows:
      
        . It avoids classifying a mixed statement as unsafe where there is
        no on-going transaction.
      
        . When there is an on-going transaction and a mixed statement updated
        a non-transactional table and read from a transactional table, the
        statement is classified as unsafe only when the isolation level is not
        serializable or a transactional table was already updated.
      
        . A a statement that updates only a non-transactional table is classifed
        as unsafe if there is an on-going transaction and a transactional table
        was already updated and the option variables.binlog_direct_non_trans_update
        is OFF.
      
        . Temporary tables are handled as transactional tables in regarding to how
        they are written to binary log when there is an on-going transaction and a
        transactional table was already updated.

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

3073 Alfranio Correia	2010-06-24
      BUG#53259 Unsafe statement binlogged in statement format w/MyIsam temp tables
      
      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 an on-going transaction and:
      
        1. It is a mixed statement that updates both types of tables.
            
        2. It is a mixed statement that updates a transactional table and reads 
        from a non-transactional table.
      
        3. It is a mixed statement that updates a non-transactional table and
        reads from a transactional table when the isolation level is lower than
        serializability.
      
      Or when there is an on-going transaction and a transactional table was
      already updated and:
      
        4. It is a mixed statement that updates a non-transactional table and
        reads from a transactional table.
      
        5. It is a statement that updates a non-transactional table and the
        option variables.binlog_direct_non_trans_update is OFF.
      
      Temporary tables are handled as transactional tables in regarding to how
      they are written to binary log. See details below.
      
      In particular, the patch has changed what follows:
      
        . It avoids classifying a mixed statement as unsafe where there is
        no on-going transaction.
      
        . When there is an on-going transaction and a mixed statement updated
        a non-transactional table and read from a transactional table, the
        statement is classified as unsafe only when the isolation level is not
        serializable or a transactional table was already updated.
      
        . A a statement that updates only a non-transactional table is classified
        as unsafe if there is an on-going transaction and a transactional table
        was already updated and the option variables.binlog_direct_non_trans_update
        is OFF.
      
        . Temporary tables are handled as transactional tables in regarding to how
        they are written to binary log when there is an on-going transaction and a
        transactional table was already updated.

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

3079 Alfranio Correia	2010-06-28
      BUG#53259 Unsafe statement binlogged in statement format w/MyIsam temp tables
            
      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 an on-going transaction and:
            
        1. It is a mixed statement that updates both types of tables.
                  
        2. It is a mixed statement that updates a transactional table and reads 
        from a non-transactional table.
            
        3. It is a mixed statement that updates a non-transactional table and
        reads from a transactional table when the isolation level is lower than
        serializability.
            
      Or when there is an on-going transaction and a transactional table was
      already updated and:
            
        4. It is a mixed statement that updates a non-transactional table and
        reads from a transactional table.
            
        5. It is a statement that updates a non-transactional table and the
        option variables.binlog_direct_non_trans_update is OFF.
            
      Temporary tables are handled as transactional tables in regarding to how
      they are written to binary log. See details below.
            
      In particular, the patch has changed what follows:
            
        . It avoids classifying a mixed statement as unsafe where there is
        no on-going transaction.
            
        . When there is an on-going transaction and a mixed statement updated
        a non-transactional table and read from a transactional table, the
        statement is classified as unsafe only when the isolation level is not
        serializable or a transactional table was already updated.
            
        . A a statement that updates only a non-transactional table is classified
        as unsafe if there is an on-going transaction and a transactional table
        was already updated and the option variables.binlog_direct_non_trans_update
        is OFF.
            
        . Temporary tables are handled as transactional tables in regarding to how
        they are written to binary log when there is an on-going transaction and a
        transactional table was already updated.

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

3079 Alfranio Correia	2010-06-28
      BUG#53259 Unsafe statement binlogged in statement format w/MyIsam temp tables
            
      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 an on-going transaction and:
            
        1. It is a mixed statement that updates both types of tables.
                  
        2. It is a mixed statement that updates a transactional table and reads 
        from a non-transactional table.
            
        3. It is a mixed statement that updates a non-transactional table and
        reads from a transactional table when the isolation level is lower than
        serializability.
            
      Or when there is an on-going transaction and a transactional table was
      already updated and:
            
        4. It is a mixed statement that updates a non-transactional table and
        reads from a transactional table.
            
        5. It is a statement that updates a non-transactional table and the
        option variables.binlog_direct_non_trans_update is OFF.
            
      Temporary tables are handled as transactional tables in regarding to how
      they are written to binary log. See details below.
            
      In particular, the patch has changed what follows:
            
        . It avoids classifying a mixed statement as unsafe where there is
        no on-going transaction.
            
        . When there is an on-going transaction and a mixed statement updated
        a non-transactional table and read from a transactional table, the
        statement is classified as unsafe only when the isolation level is not
        repeatable read or a transactional table was already updated.
            
        . A a statement that updates only a non-transactional table is classified
        as unsafe if there is an on-going transaction and a transactional table
        was already updated and the option variables.binlog_direct_non_trans_update
        is OFF.
            
        . Temporary tables are handled as transactional tables in regarding to how
        they are written to binary log when there is an on-going transaction and a
        transactional table was already updated.

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

3080 Alfranio Correia	2010-06-28
      Post-push fix for BUG#53259.

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

3079 Alfranio Correia	2010-06-30
      BUG#53259 Unsafe statement binlogged in statement format w/MyIsam temp tables
            
      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 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.

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

3086 Alfranio Correia	2010-07-04
      Post-push fix for BUG#53259.

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

3086 Alfranio Correia	2010-07-04
      Post-push fix for BUG#53259.

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)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)

See also BUG#53452.

Documented in the 5.5.6 changelog as follows:

        A number of statements generated unnecessary warnings as
        potentially unsafe statements. (Due to the fix for BUG#51894, a
        temporary table is treated in this context as a transactional
        table, so that any mixed statement such as t_innodb + t_myisam
        or t_temp + t_myisam is flagged as unsafe.)

        To reduce the number of spurious warnings produced when this
        happened, some of the criteria used to classify a statements as
        safe or unsafe have been changed. For more information about
        handling of mixed statements, see "Transactional, 
        nontransactional, and mixed statements".

Updated 5.5/5.6 versions of "Replication and Transactions" with info from Alfranio.

Closed.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)

Already documented in 5.6.0 -- setting back to Closed.

No new changelog entry needed. returning to Closed state.