Merge gbichot@bk-internal.mysql.com:/home/bk/mysql-5.0

into  mysql.com:/home/mysql_src/mysql-5.0

8 files changed, 358 insertions, 168 deletions

diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index ea4fa1082db..341d9c38f20 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -2008,7 +2008,7 @@ sub mysqld_arguments ($$$$$) {
   mtr_add_arg($args, "%s--basedir=%s", $prefix, $path_my_basedir);
   mtr_add_arg($args, "%s--character-sets-dir=%s", $prefix, $path_charsetsdir);
   mtr_add_arg($args, "%s--core", $prefix);
-  mtr_add_arg($args, "%s--log-bin-trust-routine-creators", $prefix);
+  mtr_add_arg($args, "%s--log-bin-trust-function-creators", $prefix);
   mtr_add_arg($args, "%s--default-character-set=latin1", $prefix);
   mtr_add_arg($args, "%s--language=%s", $prefix, $path_language);
   mtr_add_arg($args, "%s--tmpdir=$opt_tmpdir", $prefix);
@@ -2131,7 +2131,7 @@ sub mysqld_arguments ($$$$$) {
   mtr_add_arg($args, "%s--key_buffer_size=1M", $prefix);
   mtr_add_arg($args, "%s--sort_buffer=256K", $prefix);
   mtr_add_arg($args, "%s--max_heap_table_size=1M", $prefix);
-  mtr_add_arg($args, "%s--log-bin-trust-routine-creators", $prefix);
+  mtr_add_arg($args, "%s--log-bin-trust-function-creators", $prefix);
 
   if ( $opt_ssl_supported )
   {
diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh
index 62c2b9014c3..c84763713e1 100644
--- a/mysql-test/mysql-test-run.sh
+++ b/mysql-test/mysql-test-run.sh
@@ -1275,7 +1275,7 @@ start_master()
           --language=$LANGUAGE \
           --innodb_data_file_path=ibdata1:128M:autoextend \
 	  --open-files-limit=1024 \
-          --log-bin-trust-routine-creators \
+          --log-bin-trust-function-creators \
 	   $MASTER_40_ARGS \
            $SMALL_SERVER \
            $EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT \
@@ -1296,7 +1296,7 @@ start_master()
           --tmpdir=$MYSQL_TMP_DIR \
           --language=$LANGUAGE \
           --innodb_data_file_path=ibdata1:128M:autoextend \
-          --log-bin-trust-routine-creators \
+          --log-bin-trust-function-creators \
 	   $MASTER_40_ARGS \
            $SMALL_SERVER \
            $EXTRA_MASTER_OPT $EXTRA_MASTER_MYSQLD_OPT \
@@ -1429,7 +1429,7 @@ start_slave()
           --report-port=$slave_port \
           --master-retry-count=10 \
           -O slave_net_timeout=10 \
-          --log-bin-trust-routine-creators \
+          --log-bin-trust-function-creators \
            $SMALL_SERVER \
            $EXTRA_SLAVE_OPT $EXTRA_SLAVE_MYSQLD_OPT"
   CUR_MYERR=$slave_err
diff --git a/mysql-test/mysql_test_run_new.c b/mysql-test/mysql_test_run_new.c
index 33a69eba872..79db71fa274 100644
--- a/mysql-test/mysql_test_run_new.c
+++ b/mysql-test/mysql_test_run_new.c
@@ -486,7 +486,7 @@ void start_master()
 #endif
   add_arg(&al, "--local-infile");
   add_arg(&al, "--core");
-  add_arg(&al, "--log-bin-trust-routine-creators");
+  add_arg(&al, "--log-bin-trust-function-creators");
   add_arg(&al, "--datadir=%s", master_dir);
 #ifndef __WIN__
   add_arg(&al, "--pid-file=%s", master_pid);
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 82a761252b5..2e25019ad6f 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -1614,6 +1614,16 @@ mysqldump: Couldn't find table: "t\1"
 
 mysqldump: Couldn't find table: "t/1"
 
+mysqldump: Couldn't find table: "T_1"
+
+mysqldump: Couldn't find table: "T%1"
+
+mysqldump: Couldn't find table: "T'1"
+
+mysqldump: Couldn't find table: "T_1"
+
+mysqldump: Couldn't find table: "T_"
+
 test_sequence
 ------ Testing with illegal database names ------
 mysqldump: Got error: 1049: Unknown database 'mysqldump_test_d' when selecting the database
diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result
index 5f1c3afd14d..58692ec5b82 100644
--- a/mysql-test/r/rpl_sp.result
+++ b/mysql-test/r/rpl_sp.result
@@ -4,16 +4,11 @@ reset master;
 reset slave;
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 start slave;
-create database if not exists mysqltest1;
+drop database if exists mysqltest1;
+create database mysqltest1;
 use mysqltest1;
 create table t1 (a varchar(100));
 use mysqltest1;
-drop procedure if exists foo;
-drop procedure if exists foo2;
-drop procedure if exists foo3;
-drop procedure if exists foo4;
-drop procedure if exists bar;
-drop function if exists fn1;
 create procedure foo()
 begin
 declare b int;
@@ -21,21 +16,9 @@ set b = 8;
 insert into t1 values (b);
 insert into t1 values (unix_timestamp());
 end|
-ERROR HY000: This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)
-show binlog events from 98|
-Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	#	Query	1	#	create database if not exists mysqltest1
-master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t1 (a varchar(100))
-create procedure foo() deterministic
-begin
-declare b int;
-set b = 8;
-insert into t1 values (b);
-insert into t1 values (unix_timestamp());
-end|
 select * from mysql.proc where name='foo' and db='mysqltest1';
 db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
-mysqltest1	foo	PROCEDURE	foo	SQL	CONTAINS_SQL	YES	DEFINER			begin
+mysqltest1	foo	PROCEDURE	foo	SQL	CONTAINS_SQL	NO	DEFINER			begin
 declare b int;
 set b = 8;
 insert into t1 values (b);
@@ -43,7 +26,7 @@ insert into t1 values (unix_timestamp());
 end	root@localhost	#	#		
 select * from mysql.proc where name='foo' and db='mysqltest1';
 db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
-mysqltest1	foo	PROCEDURE	foo	SQL	CONTAINS_SQL	YES	DEFINER			begin
+mysqltest1	foo	PROCEDURE	foo	SQL	CONTAINS_SQL	NO	DEFINER			begin
 declare b int;
 set b = 8;
 insert into t1 values (b);
@@ -51,17 +34,6 @@ insert into t1 values (unix_timestamp());
 end	@	#	#		
 set timestamp=1000000000;
 call foo();
-show binlog events from 308;
-Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo() deterministic
-begin
-declare b int;
-set b = 8;
-insert into t1 values (b);
-insert into t1 values (unix_timestamp());
-end
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (unix_timestamp())
 select * from t1;
 a
 8
@@ -72,22 +44,10 @@ a
 1000000000
 delete from t1;
 create procedure foo2()
-not deterministic
-reads sql data
 select * from mysqltest1.t1;
 call foo2();
 a
-show binlog events from 518;
-Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (unix_timestamp())
-master-bin.000001	#	Query	1	#	use `mysqltest1`; delete from t1
-master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo2()
-not deterministic
-reads sql data
-select * from mysqltest1.t1
 alter procedure foo2 contains sql;
-ERROR HY000: This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)
 drop table t1;
 create table t1 (a int);
 create table t2 like t1;
@@ -99,57 +59,21 @@ grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
 grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
 create procedure foo4()
 deterministic
-insert into t1 values (10);
-ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)
-set global log_bin_trust_routine_creators=1;
-create procedure foo4()
-deterministic
 begin
 insert into t2 values(3);
 insert into t1 values (5);
 end|
 call foo4();
 Got one of the listed errors
