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

[22 Oct 2010 17:16] Elena Stepanova 
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.
[28 Oct 2010 8:44] Luis Soares 
Related: BUG#54579.
[11 Nov 2010 9:49] Bugs System 
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.
[16 Nov 2010 3:23] Bugs System 
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.
[29 Nov 2010 3:08] Bugs System 
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.
[29 Nov 2010 3:22] Bugs System 
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.
[29 Nov 2010 3:26] Daogang Qu 
Pushed into mysql-5.5-bugteam and mysql-trunk-bugfixing.
[5 Dec 2010 12:40] Bugs System 
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)
[14 Dec 2010 6:46] Jon Stephens 
Issue doesn't appear in a 5.6 release (so no 5.6 changelog entry needed); waiting for push to 5.5 tree.
[17 Dec 2010 12:51] Bugs System 
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)
[6 Jan 2011 4:29] Jon Stephens 
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.093 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.