Description:
If an event contains a statement unsafe for SBR, a corresponding warning is written into the error log. However, the warning does not say that it was created upon the event execution and does not display the exact unsafe statement; instead, it includes the CREATE statement for event, which is incorrect as the event creation as such is safe enough (and the DDL does not cause warnings).
Output for the test from 'How to repeat' section:
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;
SET GLOBAL event_scheduler = ON;
CREATE EVENT ev ON SCHEDULE EVERY 1 SECOND DO
INSERT INTO t1 VALUES (2);
# No warnings here:
SHOW WARNINGS;
Level Code Message
# Wait till the event works at least once
DROP TABLE t1, t2;
SET GLOBAL event_scheduler = OFF;
# Check warnings in the error file
main.test [ fail ] Found warnings/errors in server log file!
Test ended at 2010-12-02 23:24:51
line
101203 1:24:51 [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)
^ Found warnings in mysqld.1.err
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 EVERY 1 SECOND DO
INSERT INTO t1 VALUES (2);
--echo # No warnings here:
SHOW WARNINGS;
--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:
In addition to the warning quoted in the description, there is also a [Note] record produced by Event Scheduler. The note gives the name of the event, and repeats the warning text, except for the event definition. Probably the optimal way, if it's possible at all, would be to merge the two, and produce only one [Warning] record which would give the warning text, the event name, and the exact unsafe statement from the event definition (which might be quite long).
Description: If an event contains a statement unsafe for SBR, a corresponding warning is written into the error log. However, the warning does not say that it was created upon the event execution and does not display the exact unsafe statement; instead, it includes the CREATE statement for event, which is incorrect as the event creation as such is safe enough (and the DDL does not cause warnings). Output for the test from 'How to repeat' section: 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; SET GLOBAL event_scheduler = ON; CREATE EVENT ev ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (2); # No warnings here: SHOW WARNINGS; Level Code Message # Wait till the event works at least once DROP TABLE t1, t2; SET GLOBAL event_scheduler = OFF; # Check warnings in the error file main.test [ fail ] Found warnings/errors in server log file! Test ended at 2010-12-02 23:24:51 line 101203 1:24:51 [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) ^ Found warnings in mysqld.1.err 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 EVERY 1 SECOND DO INSERT INTO t1 VALUES (2); --echo # No warnings here: SHOW WARNINGS; --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: In addition to the warning quoted in the description, there is also a [Note] record produced by Event Scheduler. The note gives the name of the event, and repeats the warning text, except for the event definition. Probably the optimal way, if it's possible at all, would be to merge the two, and produce only one [Warning] record which would give the warning text, the event name, and the exact unsafe statement from the event definition (which might be quite long).