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; drop database if exists mysqltest1; create database mysqltest1; use mysqltest1; create table t1 (a varchar(100)); use mysqltest1; create procedure foo() 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 NO 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 NO DEFINER begin declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); end root@localhost # # set timestamp=1000000000; call foo(); select * from t1; a 8 1000000000 select * from t1; a 8 1000000000 delete from t1; create procedure foo2() select * from mysqltest1.t1; call foo2(); a alter procedure foo2 contains sql; 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; SELECT 1; 1 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 call foo3(); show warnings; Level Code Message call foo4(); Got one of the listed errors alter procedure foo4 sql security invoker; call foo4(); show warnings; Level Code Message 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 delete from t2; alter table t2 add unique (a); drop procedure foo4; create procedure foo4() deterministic begin insert into t2 values(20),(20); end| call foo4(); ERROR 23000: Duplicate entry '20' for key 'a' show warnings; Level Code Message Error 1582 Duplicate entry '20' for key 'a' select * from t2; a 20 select * from t2; a 20 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 DEFINER begin insert into t2 values(20),(20); end root@localhost # # 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 begin insert into t1 values (x); return x+2; end| ERROR HY000: This function 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_function_creators variable) 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 no sql begin return unix_timestamp(); end| alter function fn1 contains sql; ERROR HY000: This function 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_function_creators variable) delete from t1; set timestamp=1000000000; insert into t1 values(fn1()); create function fn2() returns int no sql begin return unix_timestamp(); end| 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_function_creators variable) set global log_bin_trust_routine_creators=1; Warnings: Warning 1543 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead set global log_bin_trust_function_creators=0; set global log_bin_trust_function_creators=1; set global log_bin_trust_function_creators=1; create function fn2() returns int no sql begin return unix_timestamp(); end| create function fn3() returns int not deterministic reads sql data begin return 0; end| select fn3(); fn3() 0 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 NO_SQL NO DEFINER int(11) begin return unix_timestamp(); end root@localhost # # mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); end zedjzlcsjhd@localhost # # mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin return 0; 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 NO_SQL NO DEFINER int(11) begin return unix_timestamp(); end root@localhost # # mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin return unix_timestamp(); end zedjzlcsjhd@localhost # # mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin return 0; end root@localhost # # delete from t2; alter table t2 add unique (a); drop function fn1; create function fn1(x int) returns int begin insert into t2 values(x),(x); return 10; end| do fn1(100); Warnings: Error 1582 Duplicate entry '100' for key 'a' select fn1(20); ERROR 23000: Duplicate entry '20' for key 'a' select * from t2; a 20 100 select * from t2; a 20 100 create trigger trg before insert on t1 for each row set new.a= 10; ERROR 42000: TRIGGER command denied to user 'zedjzlcsjhd'@'localhost' for table 't1' 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 select * from t1; a 1 create procedure foo() not deterministic reads sql data select * from t1; call foo(); a 1 drop procedure foo; drop function fn1; drop database mysqltest1; drop user "zedjzlcsjhd"@127.0.0.1; use test; use test; drop function if exists f1; create function f1() returns int reads sql data begin declare var integer; declare c cursor for select a from v1; open c; fetch c into var; close c; return var; end| create view v1 as select 1 as a; create table t1 (a int); insert into t1 (a) values (f1()); select * from t1; a 1 drop view v1; drop function f1; select * from t1; a 1 DROP PROCEDURE IF EXISTS p1; DROP TABLE IF EXISTS t1; CREATE TABLE t1(col VARCHAR(10)); CREATE PROCEDURE p1(arg VARCHAR(10)) INSERT INTO t1 VALUES(arg); CALL p1('test'); SELECT * FROM t1; col test SELECT * FROM t1; col test DROP PROCEDURE p1; ---> Test for BUG#20438 ---> Preparing environment... ---> connection: master DROP PROCEDURE IF EXISTS p1; DROP FUNCTION IF EXISTS f1; ---> Synchronizing slave with master... ---> connection: master ---> Creating procedure... /*!50003 CREATE PROCEDURE p1() SET @a = 1 */; /*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */; ---> Checking on master... SHOW CREATE PROCEDURE p1; Procedure sql_mode Create Procedure p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() SET @a = 1 SHOW CREATE FUNCTION f1; Function sql_mode Create Function f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) RETURN 0 ---> Synchronizing slave with master... ---> connection: master ---> Checking on slave... SHOW CREATE PROCEDURE p1; Procedure sql_mode Create Procedure p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() SET @a = 1 SHOW CREATE FUNCTION f1; Function sql_mode Create Function f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) RETURN 0 ---> connection: master ---> Cleaning up... DROP PROCEDURE p1; DROP FUNCTION f1; drop table t1; drop database if exists mysqltest; drop database if exists mysqltest2; create database mysqltest; create database mysqltest2; use mysqltest2; create table t ( t integer ); create procedure mysqltest.test() begin end; insert into t values ( 1 ); create procedure `\\`.test() begin end; ERROR 42000: Unknown database '\\' create function f1 () returns int begin insert into t values (1); return 0; end| use mysqltest; set @a:= mysqltest2.f1(); show binlog events in 'master-bin.000001' from 105; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # drop database if exists mysqltest1 master-bin.000001 # Query 1 # create database mysqltest1 master-bin.000001 # Query 1 # use `mysqltest1`; create table t1 (a varchar(100)) master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo() 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()) master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo2() select * from mysqltest1.t1 master-bin.000001 # Query 1 # use `mysqltest1`; alter procedure foo2 contains sql 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 DEFINER=`root`@`localhost` 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 DEFINER=`zedjzlcsjhd`@`127.0.0.1` 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) master-bin.000001 # Query 1 # use `mysqltest1`; delete from t2 master-bin.000001 # Query 1 # use `mysqltest1`; alter table t2 add unique (a) master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo4 master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo4() deterministic begin insert into t2 values(20),(20); end master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(20),(20) master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo4 master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo2 master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo3 master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1(x int) returns int deterministic begin insert into t1 values (x); return x+2; end master-bin.000001 # Query 1 # use `mysqltest1`; delete t1,t2 from t1,t2 master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20) master-bin.000001 # Query 1 # use `mysqltest1`; insert into t2 values(fn1(21)) master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1 master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1() returns int no sql begin return unix_timestamp(); end master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values(fn1()) master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` function fn2() returns int no sql begin return unix_timestamp(); end master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn3() returns int not deterministic reads sql data begin return 0; end master-bin.000001 # Query 1 # use `mysqltest1`; delete from t2 master-bin.000001 # Query 1 # use `mysqltest1`; alter table t2 add unique (a) master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1 master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1(x int) returns int begin insert into t2 values(x),(x); return 10; end master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(100) master-bin.000001 # Query 1 # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20) master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10 master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (1) master-bin.000001 # Query 1 # use `mysqltest1`; delete from t1 master-bin.000001 # Query 1 # use `mysqltest1`; drop trigger trg master-bin.000001 # Query 1 # use `mysqltest1`; insert into t1 values (1) master-bin.000001 # Query 1 # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo() not deterministic reads sql data select * from t1 master-bin.000001 # Query 1 # use `mysqltest1`; drop procedure foo master-bin.000001 # Query 1 # use `mysqltest1`; drop function fn1 master-bin.000001 # Query 1 # drop database mysqltest1 master-bin.000001 # Query 1 # drop user "zedjzlcsjhd"@127.0.0.1 master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` function f1() returns int reads sql data begin declare var integer; declare c cursor for select a from v1; open c; fetch c into var; close c; return var; end master-bin.000001 # Query 1 # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 as a master-bin.000001 # Query 1 # use `test`; create table t1 (a int) master-bin.000001 # Query 1 # use `test`; insert into t1 (a) values (f1()) master-bin.000001 # Query 1 # use `test`; drop view v1 master-bin.000001 # Query 1 # use `test`; drop function f1 master-bin.000001 # Query 1 # use `test`; DROP TABLE IF EXISTS t1 master-bin.000001 # Query 1 # use `test`; CREATE TABLE t1(col VARCHAR(10)) master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE p1(arg VARCHAR(10)) INSERT INTO t1 VALUES(arg) master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test')) master-bin.000001 # Query 1 # use `test`; DROP PROCEDURE p1 master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE p1() SET @a = 1 master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION f1() RETURNS INT RETURN 0 master-bin.000001 # Query 1 # use `test`; DROP PROCEDURE p1 master-bin.000001 # Query 1 # use `test`; DROP FUNCTION f1 master-bin.000001 # Query 1 # use `test`; drop table t1 master-bin.000001 # Query 1 # drop database if exists mysqltest master-bin.000001 # Query 1 # drop database if exists mysqltest2 master-bin.000001 # Query 1 # create database mysqltest master-bin.000001 # Query 1 # create database mysqltest2 master-bin.000001 # Query 1 # use `mysqltest2`; create table t ( t integer ) master-bin.000001 # Query 1 # use `mysqltest2`; CREATE DEFINER=`root`@`localhost` procedure mysqltest.test() begin end master-bin.000001 # Query 1 # use `mysqltest2`; insert into t values ( 1 ) master-bin.000001 # Query 1 # use `mysqltest2`; CREATE DEFINER=`root`@`localhost` function f1 () returns int begin insert into t values (1); return 0; end master-bin.000001 # Query 1 # use `mysqltest`; SELECT `mysqltest2`.`f1`() set global log_bin_trust_function_creators=0; set global log_bin_trust_function_creators=0; drop database mysqltest; drop database mysqltest2; End of 5.0 tests End of 5.1 tests