-show warnings;
-Level	Code	Message
-Error	1142	INSERT command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
-Warning	1417	A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes
 call foo3();
 show warnings;
 Level	Code	Message
 call foo4();
 Got one of the listed errors
-show warnings;
-Level	Code	Message
-Error	1142	INSERT command denied to user 'zedjzlcsjhd'@'127.0.0.1' for table 't1'
-Warning	1417	A routine failed and has neither NO SQL nor READS SQL DATA in its declaration and binary logging is enabled; if non-transactional tables were updated, the binary log will miss their changes
 alter procedure foo4 sql security invoker;
 call foo4();
 show warnings;
 Level	Code	Message
-show binlog events from 990;
-Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	#	Query	1	#	use `mysqltest1`; drop table t1
-master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t1 (a int)
-master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t2 like t1
-master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo3()
-deterministic
-insert into t1 values (15)
-master-bin.000001	#	Query	1	#	use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
-master-bin.000001	#	Query	1	#	use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
-master-bin.000001	#	Query	1	#	use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
-master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo4()
-deterministic
-begin
-insert into t2 values(3);
-insert into t1 values (5);
-end
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(3)
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (15)
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(3)
-master-bin.000001	#	Query	1	#	use `mysqltest1`; alter procedure foo4 sql security invoker
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(3)
-master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (5)
 select * from t1;
 a
 15
