MySQL Bugs: #45785: LIMIT in function does not cause RBL if binlog

Bug #45785	LIMIT in function does not cause RBL if binlog_format=MIXED
Submitted:	26 Jun 2009 10:25	Modified:	3 Sep 2010 16:58
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:
If binlog_format=MIXED, statements containing a LIMIT clause shall be written as row events to the binlog (because the statement may be nondeterministic).

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

How to repeat:
Run this test case, then inspect the binlog and verify that the call to func_limit is 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_limit ()
  RETURNS INT
  BEGIN
    INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
    RETURN 1;
  END|

DELIMITER ;|

INSERT INTO t2 VALUES (func_limit());

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

Update: I can't repeat this if I replace stored function by stored procedure (probably because stored procedures are unrolled in the binlog). Changing bug title.

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:

        When using MIXED binary logging format, statements containing a
        LIMIT clause in stored routines were not written as row events
        to the log.

Set status NDI, waiting for merges (5.1+).

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 apparently in error -- no new changelog entries required, closing.