Bug #58684 Unexpected unsafety warning on automatic event drop
Submitted: 2 Dec 2010 23:45
Reporter: Elena Stepanova Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.6, 5.5.8 OS:Any
Assigned to: Luis Soares CPU Architecture:Any

[2 Dec 2010 23:45] Elena Stepanova
Description:
If an event contains statements unsafe in SBR mode, and has a limited life time, an extra unsafety warning is produced when event scheduler automatically drops the event. Unlike warnings written upon event execution, the extra warning gives the DROP EVENT as an offending statement.

For the test from 'How to repeat' the following warnings are raised:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. Statement: CREATE PROCEDURE `ev`() SQL SECURITY INVOKER INSERT INTO t1 VALUES (2)
[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. Statement: DROP EVENT `test`.`ev`

How to repeat:
--source include/have_log_bin.inc

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
DROP TRIGGER IF EXISTS tr;
DROP EVENT IF EXISTS ev;

CREATE TABLE t1 (f INT);
CREATE TABLE t2
(i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT);
CREATE TRIGGER tr AFTER INSERT ON t1
FOR EACH ROW INSERT INTO t2 (f) VALUES (new.f);

SET binlog_format = STATEMENT;

let $event_scheduler = `SELECT @@event_scheduler`;
SET GLOBAL event_scheduler = ON;

CREATE EVENT ev ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
DO INSERT INTO t1 VALUES (2);

--echo # Wait till the event works at least once
let $wait_condition=
SELECT COUNT(*) > 0 FROM t2;
--source include/wait_condition.inc

# cleanup

DROP TABLE t1, t2;
eval SET GLOBAL event_scheduler = $event_scheduler;

--echo # Check warnings in the error file

Suggested fix:
There is nothing wrong with the DROP EVENT statement, so there should be no warning (and if we execute DROP EVENT manually, no warnings are thrown).