stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; create database if not exists mysqltest1; use mysqltest1; create table t1 (a varchar(100)); use mysqltest1; drop procedure if exists foo; drop procedure if exists foo2; drop procedure if exists foo3; drop procedure if exists foo4; drop procedure if exists bar; drop function if exists fn1; create procedure foo() begin declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); end| ERROR HY000: This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) show binlog events from 98| Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # create database if not exists mysqltest1 master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a varchar(100)) create procedure foo() deterministic begin declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); end| select * from mysql.proc where name='foo' and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL YES DEFINER begin declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); end root@localhost # # select * from mysql.proc where name='foo' and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL YES DEFINER begin declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); end @ # # set timestamp=1000000000; call foo(); show binlog events from 308; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo() deterministic begin declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); end master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8)) master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp()) select * from t1; a 8 1000000000 select * from t1; a 8 1000000000 delete from t1; create procedure foo2() not deterministic reads sql data select * from mysqltest1.t1; call foo2(); a show binlog events from 518; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8)) master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (unix_timestamp()) master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo2() not deterministic reads sql data select * from mysqltest1.t1 alter procedure foo2 contains sql; ERROR HY000: This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) drop table t1; create table t1 (a int); create table t2 like t1; create procedure foo3() deterministic insert into t1 values (15); grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1; create procedure foo4() deterministic insert into t1 values (10); ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) set global log_bin_trust_routine_creators=1; create procedure foo4() deterministic begin insert into t2 values(3); insert into t1 values (5); end| call foo4(); Got one of the listed errors show warnings; Level Code Message Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1' Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes call foo3(); show warnings; Level Code Message call foo4(); Got one of the listed errors show warnings; Level Code Message Error 1142 INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1' Warning 1417 A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes alter procedure foo4 sql security invoker; call foo4(); show warnings; Level Code Message show binlog events from 990; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # use `mysqltest1`; drop table t1 master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a int) master-bin.000001 # Query 1 # use `mysqltest1`; create table t2 like t1 master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo3() deterministic insert into t1 values (15) master-bin.000001 # Query 1 # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1 master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1 master-bin.000001 # Query 1 # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1 master-bin.000001 # Query 1 # use `mysqltest1`; create procedure foo4() deterministic begin insert into t2 values(3); insert into t1 values (5); end master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (15) master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo4 sql security invoker master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(3) master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (5) select * from t1; a 15 5 select * from t2; a 3 3 3 select * from t1; a 15 5 select * from t2; a 3 3 3 select * from mysql.proc where name="foo4" and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES INVOKER begin insert into t2 values(3); insert into t1 values (5); end @ # # drop procedure foo4; select * from mysql.proc where name="foo4" and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment select * from mysql.proc where name="foo4" and db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment drop procedure foo; drop procedure foo2; drop procedure foo3; create function fn1(x int) returns int deterministic begin insert into t1 values (x); return x+2; end| delete t1,t2 from t1,t2; select fn1(20); fn1(20) 22 insert into t2 values(fn1(21)); select * from t1; a 21 20 select * from t2; a 23 select * from t1; a 21 20 select * from t2; a 23 drop function fn1; create function fn1() returns int deterministic begin return unix_timestamp(); end| delete from t1; set timestamp=1000000000; insert into t1 values(fn1()); select * from mysql.proc where db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment mysqltest1 fn1 FUNCTION fn1 SQL CONTAINS_SQL YES DEFINER int(11) begin return unix_timestamp(); end root@localhost # # select * from t1; a 1000000000 use mysqltest1; select * from t1; a 1000000000 select * from mysql.proc where db='mysqltest1'; db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment mysqltest1 fn1 FUNCTION fn1 SQL CONTAINS_SQL YES DEFINER int(11) begin return unix_timestamp(); end @ # # create trigger trg before insert on t1 for each row set new.a= 10; ERROR 42000: Access denied; you need the SUPER privilege for this operation flush logs; delete from t1; create trigger trg before insert on t1 for each row set new.a= 10; insert into t1 values (1); select * from t1; a 10 select * from t1; a 10 delete from t1; drop trigger trg; insert into t1 values (1); select * from t1; a 1 show binlog events in 'master-bin.000002' from 98; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 master-bin.000002 # Query 1 # use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10 master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) master-bin.000002 # Query 1 # use `mysqltest1`; delete from t1 master-bin.000002 # Query 1 # use `mysqltest1`; drop trigger trg master-bin.000002 # Query 1 # use `mysqltest1`; insert into t1 values (1) select * from t1; a 1 drop function fn1; drop database mysqltest1; drop user "zedjzlcsjhd"@127.0.0.1;