MySQL Bugs: #57666: Unclear warning with broken text in error log on INSERT DELAYED

Bug #57666	Unclear warning with broken text in error log on INSERT DELAYED
Submitted:	22 Oct 2010 17:16	Modified:	6 Jan 2011 4:29
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.7, 5.6.99	OS:	Any
Assigned to:	Daogang Qu	CPU Architecture:	Any

Description:
INSERT DELAYED statement with a single inserted value does not return any visible warnings; however, the warning might be still written into the error log, e.g.:

101022 18:00:30 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is
unsafe because it uses INSERT DELAYED. This is unsafe because the times when rows are inserted cannot be predicted. Statement: t

(this is a full quote)

I see two problems with it:

1) The text of the statement is broken, it only contains the table name but not the statement;

2) Appearance of the warning, as well as the actual binlogging format for the statement, does not depend either current global or session value of the binlog_format, but on the global value effective by the time the delayed thread was created. While it might be the expected behavior, the warning itself is quite confusing.

In 5.1 the warning also appears, but it contains the full statement text.

How to repeat:
SET GLOBAL binlog_format = STATEMENT;
DROP TABLE IF EXISTS t;
CREATE TABLE t ( i INT ) ENGINE = MyISAM;
INSERT DELAYED INTO t VALUES (1);

# Query OK, 1 row affected (0.00 sec)

# Check the error log to see the warning

SET GLOBAL binlog_format = ROW;
INSERT DELAYED INTO t VALUES (1);

# Query OK, 1 row affected (0.00 sec)

# Check the error log to see the warning

Suggested fix:
If the warning really should be there, at least fix the text of the statement.

The second problem is partially related to bug#49567, although meanwhile it could help if the text of the warning itself was more explanatory in terms of where the BINLOG_FORMAT=STATEMENT comes from.

Related: BUG#54579.

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

3118 Dao-Gang.Qu@sun.com	2010-11-11
      Bug #57666  Unclear warning with broken text in error log on INSERT DELAYED
      
      It is not necessary to support INSERT DELAYED for a single value insert,
      while we do not support that for multi-values insert when binlog is
      enabled in SBR.
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED for single value insert as multi-values insert
      did when binlog is enabled. Then it's safe. And binlog it as
      INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Added test case for bug#57666.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Updated for bug#57666
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Updated for bug#57666
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Updated for bug#57666
     @ sql/sql_insert.cc
        Updated code for the following things:
        1. Upgrade the lock_type to TL_WRITE from TL_WRITE_DELAYED
        for INSERT DELAYED for single value insert as multi-values
        insert did when binlog is enabled.
        2. Clear code to not binlog INSERT DELAYED STMT in SBR.
        3. Get rid of privilege check for log_on.

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

3129 Dao-Gang.Qu@sun.com	2010-11-16
      Bug #57666  Unclear warning with broken text in error log on INSERT DELAYED
      
      It is not necessary to support INSERT DELAYED for a single value insert,
      while we do not support that for multi-values insert when binlog is
      enabled in SBR.
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED for single value insert as multi-values insert
      did when binlog is enabled. Then it's safe. And binlog it as
      INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Added test case for bug#57666.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Updated for bug#57666
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Updated for bug#57666
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Updated for bug#57666
     @ mysql-test/suite/rpl/r/rpl_stm_insert_delayed.result
        Updated for bug#57666
     @ sql/sql_insert.cc
        Updated code for the following things:
        1. Upgrade the lock_type to TL_WRITE from TL_WRITE_DELAYED
        for INSERT DELAYED for single value insert as multi-values
        insert did when binlog is enabled.
        2. Clear code to not binlog INSERT DELAYED STMT in SBR.
        3. Get rid of privilege check for log_on.

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

3149 Dao-Gang.Qu@sun.com	2010-11-29
      Bug #57666  Unclear warning with broken text in error log on INSERT DELAYED
      
      It is not necessary to support INSERT DELAYED for a single value insert,
      while we do not support that for multi-values insert when binlog is
      enabled in SBR.
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED for single value insert as multi-values insert
      did when binlog is enabled. Then it's safe. And binlog it as
      INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Added test case for bug#57666.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Updated for bug#57666
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Updated for bug#57666
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Updated for bug#57666
     @ mysql-test/suite/rpl/r/rpl_stm_insert_delayed.result
        Updated for bug#57666
     @ sql/sql_insert.cc
        Updated code for the following things:
        1. Upgrade the lock_type to TL_WRITE from TL_WRITE_DELAYED
        for INSERT DELAYED for single value insert as multi-values
        insert did when binlog is enabled.
        2. Clear code to not binlog INSERT DELAYED STMT in SBR.
        3. Get rid of privilege check for log_on.

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

3406 Dao-Gang.Qu@sun.com	2010-11-29 [merge]
      Bug #57666  Unclear warning with broken text in error log on INSERT DELAYED
      
      It is not necessary to support INSERT DELAYED for a single value insert,
      while we do not support that for multi-values insert when binlog is
      enabled in SBR.
      
      The lock_type is upgrade to TL_WRITE from TL_WRITE_DELAYED for
      INSERT DELAYED for single value insert as multi-values insert
      did when binlog is enabled. Then it's safe. And binlog it as
      INSERT without DELAYED.
     @ mysql-test/extra/binlog_tests/binlog_insert_delayed.test
        Added test for bug#57666
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Updated for bug#57666
     @ mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result
        Updated for bug#57666
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Updated for bug#57666
     @ mysql-test/suite/rpl/r/rpl_stm_insert_delayed.result
        Updated for bug#57666
     @ sql/sql_insert.cc
        Updated code for the following things:
        1. Upgrade the lock_type to TL_WRITE from TL_WRITE_DELAYED
        for INSERT DELAYED for single value insert as multi-values
        insert did when binlog is enabled.
        2. Clear code to not binlog INSERT DELAYED STMT in SBR.
        3. Get rid of privilege check for log_on.

Pushed into mysql-5.5-bugteam and mysql-trunk-bugfixing.

Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)

Issue doesn't appear in a 5.6 release (so no 5.6 changelog entry needed); waiting for push to 5.5 tree.

Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:dao-gang.qu@sun.com-20101129030416-wyveoivfakyl7e7b) (merge vers: 5.5.8) (pib:24)

Documented fix in the 5.5.9 changelog as follows:

        While an INSERT DELAYED statement with a single inserted value
        does not return any visible warnings, such a warning could be
        still written into the error log.

Closed.