# row-based and statement have expected binlog difference in result files # 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/have_binlog_format_mixed.inc; 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; # 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)); --sorted_result select * from t1; select * from t2; sync_slave_with_master; --sorted_result 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'; # ********************** PART 3 : TRIGGERS *************** connection con1; # now fails due to missing trigger grant (err 1142 i/o 1227) due to new # check in sql_trigger.cc (v1.44) by anozdrin on 2006/02/01 --azundris --error ER_TABLEACCESS_DENIED_ERROR 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; --replace_column 2 # 5 # --replace_regex /table_id: [0-9]+/table_id: #/ #show binlog events in 'master-bin.000001' from 106; sync_slave_with_master; select * from t1; # # 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; 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; # Restore log_bin_trust_function_creators to original value set global log_bin_trust_function_creators=0; connection master; set global log_bin_trust_function_creators=0; --echo End of 5.0 tests # # Bug22043: MySQL don't add "USE " before "DROP PROCEDURE IF EXISTS" # connection master; reset 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 ); #show binlog events in 'master-bin.000001' from 106; --error ER_BAD_DB_ERROR create procedure `\\`.test() begin end; # Clean up drop database mysqltest; drop database mysqltest2; --echo End of 5.1 tests