Bug #45825 INSERT DELAYED is not unsafe: logged in statement format
Submitted: 29 Jun 2009 14:55 Modified: 3 Sep 2010 17:20
Reporter: Sven Sandberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1+ OS:Any
Assigned to: Sven Sandberg CPU Architecture:Any

[29 Jun 2009 14:55] Sven Sandberg
Description:
Statements may be marked "unsafe", meaning that writing them to the binlog in statement format may cause the slave to diverge. If binlog_format=MIXED, then unsafe statements are logged in row format. If binlog_format=STATEMENT, then unsafe statements produce a warning.

One unsafe statement is INSERT DELAYED. Currently, however, INSERT DELAYED only causes the statement to be logged in row format if binlog_format=MIXED. They do not cause a warning to be generated if binlog_format=STATEMENT.

How to repeat:
source include/have_binlog_format_statement.inc;

CREATE TABLE t1 (a INT);

SET SESSION binlog_format = STATEMENT;
# Logged in statement format but no warning is given
INSERT DELAYED INTO t1 VALUES (1), (2);

SET SESSION binlog_format = MIXED;
# Logged in row format
INSERT DELAYED INTO t1 VALUES (1), (2);

source include/show_binlog_events.inc;
exit;

See also binlog_unsafe: the result file does not show any warning for INSERT DELAYED.

Suggested fix:
I'll fix this when I fix BUG#39934.
[13 Jul 2009 11:11] Sven Sandberg
In fact, the bug is even more serious than reported above. INSERT DELAYED is not even logged in row format if binlog_format=MIXED (run the above test case to see). Changing bug title accordingly.
[18 Jan 2010 12:06] Bugs System
Pushed into 6.0.14-alpha (revid:alik@ibmvm-20100118120357-hnzhgadkpzqfnvsc) (version source revid:alik@ibmvm-20100118115413-kd3klpine09yyktw) (merge vers: 6.0.14-alpha) (pib:16)
[18 Jan 2010 12:07] Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100118120111-73dulkgc893it4r9) (version source revid:alik@ibmvm-20100118115335-0stecyzftqm7bqx6) (pib:16)
[18 Jan 2010 14:27] Jon Stephens
DOcumented bugfix in the 5.6.0 and 6.0.14 changelogs as follows:

        Even though INSERT DELAYED statements are unsafe for
        statement-based replication, they caused the statement only to
        be logged in row format when the binary logging format was
        MIXED, but did not cause a warning to be generated when the
        binary logging format was STATEMENT.

Waiting for other merges to 5.1+, set status NDI.
[6 Mar 2010 11:00] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@linux-rbsx-20100118220048-5vnyqi5ghsbgmdsd) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 2:13] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.

Setting report to Need Merge pending push to 5.1.x.
[3 Sep 2010 17:20] Jon Stephens
Need Merge status appears to have been set in error; closing.