@@ -168,11 +92,29 @@ a
 3
 3
 3
+delete from t2;
+alter table t2 add unique (a);
+drop procedure foo4;
+create procedure foo4()
+deterministic
+begin
+insert into t2 values(20),(20);
+end|
+call foo4();
+ERROR 23000: Duplicate entry '20' for key 1
+show warnings;
+Level	Code	Message
+Error	1062	Duplicate entry '20' for key 1
+select * from t2;
+a
+20
+select * from t2;
+a
+20
 select * from mysql.proc where name="foo4" and db='mysqltest1';
 db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
-mysqltest1	foo4	PROCEDURE	foo4	SQL	CONTAINS_SQL	YES	INVOKER			begin
-insert into t2 values(3);
-insert into t1 values (5);
+mysqltest1	foo4	PROCEDURE	foo4	SQL	CONTAINS_SQL	YES	DEFINER			begin
+insert into t2 values(20),(20);
 end	@	#	#		
 drop procedure foo4;
 select * from mysql.proc where name="foo4" and db='mysqltest1';
@@ -184,6 +126,13 @@ drop procedure foo2;
 drop procedure foo3;
 create function fn1(x int)
 returns int
+begin
+insert into t1 values (x);
+return x+2;
+end|
+ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
+create function fn1(x int)
+returns int
 deterministic
 begin
 insert into t1 values (x);
@@ -211,17 +160,54 @@ a
 drop function fn1;
 create function fn1()
 returns int
-deterministic
+no sql
 begin
 return unix_timestamp();
 end|
+alter function fn1 contains sql;
+ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
 delete from t1;
 set timestamp=1000000000;
 insert into t1 values(fn1());
+create function fn2()
+returns int
+no sql
+begin
+return unix_timestamp();
+end|
+ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
+set global log_bin_trust_routine_creators=1;
+Warnings:
+Warning	1287	'log_bin_trust_routine_creators' is deprecated; use 'log_bin_trust_function_creators' instead
+set global log_bin_trust_function_creators=0;
+set global log_bin_trust_function_creators=1;
+set global log_bin_trust_function_creators=1;
+create function fn2()
+returns int
+no sql
+begin
+return unix_timestamp();
+end|
+create function fn3()
+returns int
+not deterministic
+reads sql data
+begin
+return 0;
+end|
+select fn3();
+fn3()
+0
 select * from mysql.proc where db='mysqltest1';
 db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
-mysqltest1	fn1	FUNCTION	fn1	SQL	CONTAINS_SQL	YES	DEFINER		int(11)	begin
+mysqltest1	fn1	FUNCTION	fn1	SQL	NO_SQL	NO	DEFINER		int(11)	begin
+return unix_timestamp();
+end	root@localhost	#	#		
+mysqltest1	fn2	FUNCTION	fn2	SQL	NO_SQL	NO	DEFINER		int(11)	begin
 return unix_timestamp();
+end	zedjzlcsjhd@localhost	#	#		
+mysqltest1	fn3	FUNCTION	fn3	SQL	READS_SQL_DATA	NO	DEFINER		int(11)	begin
+return 0;
 end	root@localhost	#	#		
 select * from t1;
 a
@@ -232,12 +218,34 @@ a
 1000000000
 select * from mysql.proc where db='mysqltest1';
 db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
-mysqltest1	fn1	FUNCTION	fn1	SQL	CONTAINS_SQL	YES	DEFINER		int(11)	begin
+mysqltest1	fn1	FUNCTION	fn1	SQL	NO_SQL	NO	DEFINER		int(11)	begin
+return unix_timestamp();
+end	@	#	#		
+mysqltest1	fn2	FUNCTION	fn2	SQL	NO_SQL	NO	DEFINER		int(11)	begin
 return unix_timestamp();
 end	@	#	#		
+mysqltest1	fn3	FUNCTION	fn3	SQL	READS_SQL_DATA	NO	DEFINER		int(11)	begin
+return 0;
+end	@	#	#		
+delete from t2;
+alter table t2 add unique (a);
+drop function fn1;
+create function fn1()
+returns int
+begin
+insert into t2 values(20),(20);
+return 10;
+end|
+select fn1();
+ERROR 23000: Duplicate entry '20' for key 1
+select * from t2;
+a
+20
+select * from t2;
+a
+20
 create trigger trg before insert on t1 for each row set new.a= 10;
 ERROR 42000: Access denied; you need the SUPER privilege for this operation
-flush logs;
 delete from t1;
 create trigger trg before insert on t1 for each row set new.a= 10;
 insert into t1 values (1);
