Bug #45824 Unsafe stmt in proc doesn't generate warning if binlog_format=STATEMENT
Submitted: 29 Jun 2009 14:50 Modified: 10 Jul 2009 17:30
Reporter: Sven Sandberg Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1+ OS:Any
Assigned to: Sven Sandberg CPU Architecture:Any
Tags: replication, stored procedure, unsafe

[29 Jun 2009 14:50] 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.

Unsafe sub-statements in a stored procedure/function/trigger/prepared statement cause the top-level statement to be unsafe.

Currently, however, unsafe sub-statements only cause the top-level 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 VARCHAR(1000));

--delimiter |
CREATE PROCEDURE proc ()
BEGIN
  INSERT INTO t1 VALUES (UUID());
END|
--delimiter ;

# Logged in statement mode, but no warning!
CALL proc();

SET binlog_format=MIXED;
# Logged in row format.
CALL proc();

source include/show_binlog_events.inc;

DROP TABLE t1;
DROP PROCEDURE proc;
exit;

See also rpl_ndb_stm_innodb.result: the procedure tpcp.trans should generate warnings because it uses UUID() but doesn't.

Suggested fix:
I'll fix this when I fix BUG#39934
[10 Jul 2009 17:30] Sven Sandberg
Uhm, sorry, I must have made a mistake, I can't repeat this any more. Probably I was executing in mixed mode. Setting to can't repeat.