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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.