MySQL Bugs: #45825: INSERT DELAYED is not unsafe: logged in statement format

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

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.

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.

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)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100118120111-73dulkgc893it4r9) (version source revid:alik@ibmvm-20100118115335-0stecyzftqm7bqx6) (pib:16)

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.

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)

Moved 5.6.0 changelog entry to 5.5.3.

Setting report to Need Merge pending push to 5.1.x.

Need Merge status appears to have been set in error; closing.