# Test of replication of stored procedures (WL#2146 for MySQL 5.0) # Modified by WL#2971. # Note that in the .opt files we still use the old variable name # log-bin-trust-routine-creators so that this test checks that it's # still accepted (this test also checks that the new name is # accepted). The old name could be removed in 5.1 or 6.0. source include/master-slave.inc; # we need a db != test, where we don't have automatic grants --disable_warnings drop database if exists mysqltest1; --enable_warnings create database mysqltest1; use mysqltest1; create table t1 (a varchar(100)); sync_slave_with_master; use mysqltest1; # ********************** PART 1 : STORED PROCEDURES *************** # Does the same proc as on master get inserted into mysql.proc ? # (same definer, same properties...) connection master; delimiter |; # Stored procedures don't have the limitations that functions have # regarding binlogging: it's ok to create a procedure as not # deterministic and updating data, while it's not ok to create such a # function. We test this. create procedure foo() begin declare b int; set b = 8; insert into t1 values (b); insert into t1 values (unix_timestamp()); end| delimiter ;| # we replace columns having times # (even with fixed timestamp displayed time may changed based on TZ) --replace_result localhost.localdomain localhost 127.0.0.1 localhost --replace_column 13 # 14 # select * from mysql.proc where name='foo' and db='mysqltest1'; sync_slave_with_master; # You will notice in the result that the definer does not match what # it is on master, it is a known bug on which Alik is working --replace_result localhost.localdomain localhost 127.0.0.1 localhost --replace_column 13 # 14 # select * from mysql.proc where name='foo' and db='mysqltest1'; connection master; # see if timestamp used in SP on slave is same as on master set timestamp=1000000000; call foo(); select * from t1; sync_slave_with_master; select * from t1; # Now a SP which is not updating tables connection master; delete from t1; create procedure foo2() select * from mysqltest1.t1; call foo2(); # check that this is allowed (it's not for functions): alter procedure foo2 contains sql; # SP with definer's right drop table t1; create table t1 (a int); create table t2 like t1; create procedure foo3() deterministic insert into t1 values (15); # let's create a non-privileged user 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; # ToDo: BUG#14931: There is a race between the last grant binlogging, and # the binlogging in the new connection made below, causing sporadic test # failures due to switched statement order in binlog. To fix this we do # SELECT 1 in the first connection before starting the second, ensuring # that binlogging is done in the expected order. # Please remove this SELECT 1 when BUG#14931 is fixed. SELECT 1; connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,); connection con1; # this routine will fail in the second INSERT because of privileges delimiter |; create procedure foo4() deterministic begin insert into t2 values(3); insert into t1 values (5); end| delimiter ;| # I add ,0 so that it does not print the error in the test output, # because this error is hostname-dependent --error 1142,0 call foo4(); # invoker has no INSERT grant on table t1 => failure connection master; call foo3(); # success (definer == root) show warnings; --error 1142,0 call foo4(); # definer's rights => failure # we test replication of ALTER PROCEDURE alter procedure foo4 sql security invoker; call foo4(); # invoker's rights => success show warnings; # Note that half-failed procedure calls are ok with binlogging; # if we compare t2 on master and slave we see they are identical: select * from t1; select * from t2; sync_slave_with_master; select * from t1; select * from t2; # Let's check another failing-in-the-middle procedure connection master; delete from t2; alter table t2 add unique (a); drop procedure foo4; delimiter |; create procedure foo4() deterministic begin insert into t2 values(20),(20); end| delimiter ;| --error 1062 call foo4(); show warnings; select * from t2; sync_slave_with_master; # check that this failed-in-the-middle replicated right: select * from t2; # Test of DROP PROCEDURE --replace_result localhost.localdomain localhost 127.0.0.1 localhost --replace_column 13 # 14 # select * from mysql.proc where name="foo4" and db='mysqltest1'; connection master; drop procedure foo4; select * from mysql.proc where name="foo4" and db='mysqltest1'; sync_slave_with_master; select * from mysql.proc where name="foo4" and db='mysqltest1'; # ********************** PART 2 : FUNCTIONS *************** connection master; drop procedure foo; drop procedure foo2; drop procedure foo3; delimiter |; # check that needs "deterministic" --error 1418 create function fn1(x int) returns int begin insert into t1 values (x); return x+2; end| create function fn1(x int) returns int deterministic begin insert into t1 values (x); return x+2; end| delimiter ;| delete t1,t2 from t1,t2; select fn1(20); insert into t2 values(fn1(21)); select * from t1; select * from t2; sync_slave_with_master; select * from t1; select * from t2; connection master; delimiter |; drop function fn1; create function fn1() returns int no sql begin return unix_timestamp(); end| delimiter ;| # check that needs "deterministic" --error 1418 alter function fn1 contains sql; delete from t1; set timestamp=1000000000; insert into t1 values(fn1()); connection con1; delimiter |; --error 1419 # only full-global-privs user can create a function create function fn2() returns int no sql begin return unix_timestamp(); end| delimiter ;| connection master; # test old variable name: set global log_bin_trust_routine_creators=1; # now use new name: set global log_bin_trust_function_creators=0; set global log_bin_trust_function_creators=1; # slave needs it too otherwise will not execute what master allowed: connection slave; set global log_bin_trust_function_creators=1; connection con1; delimiter |; create function fn2() returns int no sql begin return unix_timestamp(); end| delimiter ;| connection master; # Now a function which is supposed to not update tables # as it's "reads sql data", so should not give error even if # non-deterministic. delimiter |; create function fn3() returns int not deterministic reads sql data begin return 0; end| delimiter ;| select fn3(); --replace_result localhost.localdomain localhost 127.0.0.1 localhost --replace_column 13 # 14 # select * from mysql.proc where db='mysqltest1'; select * from t1; sync_slave_with_master; use mysqltest1; select * from t1; --replace_result localhost.localdomain localhost 127.0.0.1 localhost --replace_column 13 # 14 # select * from mysql.proc where db='mysqltest1'; # Let's check a failing-in-the-middle function connection master; delete from t2; alter table t2 add unique (a); drop function fn1; delimiter |; create function fn1(x int) returns int begin insert into t2 values(x),(x); return 10; end| delimiter ;| do fn1(100); --error 1062 select fn1(20); select * from t2; sync_slave_with_master; # check that this failed-in-the-middle replicated right: select * from t2; # ********************** PART 3 : TRIGGERS *************** connection con1; --error 1227 create trigger trg before insert on t1 for each row set new.a= 10; connection master; delete from t1; # TODO: when triggers can contain an update, test that this update # does not go into binlog. # I'm not setting user vars in the trigger, because replication of user vars # would take care of propagating the user var's value to slave, so even if # the trigger was not executed on slave it would not be discovered. create trigger trg before insert on t1 for each row set new.a= 10; insert into t1 values (1); select * from t1; sync_slave_with_master; select * from t1; connection master; delete from t1; drop trigger trg; insert into t1 values (1); select * from t1; sync_slave_with_master; select * from t1; # ********************** PART 4 : RELATED FIXED BUGS *************** # # Test for bug #13969 "Routines which are replicated from master can't be # executed on slave". # connection master; create procedure foo() not deterministic reads sql data select * from t1; sync_slave_with_master; # This should not fail call foo(); connection master; drop procedure foo; sync_slave_with_master; # Clean up connection master; drop function fn1; drop database mysqltest1; drop user "zedjzlcsjhd"@127.0.0.1; use test; sync_slave_with_master; use test; # # Bug#14077 "Failure to replicate a stored function with a cursor": # verify that stored routines with cursors work on slave. # connection master; --disable_warnings drop function if exists f1; --enable_warnings delimiter |; 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| delimiter ;| create view v1 as select 1 as a; create table t1 (a int); insert into t1 (a) values (f1()); select * from t1; drop view v1; drop function f1; sync_slave_with_master; connection slave; select * from t1; # # Bug#16621 "INSERTs in Stored Procedures causes data corruption in the Binary # Log for 5.0.18" # # Prepare environment. connection master; --disable_warnings DROP PROCEDURE IF EXISTS p1; DROP TABLE IF EXISTS t1; --enable_warnings # Test case. CREATE TABLE t1(col VARCHAR(10)); CREATE PROCEDURE p1(arg VARCHAR(10)) INSERT INTO t1 VALUES(arg); CALL p1('test'); SELECT * FROM t1; sync_slave_with_master; connection slave; SELECT * FROM t1; # Cleanup. connection master; DROP PROCEDURE p1; # # BUG#20438: CREATE statements for views, stored routines and triggers can be # not replicable. # --echo --echo ---> Test for BUG#20438 # Prepare environment. --echo --echo ---> Preparing environment... --echo ---> connection: master --connection master --disable_warnings DROP PROCEDURE IF EXISTS p1; DROP FUNCTION IF EXISTS f1; --enable_warnings --echo --echo ---> Synchronizing slave with master... --save_master_pos --connection slave --sync_with_master --echo --echo ---> connection: master --connection master # Test. --echo --echo ---> Creating procedure... /*!50003 CREATE PROCEDURE p1() SET @a = 1 */; /*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */; --echo --echo ---> Checking on master... SHOW CREATE PROCEDURE p1; SHOW CREATE FUNCTION f1; --echo --echo ---> Synchronizing slave with master... --save_master_pos --connection slave --sync_with_master --echo ---> connection: master --echo --echo ---> Checking on slave... SHOW CREATE PROCEDURE p1; SHOW CREATE FUNCTION f1; # Cleanup. --echo --echo ---> connection: master --connection master --echo --echo ---> Cleaning up... DROP PROCEDURE p1; DROP FUNCTION f1; --save_master_pos --connection slave --sync_with_master --connection master # cleanup connection master; drop table t1; sync_slave_with_master; # # Bug22043: MySQL don't add "USE <DATABASE>" before "DROP PROCEDURE IF EXISTS" # connection master; --disable_warnings drop database if exists mysqltest; drop database if exists mysqltest2; --enable_warnings create database mysqltest; create database mysqltest2; use mysqltest2; create table t ( t integer ); create procedure mysqltest.test() begin end; insert into t values ( 1 ); --error ER_WRONG_DB_NAME create procedure `\\`.test() begin end; # # BUG#19725: Calls to stored function in other database are not # replicated correctly in some cases # connection master; delimiter |; create function f1 () returns int begin insert into t values (1); return 0; end| delimiter ;| sync_slave_with_master; # Let us test if we don't forget to binlog the function's database connection master; use mysqltest; set @a:= mysqltest2.f1(); sync_slave_with_master; connection master; # Final inspection which verifies how all statements of this test file # were written to the binary log. --replace_column 2 # 5 # show binlog events in 'master-bin.000001' from 98; # Restore log_bin_trust_function_creators to its original value. # This is a cleanup for all parts above where we tested stored # functions and triggers. set global log_bin_trust_function_creators=0; connection master; set global log_bin_trust_function_creators=0; # Clean up drop database mysqltest; drop database mysqltest2; sync_slave_with_master; # # Bug#36570: Parse error of CREATE PROCEDURE stmt with comments on slave # connection master; use test; delimiter |; /*!50001 create procedure `mysqltestbug36570_p1`() */ begin select 1; end| use mysql| create procedure test.` mysqltestbug36570_p2`(/*!50001 a int*/)`label`: begin select a; end| /*!50001 create function test.mysqltestbug36570_f1() */ returns int /*!50001 deterministic */ begin return 3; end| use test| delimiter ;| sync_slave_with_master; connection slave; --replace_column 5 t 6 t show procedure status like '%mysqltestbug36570%'; show create procedure ` mysqltestbug36570_p2`; call ` mysqltestbug36570_p2`(42); --replace_column 5 t 6 t show function status like '%mysqltestbug36570%'; connection master; use test; drop procedure mysqltestbug36570_p1; drop procedure ` mysqltestbug36570_p2`; drop function mysqltestbug36570_f1;