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

[26 Jun 2009 10:25] Sven Sandberg
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)
[10 Jul 2009 9:08] Susanne Ebrecht
Set to verified by Sven Sandberg according to his comment above and after chatting with him on IRC
[10 Jul 2009 18:14] Sven Sandberg
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.
[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:06] 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:16] Jon Stephens
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+).
[6 Mar 2010 11:03] 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:14] 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 16:58] Jon Stephens
Need Merge status apparently in error -- no new changelog entries required, closing.