@@ -253,14 +261,106 @@ insert into t1 values (1);
 select * from t1;
 a
 1
-show binlog events in 'master-bin.000002' from 98;
+show binlog events in 'master-bin.000001' from 98;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000002	#	Query	1	#	use `mysqltest1`; delete from t1
-master-bin.000002	#	Query	1	#	use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10
-master-bin.000002	#	Query	1	#	use `mysqltest1`; insert into t1 values (1)
-master-bin.000002	#	Query	1	#	use `mysqltest1`; delete from t1
-master-bin.000002	#	Query	1	#	use `mysqltest1`; drop trigger trg
-master-bin.000002	#	Query	1	#	use `mysqltest1`; insert into t1 values (1)
+master-bin.000001	#	Query	1	#	drop database if exists mysqltest1
+master-bin.000001	#	Query	1	#	create database mysqltest1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t1 (a varchar(100))
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo()
+begin
+declare b int;
+set b = 8;
+insert into t1 values (b);
+insert into t1 values (unix_timestamp());
+end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (unix_timestamp())
+master-bin.000001	#	Query	1	#	use `mysqltest1`; delete from t1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo2()
+select * from mysqltest1.t1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; alter procedure foo2 contains sql
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop table t1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t1 (a int)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create table t2 like t1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo3()
+deterministic
+insert into t1 values (15)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo4()
+deterministic
+begin
+insert into t2 values(3);
+insert into t1 values (5);
+end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(3)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (15)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(3)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; alter procedure foo4 sql security invoker
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(3)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (5)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; delete from t2
+master-bin.000001	#	Query	1	#	use `mysqltest1`; alter table t2 add unique (a)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop procedure foo4
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create procedure foo4()
+deterministic
+begin
+insert into t2 values(20),(20);
+end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(20),(20)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop procedure foo4
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop procedure foo
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop procedure foo2
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop procedure foo3
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create function fn1(x int)
+returns int
+deterministic
+begin
+insert into t1 values (x);
+return x+2;
+end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; delete t1,t2 from t1,t2
+master-bin.000001	#	Query	1	#	use `mysqltest1`; DO `fn1`(20)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t2 values(fn1(21))
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop function fn1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create function fn1()
+returns int
+no sql
+begin
+return unix_timestamp();
+end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; delete from t1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values(fn1())
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create function fn2()
+returns int
+no sql
+begin
+return unix_timestamp();
+end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create function fn3()
+returns int
+not deterministic
+reads sql data
+begin
+return 0;
+end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; delete from t2
+master-bin.000001	#	Query	1	#	use `mysqltest1`; alter table t2 add unique (a)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop function fn1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create function fn1()
+returns int
+begin
+insert into t2 values(20),(20);
+return 10;
+end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; DO `fn1`()
+master-bin.000001	#	Query	1	#	use `mysqltest1`; delete from t1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; create trigger trg before insert on t1 for each row set new.a= 10
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (1)
+master-bin.000001	#	Query	1	#	use `mysqltest1`; delete from t1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop trigger trg
+master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (1)
 select * from t1;
 a
 1
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 91a87e2c774..0dceba08031 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -658,19 +658,19 @@ select '------ Testing with illegal table names ------' as test_sequence ;
 --exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db  "t/1" 2>&1
 
 --error 6
---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T_1"
+--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T_1" 2>&1
 
 --error 6
---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T%1"
+--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T%1" 2>&1
 
 --error 6
---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T'1"
+--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T'1" 2>&1
 
 --error 6
---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T_1"
+--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T_1" 2>&1
 
 --error 6
---exec $MYSQL_DUMP --compact --skip-comments "mysqldump_test_db" "T_"
+--exec $MYSQL_DUMP --compact --skip-comments mysqldump_test_db "T_" 2>&1
 
 --disable_query_log
 select '------ Testing with illegal database names ------' as test_sequence ;
diff --git a/mysql-test/t/rpl_sp-slave.opt b/mysql-test/t/rpl_sp-slave.opt
index 709a224fd92..611ee1f33be 100644
--- a/mysql-test/t/rpl_sp-slave.opt
+++ b/mysql-test/t/rpl_sp-slave.opt
@@ -1 +1 @@
---log_bin_trust_routine_creators=0
+--log_bin_trust_routine_creators=0 --slave-skip-errors=1062
diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test
index e62a6c73c0a..e7a3afca9cb 100644
--- a/mysql-test/t/rpl_sp.test
+++ b/mysql-test/t/rpl_sp.test
@@ -1,10 +1,18 @@
 # 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;
 
