1 files changed, 323 insertions, 0 deletions
diff --git a/mysql-test/t/rpl_row_sp000.test b/mysql-test/t/rpl_row_sp000.test
new file mode 100644
index 00000000000..e591cb054fc
--- /dev/null
+++ b/mysql-test/t/rpl_row_sp000.test
@@ -0,0 +1,323 @@
+# row-based and statement have expected binlog difference in result files
+-- source include/have_binlog_format_row.inc
+
+# Test of replication of stored procedures in row-based replication.
+# Initially copied from the statement-based version rpl_stm_sp.test.
+
+# 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 ?
+# (all same properties)
+
+connection master;
+
+delimiter |;
+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;
+--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()
+  not deterministic
+  select * from mysqltest1.t1;
+call foo2();
+
+alter procedure foo2 contains sql;
+
+# SP with definer's right
+
+drop table t1;
+create table t1 (a int);
+create table t2 (a int);
+
+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;
+
+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';
+
+# Test of a procedure and function containing UUID() is done in
+# rpl_row_UUID.
+
+
+# ********************** PART 2 : FUNCTIONS ***************
+
+connection master;
+drop procedure foo;
+drop procedure foo2;
+drop procedure foo3;
+
+delimiter |;
+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
+begin
+       return unix_timestamp();
+end|
+
+delimiter ;|
+# Just to test ALTER FUNCTION
+alter function fn1 no sql;
+delete from t1;
+set timestamp=1000000000;
+insert into t1 values(fn1()); 
+
+connection con1;
+
+delimiter |;
+create function fn2()
+       returns int
+       no sql
+begin
+       return unix_timestamp();
+end|
+delimiter ;|
+
+connection master;
+
+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()
+       returns int
+begin
+  insert into t2 values(20),(20);
+  return 10;
+end|
+
+delimiter ;|
+
+--error 1062
+select fn1();
+
+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;
+--replace_column 2 # 5 #
+show binlog events in 'master-bin.000001' from 102;
+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;
+sync_slave_with_master;