MySQL Bugs: #54579: Wrong unsafe warning for INSERT DELAYED in SBR

Bug #54579	Wrong unsafe warning for INSERT DELAYED in SBR
Submitted:	17 Jun 2010 9:45	Modified:	15 Nov 2010 13:16
Reporter:	Daogang Qu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5+	OS:	Any
Assigned to:	Daogang Qu	CPU Architecture:	Any
Tags:	parsing for replication, regression, replication, unsafe

Description:
The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for INSERT DELAYED
when inserting multi values in one statement. So we shouldn't cause the following
warning:

Note	1592	Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statement uses INSERT DELAYED. This is unsafe because the time when rows are inserted cannot be predicted.
 insert delayed into t1(a) values(50),(60);

How to repeat:
source include/have_binlog_format_statement.inc;
source include/master-slave.inc;

create table t1(a int) ENGINE = MyISAM;
insert delayed into t1(a) values(30),(40);
insert delayed into t1(a) values(50),(60);
insert delayed into t1(a) values(70);
insert delayed into t1(a) values(80),(90),(100),(110);

drop table t1;
sync_slave_with_master;

Suggested fix:
Don't cause the warning for INSERT DELAYED.

corrected bug category to Server: Replication.

The way we currently log, I think INSERT DELAYED is actually unsafe:

 1. INSERT DELAYED is executed as INSERT when binlogging is turned on,
    but it is still logged as INSERT DELAYED.

 2. The slave SQL thread executes INSERT DELAYED as INSERT. So the slave SQL
    thread is safe.

 3. However, mysqlbinlog executes INSERT DELAYED as INSERT DELAYED (if
    binlogging is turned off). Hence, INSERT DELAYED is not safe.

I've been unable to reproduce wrong behavior. It's hard because it's a race. It can't be synchronized with DBUG_SYNCPOINT because the delayed_insert thread is not a client thread and won't respond to sync points. Still I believe it can happen.

We have three alternatives:

A1. Since INSERT DELAYED is not safe, mark this as Not A Bug

A2. Since INSERT DELAYED is only unsafe when replayed by mysqlbinlog, add
    a clarification to the warning message.

A3. Make INSERT DELAYED safe by logging it as INSERT without DELAYED.

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

3215 Dao-Gang.Qu@sun.com	2010-08-19
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
      
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Test result for BUG#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

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

3215 Dao-Gang.Qu@sun.com	2010-08-19
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
      
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Test result for BUG#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

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

3215 Dao-Gang.Qu@sun.com	2010-08-19
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
      
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Test result for BUG#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

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

3215 Dao-Gang.Qu@sun.com	2010-08-19
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
      
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Test result for BUG#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

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

3215 Dao-Gang.Qu@sun.com	2010-08-19
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
      
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Test result for BUG#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

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

3215 Dao-Gang.Qu@sun.com	2010-08-19
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
      
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Test result for BUG#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

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

3215 Dao-Gang.Qu@sun.com	2010-08-19
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
      
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Test result for BUG#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

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

3192 Dao-Gang.Qu@sun.com	2010-08-22
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
            
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
            
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Test result for BUG#54579.
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

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

3192 Dao-Gang.Qu@sun.com	2010-08-23
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
            
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
            
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Test result for BUG#54579.
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

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

3192 Dao-Gang.Qu@sun.com	2010-08-25
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
            
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
            
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Test result for BUG#54579.
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

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

3261 Dao-Gang.Qu@sun.com	2010-08-30 [merge]
      Bug #54579  	Wrong unsafe warning for INSERT DELAYED in SBR
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED when inserting multi values in one statement.
      It's safe. But it causes an unsafe warning in SBR.
                  
      Make INSERT DELAYED safe by logging it as INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Updated the test file to test multi INSERT DELAYED statement
        is no longer causes an unsafe warning and binlogged as INSERT
        without DELAYED.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated for the patch of bug#54579.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Updated for the patch of bug#54579.
     @ sql/sql_insert.cc
        Added code to genetate a new query string for removing
        DELAYED keyword for multi INSERT DEALAYED statement.
     @ sql/sql_yacc.yy
        Added code to record the DELAYED keyword position and remove the setting
        of unsafe statement for INSERT DELAYED statement

Pushed into mysql-5.5-bugfixing, mysql-trunk-bugfixing and mysql-next-mr-bugfixing.

http://lists.mysql.com/commits/117115
http://lists.mysql.com/commits/117116
http://lists.mysql.com/commits/117117

Clarification for documentation: 

Before this patch, INSERT DELAYED did this:
 - When binlog_format=statement, it generated a warning, was executed as
   INSERT without DELAYED, and was logged as INSERT DELAYED.
 - When binlog_format=mixed or row, it did not generate a warning, was
   executed as INSERT DELAYED and logged in row format.

After this patch, INSERT DELAYED does this:
 - When binlog_format=statement, it does not generate a warning, is executed
   as INSERT without DELAYED, and logged as INSERT without DELAYED.
 - When binlog_format=row, there is no change.

Pushed into mysql-trunk 5.6.1-m4 (revid:alexander.nozdrin@oracle.com-20101002180948-852x1cuv7c6i85ea) (version source revid:alexander.nozdrin@oracle.com-20101002180857-an32jpuwzemsp4f2) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alexander.nozdrin@oracle.com-20101002180831-590ka2tuit9qoxbb) (version source revid:alexander.nozdrin@oracle.com-20101002180831-590ka2tuit9qoxbb) (merge vers: 5.5.7-rc) (pib:21)

Documented as Important Change in the 5.5.7 and 5.6.1 changelogs, as follows:

        The behavior of INSERT DELAYED statements when using
        statement-based replication has changed as follows:

        Previously, when using --binlog_format=STATEMENT, a warning was
        generated in the binary log when executing INSERT DELAYED; now,
        no warning is logged in such cases.

        Previously, when using --binlog_format=STATEMENT, INSERT DELAYED
        was logged as INSERT DELAYED; now, it is logged as an INSERT,
        without the DELAYED option.

        However, when --binlog_format=STATEMENT, INSERT DELAYED
        continues to be executed as INSERT (without the DELAYED option).
        The behavior of INSERT DELAYED remains unchanged when using
        --binlog_format=ROW or --binlog_format=MIXED (INSERT DELAYED
        generates no warnings, is executed as INSERT DELAYED, and
        is logged using the row-based format).

Also updated 5.5 and 5.6 versions of "INSERT DELAYED Syntax" with relevant info. 

Closed.

Related: BUG#57666.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (pib:21)

Already documented for 5.5 and 5.6 -- returning to Closed state.