-# First let's test replication of current_user() (that's a related thing)
 # we need a db != test, where we don't have automatic grants
-create database if not exists mysqltest1;
+--disable_warnings
+drop database if exists mysqltest1;
+--enable_warnings
+create database mysqltest1;
 use mysqltest1;
 create table t1 (a varchar(100));
 sync_slave_with_master;
@@ -16,30 +24,15 @@ use mysqltest1;
 # (same definer, same properties...)
 
 connection master;
-# cleanup
---disable_warnings
-drop procedure if exists foo;
-drop procedure if exists foo2;
-drop procedure if exists foo3;
-drop procedure if exists foo4;
-drop procedure if exists bar;
-drop function if exists fn1;
---enable_warnings
 
 delimiter |;
---error 1418 # not deterministic
-create procedure foo()
-begin
-  declare b int;
-  set b = 8;
-  insert into t1 values (b);
-  insert into t1 values (unix_timestamp());
-end|
 
---replace_column 2 # 5 #
-show binlog events from 98| # check that not there
+# 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() deterministic
+create procedure foo()
 begin
   declare b int;
   set b = 8;
@@ -54,38 +47,29 @@ delimiter ;|
 --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';
 
-# Now when we call it, does the CALL() get into binlog,
-# or the substatements?
 connection master;
 # see if timestamp used in SP on slave is same as on master
 set timestamp=1000000000;
 call foo();
---replace_column 2 # 5 #
-show binlog events from 308;
 select * from t1;
 sync_slave_with_master;
 select * from t1;
 
-# Now a SP which is supposed to not update tables (CALL should not be
-# binlogged) as it's "read sql data", so should not give error even if
-# non-deterministic.
+# Now a SP which is not updating tables
 
 connection master;
 delete from t1;
 create procedure foo2()
-  not deterministic
-  reads sql data
   select * from mysqltest1.t1;
 call foo2();
-# verify CALL is not in binlog
---replace_column 2 # 5 #
-show binlog events from 518;
 
---error 1418
+# check that this is allowed (it's not for functions):
 alter procedure foo2 contains sql;
 
 # SP with definer's right
@@ -106,15 +90,7 @@ grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
 connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,);
 connection con1;
 
---error 1419 # only full-global-privs user can create a routine
-create procedure foo4()
-  deterministic
-  insert into t1 values (10);
-
-connection master;
-set global log_bin_trust_routine_creators=1;
-connection con1;
-
+# this routine will fail in the second INSERT because of privileges
 delimiter |;
 create procedure foo4()
   deterministic
@@ -128,29 +104,22 @@ 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 => failure
-show warnings;
+call foo4(); # invoker has no INSERT grant on table t1 => failure
 
 connection master;
 call foo3(); # success (definer == root)
 show warnings;
 
---replace_result localhost.localdomain localhost 127.0.0.1 localhost
 --error 1142,0
 call foo4(); # definer's rights => failure
-show warnings;
 
 # we test replication of ALTER PROCEDURE
 alter procedure foo4 sql security invoker;
 call foo4(); # invoker's rights => success
 show warnings;
 
-# Check that only successful CALLs are in binlog
---replace_column 2 # 5 #
-show binlog events from 990;
-
-# Note that half-failed CALLs are not in binlog, which is a known
-# bug. If we compare t2 on master and slave we see they differ:
+# 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;
@@ -158,6 +127,30 @@ 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
@@ -177,6 +170,14 @@ 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
@@ -202,15 +203,69 @@ drop function fn1;
 
 create function fn1()
        returns int
-       deterministic
+       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';
@@ -223,18 +278,43 @@ select * from t1;
 --replace_column 13 # 14 #
 select * from mysql.proc where db='mysqltest1';
 
-# And now triggers
+# 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 ;|
+
+# Because of BUG#14769 the following statement requires that we start
+# slave with --slave-skip-errors=1062. When that bug is fixed, that
+# option can be removed.
+
+--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;
-# fn1() above uses timestamps, so in !ps-protocol, the timezone will be
-# binlogged, but in --ps-protocol it will not be (BUG#9359) so
-# the binlog offsets get shifted which spoils SHOW BINLOG EVENTS.
-# To be immune, we take a new binlog.
-flush logs;
 delete from t1;
 # TODO: when triggers can contain an update, test that this update
 # does not go into binlog.
@@ -253,7 +333,7 @@ drop trigger trg;
 insert into t1 values (1);
 select * from t1;
 --replace_column 2 # 5 #
-show binlog events in 'master-bin.000002' from 98;
+show binlog events in 'master-bin.000001' from 98;
 sync_slave_with_master;
 select * from t1;