1 files changed, 730 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/t/rpl_sp.test b/mysql-test/suite/rpl/t/rpl_sp.test
new file mode 100644
index 00000000..637dda47
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_sp.test
@@ -0,0 +1,730 @@
+# 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;
+
+set local sql_mode='';
+# 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));
+--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;
+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...
+--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
+
+--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
+--echo ---> Checking on slave...
+
+SHOW CREATE PROCEDURE p1;
+SHOW CREATE FUNCTION f1;
+
+# Cleanup.
+
+--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 #
+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;
+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;
+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;
+connection master;
+--echo # Reaping 'insert into t1 (a) values (f1())'...
+--reap
+connection master1;
+--echo # Reaping 'drop function f1'
+--reap
+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