MySQL Bugs: #45784: INSERT DELAYED in SP does not cause RBL if binlog

Bug #45784	INSERT DELAYED in SP does not cause RBL if binlog_format=MIXED
Submitted:	26 Jun 2009 10:19	Modified:	13 Jul 2009 14:30
Reporter:	Sven Sandberg	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1+	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If binlog_format=MIXED, statements containing INSERT DELAYED shall be written as row events to the binlog (because INSERT DELAYED statements are nondeterministic).

This works, except if the statement containing INSERT DELAYED is in a stored procedure, trigger, function etc.

How to repeat:
Run this test case, then inspect the master binlog and verify that it's logged as statement:

source include/master-slave.inc;
source include/have_binlog_format_mixed.inc;

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);

DELIMITER |;

CREATE FUNCTION func_delayed_insert ()
  RETURNS INT
  BEGIN
    INSERT DELAYED INTO t1 VALUES (1), (2);
    RETURN 1;
  END|

DELIMITER ;|

INSERT INTO t2 VALUES (func_delayed_insert());

sync_slave_with_master;

exit;

Suggested fix:
The code for switching to row logging should be refactored so that:
 - the format is only changed inside decide_logging_format()
 - statements that are unsafe are marked as such during parsing (binlog format must not be changed at execution time or anything like that)

Set to verified by Sven Sandberg according to his comment above and after chatting with him on IRC

In fact, INSERT DELAYED is not unsafe at all. Marking this as a duplicate of BUG#45825.