MySQL Bugs: #45824: Unsafe stmt in proc doesn't generate warning if binlog

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

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

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.