==== 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;