diff options
Diffstat (limited to 'mysql-test/suite/rpl/t/rpl_sp.test')
| -rw-r--r-- | mysql-test/suite/rpl/t/rpl_sp.test | 741 |
1 files changed, 0 insertions, 741 deletions
diff --git a/mysql-test/suite/rpl/t/rpl_sp.test b/mysql-test/suite/rpl/t/rpl_sp.test deleted file mode 100644 index c978a145a92..00000000000 --- a/mysql-test/suite/rpl/t/rpl_sp.test +++ /dev/null @@ -1,741 +0,0 @@ -# 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. - -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; - -# 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 ER_DUP_ENTRY -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; -set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; -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 @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; -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 ER_DUP_ENTRY -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; -# 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; -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; -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... - ---sync_slave_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... - ---sync_slave_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; - ---sync_slave_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_BAD_DB_ERROR -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. ---source include/show_binlog_events.inc - - -# 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. -connection slave; -set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; -connection master; -set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; - -# 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 ;| - ---replace_column 5 t 6 t -show procedure status like '%mysqltestbug36570%'; -show create procedure ` mysqltestbug36570_p2`; - -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; -flush logs; -let $MYSQLD_DATADIR= `select @@datadir`; ---replace_regex /$MYSQL_TEST_DIR/MYSQL_TEST_DIR/ /TIMESTAMP=[0-9]*/TIMESTAMP=t/ ---exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000001 -use test; -drop procedure mysqltestbug36570_p1; -drop procedure ` mysqltestbug36570_p2`; -drop function mysqltestbug36570_f1; ---echo End of 5.0 tests ---echo # End of 5.1 tests ---echo # ---echo # Test Bug#30977 Concurrent statement using stored ---echo # function and DROP FUNCTION breaks SBR. ---echo # ---echo # Demonstrate that stored function DDL can not go through, ---echo # or, worse yet, make its way into the binary log, while ---echo # the stored function is in use. ---echo # For that, try to insert a result of a stored function ---echo # into a table. Block the insert in the beginning, waiting ---echo # on a table lock. While insert is blocked, attempt to ---echo # drop the routine. Verify that this attempt ---echo # blocks and waits for INSERT to complete. Commit and ---echo # reap the chain of events. Master and slave must contain ---echo # identical data. Statements in the binrary log must be ---echo # consistent with data in the table. ---echo # ---echo # --> connection default -connection default; ---disable_warnings -drop table if exists t1, t2; -drop function if exists t1; ---enable_warnings -create table t1 (a int); -create table t2 (a int) as select 1 as a; -create function f1() returns int deterministic return (select max(a) from t2); -lock table t2 write; ---echo # --> connection master -connection master; ---echo # Sending 'insert into t1 (a) values (f1())'... ---send insert into t1 (a) values (f1()) -connection master1; ---echo # Waitng for 'insert into t1 ...' to get blocked on table lock... -let $wait_condition=select count(*)=1 from information_schema.processlist -where state='Waiting for table metadata lock' and - info='insert into t1 (a) values (f1())'; ---source include/wait_condition.inc ---echo # Sending 'drop function f1'. It will wait till insert finishes. ---send drop function f1; ---echo # --> connection default -connection default; ---echo # Check that 'drop function f1' gets blocked. -let $wait_condition=select count(*)=1 from information_schema.processlist -where state='Waiting for stored function metadata lock' and info='drop function f1'; ---source include/wait_condition.inc ---echo # Now let's let 'insert' go through... -unlock tables; ---echo # --> connection master -connection master; ---echo # Reaping 'insert into t1 (a) values (f1())'... ---reap ---echo # --> connection master1 -connection master1; ---echo # Reaping 'drop function f1' ---reap ---echo # --> connection master -connection master; -select * from t1; -sync_slave_with_master; -connection slave; -select * from t1; -# Cleanup -connection master; -drop table t1, t2; ---error ER_SP_DOES_NOT_EXIST -drop function f1; - - ---echo # ---echo # Bug #11918 Can't use a declared variable in LIMIT clause ---echo # ---source include/rpl_reset.inc - -create table t1 (c1 int); -insert into t1 (c1) values -(1), (2), (3), (4), (5), (6), (7), (8), (9), (10); - -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); - -create procedure p1(p1 integer) - delete from t1 limit p1; - -set @save_binlog_format=@@session.binlog_format; -set @@session.binlog_format=STATEMENT; - ---disable_warnings -call p1(NULL); -call p1(0); -call p1(1); -call p1(2); -call p1(3); ---enable_warnings - -select * from t1; -sync_slave_with_master; -connection slave; -select * from t1; -connection master; ---disable_warnings -call p1(-1); ---enable_warnings -select * from t1; -sync_slave_with_master; -connection slave; -select * from t1; -connection master; - ---echo # Cleanup -set @@session.binlog_format=@save_binlog_format; -drop table t1; -drop procedure p1; - ---echo # End of 5.5 tests. - - -# Cleanup -sync_slave_with_master; ---source include/rpl_end.inc |