==== Setup tables ==== CREATE TABLE t1 (a INT); CREATE TABLE t2 (a CHAR(40)); CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY); CREATE TABLE trigger_table (a CHAR(7)); CREATE TABLE trigger_table2 (a INT); ==== Non-deterministic statements ==== INSERT DELAYED INTO t1 VALUES (5); ==== Some variables that *should* be unsafe ==== ---- Insert directly ---- INSERT INTO t1 VALUES (@@global.sync_binlog); Warnings: Warning 1592 Statement is not safe to log in statement format. INSERT INTO t1 VALUES (@@session.insert_id); Warnings: Warning 1592 Statement is not safe to log in statement format. INSERT INTO t1 VALUES (@@global.auto_increment_increment); Warnings: Warning 1592 Statement is not safe to log in statement format. INSERT INTO t2 SELECT UUID(); Warnings: Warning 1592 Statement is not safe to log in statement format. INSERT INTO t2 VALUES (@@session.sql_mode); Warnings: Warning 1592 Statement is not safe to log in statement format. INSERT INTO t2 VALUES (@@global.init_slave); Warnings: Warning 1592 Statement is not safe to log in statement format. INSERT INTO t2 VALUES (@@hostname); Warnings: Warning 1592 Statement is not safe to log in statement format. ---- Insert from stored procedure ---- CREATE PROCEDURE proc() BEGIN INSERT INTO t1 VALUES (@@global.sync_binlog); INSERT INTO t1 VALUES (@@session.insert_id); INSERT INTO t1 VALUES (@@global.auto_increment_increment); INSERT INTO t2 SELECT UUID(); INSERT INTO t2 VALUES (@@session.sql_mode); INSERT INTO t2 VALUES (@@global.init_slave); INSERT INTO t2 VALUES (@@hostname); END| CALL proc(); Warnings: Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. ---- Insert from stored function ---- CREATE FUNCTION func() RETURNS INT BEGIN INSERT INTO t1 VALUES (@@global.sync_binlog); INSERT INTO t1 VALUES (@@session.insert_id); INSERT INTO t1 VALUES (@@global.auto_increment_increment); INSERT INTO t2 SELECT UUID(); INSERT INTO t2 VALUES (@@session.sql_mode); INSERT INTO t2 VALUES (@@global.init_slave); INSERT INTO t2 VALUES (@@hostname); RETURN 0; END| SELECT func(); func() 0 Warnings: Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. ---- Insert from trigger ---- CREATE TRIGGER trig BEFORE INSERT ON trigger_table FOR EACH ROW BEGIN INSERT INTO t1 VALUES (@@global.sync_binlog); INSERT INTO t1 VALUES (@@session.insert_id); INSERT INTO t1 VALUES (@@global.auto_increment_increment); INSERT INTO t2 SELECT UUID(); INSERT INTO t2 VALUES (@@session.sql_mode); INSERT INTO t2 VALUES (@@global.init_slave); INSERT INTO t2 VALUES (@@hostname); END| INSERT INTO trigger_table VALUES ('bye.'); Warnings: Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. ---- Insert from prepared statement ---- PREPARE p1 FROM 'INSERT INTO t1 VALUES (@@global.sync_binlog)'; PREPARE p2 FROM 'INSERT INTO t1 VALUES (@@session.insert_id)'; PREPARE p3 FROM 'INSERT INTO t1 VALUES (@@global.auto_increment_increment)'; PREPARE p4 FROM 'INSERT INTO t2 SELECT UUID()'; PREPARE p5 FROM 'INSERT INTO t2 VALUES (@@session.sql_mode)'; PREPARE p6 FROM 'INSERT INTO t2 VALUES (@@global.init_slave)'; PREPARE p7 FROM 'INSERT INTO t2 VALUES (@@hostname)'; EXECUTE p1; Warnings: Warning 1592 Statement is not safe to log in statement format. EXECUTE p2; Warnings: Warning 1592 Statement is not safe to log in statement format. EXECUTE p3; Warnings: Warning 1592 Statement is not safe to log in statement format. EXECUTE p4; Warnings: Warning 1592 Statement is not safe to log in statement format. EXECUTE p5; Warnings: Warning 1592 Statement is not safe to log in statement format. EXECUTE p6; Warnings: Warning 1592 Statement is not safe to log in statement format. EXECUTE p7; Warnings: Warning 1592 Statement is not safe to log in statement format. ---- Insert from nested call of triggers / functions / procedures ---- CREATE PROCEDURE proc1() INSERT INTO trigger_table VALUES ('ha!')| CREATE FUNCTION func2() RETURNS INT BEGIN CALL proc1(); RETURN 0; END| CREATE TRIGGER trig3 BEFORE INSERT ON trigger_table2 FOR EACH ROW BEGIN DECLARE tmp INT; SELECT func2() INTO tmp; END| CREATE PROCEDURE proc4() INSERT INTO trigger_table2 VALUES (1)| CREATE FUNCTION func5() RETURNS INT BEGIN CALL proc4; RETURN 0; END| PREPARE prep6 FROM 'SELECT func5()'| EXECUTE prep6; func5() 0 Warnings: Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. Warning 1592 Statement is not safe to log in statement format. ==== Variables that should *not* be unsafe ==== INSERT INTO t1 VALUES (@@session.pseudo_thread_id); INSERT INTO t1 VALUES (@@session.pseudo_thread_id); INSERT INTO t1 VALUES (@@session.foreign_key_checks); INSERT INTO t1 VALUES (@@session.sql_auto_is_null); INSERT INTO t1 VALUES (@@session.unique_checks); INSERT INTO t1 VALUES (@@session.auto_increment_increment); INSERT INTO t1 VALUES (@@session.auto_increment_offset); INSERT INTO t2 VALUES (@@session.character_set_client); INSERT INTO t2 VALUES (@@session.collation_connection); INSERT INTO t2 VALUES (@@session.collation_server); INSERT INTO t2 VALUES (@@session.time_zone); INSERT INTO t2 VALUES (@@session.lc_time_names); INSERT INTO t2 VALUES (@@session.collation_database); INSERT INTO t2 VALUES (@@session.timestamp); INSERT INTO t2 VALUES (@@session.last_insert_id); SET @my_var= 4711; INSERT INTO t1 VALUES (@my_var); SET insert_id=12; INSERT INTO t3 VALUES (NULL); ==== Clean up ==== DROP PROCEDURE proc; DROP FUNCTION func; DROP TRIGGER trig; DROP PROCEDURE proc1; DROP FUNCTION func2; DROP TRIGGER trig3; DROP PROCEDURE proc4; DROP FUNCTION func5; DROP PREPARE prep6; DROP TABLE t1, t2, t3, trigger_table, trigger_table2;