~drizzle-trunk/drizzle/development : contents of mysql-test/suite/rpl/r/rpl

~drizzle-trunk/drizzle/development : (revision 33)

stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
drop database if exists mysqltest1;
create database mysqltest1;
use mysqltest1;
create table t1 (a varchar(100));
use mysqltest1;
create procedure foo()
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	character_set_client	collation_connection	db_collation	body_utf8
mysqltest1	foo	PROCEDURE	foo	SQL	CONTAINS_SQL	NO	DEFINER			begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end	root@localhost	#	#			latin1	latin1_swedish_ci	latin1_swedish_ci	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	character_set_client	collation_connection	db_collation	body_utf8
mysqltest1	foo	PROCEDURE	foo	SQL	CONTAINS_SQL	NO	DEFINER			begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end	root@localhost	#	#			latin1	latin1_swedish_ci	latin1_swedish_ci	begin
declare b int;
set b = 8;
insert into t1 values (b);
insert into t1 values (unix_timestamp());
end
set timestamp=1000000000;
call foo();
select * from t1;
a
8
1000000000
select * from t1;
a
8
1000000000
delete from t1;
create procedure foo2()
select * from mysqltest1.t1;
call foo2();
a
alter procedure foo2 contains sql;
drop table t1;
create table t1 (a int);
create table t2 like t1;
create procedure foo3()
deterministic
insert into t1 values (15);
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;
SELECT 1;
1
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
call foo3();
show warnings;
Level	Code	Message
call foo4();
Got one of the listed errors
alter procedure foo4 sql security invoker;
call foo4();
show warnings;
Level	Code	Message
select * from t1;
a
15
5
select * from t2;
a
3
3
3
select * from t1;
a
15
5
select * from t2;
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 'a'
show warnings;
Level	Code	Message
Error	1062	Duplicate entry '20' for key 'a'
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	character_set_client	collation_connection	db_collation	body_utf8
mysqltest1	foo4	PROCEDURE	foo4	SQL	CONTAINS_SQL	YES	DEFINER			begin
insert into t2 values(20),(20);
end	root@localhost	#	#			latin1	latin1_swedish_ci	latin1_swedish_ci	begin
insert into t2 values(20),(20);
end
drop procedure foo4;
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	character_set_client	collation_connection	db_collation	body_utf8
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	character_set_client	collation_connection	db_collation	body_utf8
drop procedure foo;
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);
return x+2;
end|
delete t1,t2 from t1,t2;
select fn1(20);
fn1(20)
22
insert into t2 values(fn1(21));
select * from t1;
a
21
20
select * from t2;
a
23
select * from t1;
a
21
20
select * from t2;
a
23
drop function fn1;
create function fn1()
returns int
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_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	character_set_client	collation_connection	db_collation	body_utf8
mysqltest1	fn1	FUNCTION	fn1	SQL	NO_SQL	NO	DEFINER		int(11)	begin
return unix_timestamp();
end	root@localhost	#	#			latin1	latin1_swedish_ci	latin1_swedish_ci	begin
return unix_timestamp();
end
mysqltest1	fn2	FUNCTION	fn2	SQL	NO_SQL	NO	DEFINER		int(11)	begin
return unix_timestamp();
end	zedjzlcsjhd@localhost	#	#			latin1	latin1_swedish_ci	latin1_swedish_ci	begin
return unix_timestamp();
end
mysqltest1	fn3	FUNCTION	fn3	SQL	READS_SQL_DATA	NO	DEFINER		int(11)	begin
return 0;
end	root@localhost	#	#			latin1	latin1_swedish_ci	latin1_swedish_ci	begin
return 0;
end
select * from t1;
a
1000000000
use mysqltest1;
select * from t1;
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	character_set_client	collation_connection	db_collation	body_utf8
mysqltest1	fn1	FUNCTION	fn1	SQL	NO_SQL	NO	DEFINER		int(11)	begin
return unix_timestamp();
end	root@localhost	#	#			latin1	latin1_swedish_ci	latin1_swedish_ci	begin
return unix_timestamp();
end
mysqltest1	fn2	FUNCTION	fn2	SQL	NO_SQL	NO	DEFINER		int(11)	begin
return unix_timestamp();
end	zedjzlcsjhd@localhost	#	#			latin1	latin1_swedish_ci	latin1_swedish_ci	begin
return unix_timestamp();
end
mysqltest1	fn3	FUNCTION	fn3	SQL	READS_SQL_DATA	NO	DEFINER		int(11)	begin
return 0;
end	root@localhost	#	#			latin1	latin1_swedish_ci	latin1_swedish_ci	begin
return 0;
end
delete from t2;
alter table t2 add unique (a);
drop function fn1;
create function fn1(x int)
returns int
begin
insert into t2 values(x),(x);
return 10;
end|
do fn1(100);
Warnings:
Error	1062	Duplicate entry '100' for key 'a'
select fn1(20);
ERROR 23000: Duplicate entry '20' for key 'a'
select * from t2;
a
20
100
select * from t2;
a
20
100
create trigger trg before insert on t1 for each row set new.a= 10;
ERROR 42000: TRIGGER command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
delete from t1;
create trigger trg before insert on t1 for each row set new.a= 10;
insert into t1 values (1);
select * from t1;
a
10
select * from t1;
a
10
delete from t1;
drop trigger trg;
insert into t1 values (1);
select * from t1;
a
1
select * from t1;
a
1
create procedure foo()
not deterministic
reads sql data
select * from t1;
call foo();
a
1
drop procedure foo;
drop function fn1;
drop database mysqltest1;
drop user "zedjzlcsjhd"@127.0.0.1;
use test;
use test;
drop function if exists f1;
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|
create view v1 as select 1 as a;
create table t1 (a int);
insert into t1 (a) values (f1());
select * from t1;
a
1
drop view v1;
drop function f1;
select * from t1;
a
1
DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(col VARCHAR(10));
CREATE PROCEDURE p1(arg VARCHAR(10))
INSERT INTO t1 VALUES(arg);
CALL p1('test');
SELECT * FROM t1;
col
test
SELECT * FROM t1;
col
test
DROP PROCEDURE p1;

---> Test for BUG#20438

---> Preparing environment...
---> connection: master
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;

---> Synchronizing slave with master...

---> connection: master

---> Creating procedure...
/*!50003 CREATE PROCEDURE p1() SET @a = 1 */;
/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */;

---> Checking on master...
SHOW CREATE PROCEDURE p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
RETURN 0	latin1	latin1_swedish_ci	latin1_swedish_ci

---> Synchronizing slave with master...
---> connection: master

---> Checking on slave...
SHOW CREATE PROCEDURE p1;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
SET @a = 1	latin1	latin1_swedish_ci	latin1_swedish_ci
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
RETURN 0	latin1	latin1_swedish_ci	latin1_swedish_ci

---> connection: master

---> Cleaning up...
DROP PROCEDURE p1;
DROP FUNCTION f1;
drop table t1;
drop database if exists mysqltest;
drop database if exists mysqltest2;
create database mysqltest;
create database mysqltest2;
use mysqltest2;
create table t ( t integer );
create procedure mysqltest.test() begin end;
insert into t values ( 1 );
create procedure `\\`.test() begin end;
ERROR 42000: Unknown database '\\'
create function f1 () returns int
begin
insert into t values (1);
return 0;
end|
use mysqltest;
set @a:= mysqltest2.f1();
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	drop database if exists mysqltest1
master-bin.000001	#	Query	#	#	create database mysqltest1
master-bin.000001	#	Query	#	#	use `mysqltest1`; create table t1 (a varchar(100))
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` 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	#	#	use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values (unix_timestamp())
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t1
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo2()
select * from mysqltest1.t1
master-bin.000001	#	Query	#	#	use `mysqltest1`; alter procedure foo2 contains sql
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop table t1
master-bin.000001	#	Query	#	#	use `mysqltest1`; create table t1 (a int)
master-bin.000001	#	Query	#	#	use `mysqltest1`; create table t2 like t1
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo3()
deterministic
insert into t1 values (15)
master-bin.000001	#	Query	#	#	use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
master-bin.000001	#	Query	#	#	use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
master-bin.000001	#	Query	#	#	use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` procedure foo4()
deterministic
begin
insert into t2 values(3);
insert into t1 values (5);
end
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t2 values(3)
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values (15)
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t2 values(3)
master-bin.000001	#	Query	#	#	use `mysqltest1`; alter procedure foo4 sql security invoker
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t2 values(3)
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values (5)
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t2
master-bin.000001	#	Query	#	#	use `mysqltest1`; alter table t2 add unique (a)
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop procedure foo4
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo4()
deterministic
begin
insert into t2 values(20),(20);
end
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t2 values(20),(20)
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop procedure foo4
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop procedure foo
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop procedure foo2
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop procedure foo3
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1(x int)
returns int
deterministic
begin
insert into t1 values (x);
return x+2;
end
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete t1,t2 from t1,t2
master-bin.000001	#	Query	#	#	use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t2 values(fn1(21))
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop function fn1
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1()
returns int
no sql
begin
return unix_timestamp();
end
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t1
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values(fn1())
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` function fn2()
returns int
no sql
begin
return unix_timestamp();
end
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn3()
returns int
not deterministic
reads sql data
begin
return 0;
end
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t2
master-bin.000001	#	Query	#	#	use `mysqltest1`; alter table t2 add unique (a)
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop function fn1
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1(x int)
returns int
begin
insert into t2 values(x),(x);
return 10;
end
master-bin.000001	#	Query	#	#	use `mysqltest1`; SELECT `mysqltest1`.`fn1`(100)
master-bin.000001	#	Query	#	#	use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t1
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values (1)
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t1
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop trigger trg
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values (1)
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo()
not deterministic
reads sql data
select * from t1
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop procedure foo
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop function fn1
master-bin.000001	#	Query	#	#	drop database mysqltest1
master-bin.000001	#	Query	#	#	drop user "zedjzlcsjhd"@127.0.0.1
master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` 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
master-bin.000001	#	Query	#	#	use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 as a
master-bin.000001	#	Query	#	#	use `test`; create table t1 (a int)
master-bin.000001	#	Query	#	#	use `test`; insert into t1 (a) values (f1())
master-bin.000001	#	Query	#	#	use `test`; drop view v1
master-bin.000001	#	Query	#	#	use `test`; drop function f1
master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS t1
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1(col VARCHAR(10))
master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE p1(arg VARCHAR(10))
INSERT INTO t1 VALUES(arg)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test'))
master-bin.000001	#	Query	#	#	use `test`; DROP PROCEDURE p1
master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE p1() SET @a = 1
master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION f1() RETURNS INT RETURN 0
master-bin.000001	#	Query	#	#	use `test`; DROP PROCEDURE p1
master-bin.000001	#	Query	#	#	use `test`; DROP FUNCTION f1
master-bin.000001	#	Query	#	#	use `test`; drop table t1
master-bin.000001	#	Query	#	#	drop database if exists mysqltest
master-bin.000001	#	Query	#	#	drop database if exists mysqltest2
master-bin.000001	#	Query	#	#	create database mysqltest
master-bin.000001	#	Query	#	#	create database mysqltest2
master-bin.000001	#	Query	#	#	use `mysqltest2`; create table t ( t integer )
master-bin.000001	#	Query	#	#	use `mysqltest2`; CREATE DEFINER=`root`@`localhost` procedure mysqltest.test() begin end
master-bin.000001	#	Query	#	#	use `mysqltest2`; insert into t values ( 1 )
master-bin.000001	#	Query	#	#	use `mysqltest2`; CREATE DEFINER=`root`@`localhost` function f1 () returns int
begin
insert into t values (1);
return 0;
end
master-bin.000001	#	Query	#	#	use `mysqltest`; SELECT `mysqltest2`.`f1`()
set global log_bin_trust_function_creators=0;
set global log_bin_trust_function_creators=0;
drop database mysqltest;
drop database mysqltest2;
End of 5.0 tests
End of 5.1 tests

1 by brian clean slate	1	stop slave;
	2	drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
	3	reset master;
	4	reset slave;
	5	drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
	6	start slave;
	7	drop database if exists mysqltest1;
	8	create database mysqltest1;
	9	use mysqltest1;
	10	create table t1 (a varchar(100));
	11	use mysqltest1;
	12	create procedure foo()
	13	begin
	14	declare b int;
	15	set b = 8;
	16	insert into t1 values (b);
	17	insert into t1 values (unix_timestamp());
	18	end\|
	19	select * from mysql.proc where name='foo' and db='mysqltest1';
	20	db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
	21	mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin
	22	declare b int;
	23	set b = 8;
	24	insert into t1 values (b);
	25	insert into t1 values (unix_timestamp());
	26	end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
	27	declare b int;
	28	set b = 8;
	29	insert into t1 values (b);
	30	insert into t1 values (unix_timestamp());
	31	end
	32	select * from mysql.proc where name='foo' and db='mysqltest1';
	33	db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
	34	mysqltest1 foo PROCEDURE foo SQL CONTAINS_SQL NO DEFINER begin
	35	declare b int;
	36	set b = 8;
	37	insert into t1 values (b);
	38	insert into t1 values (unix_timestamp());
	39	end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
	40	declare b int;
	41	set b = 8;
	42	insert into t1 values (b);
	43	insert into t1 values (unix_timestamp());
	44	end
	45	set timestamp=1000000000;
	46	call foo();
	47	select * from t1;
	48	a
	49	8
	50	1000000000
	51	select * from t1;
	52	a
	53	8
	54	1000000000
	55	delete from t1;
	56	create procedure foo2()
	57	select * from mysqltest1.t1;
	58	call foo2();
	59	a
	60	alter procedure foo2 contains sql;
	61	drop table t1;
	62	create table t1 (a int);
	63	create table t2 like t1;
	64	create procedure foo3()
65	deterministic
66	insert into t1 values (15);
67	grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1;
68	grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1;
69	grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1;
70	SELECT 1;
71	1
72	1
73	create procedure foo4()
74	deterministic
75	begin
76	insert into t2 values(3);
77	insert into t1 values (5);
78	end\|
79	call foo4();
80	Got one of the listed errors
81	call foo3();
82	show warnings;
83	Level Code Message
84	call foo4();
85	Got one of the listed errors
86	alter procedure foo4 sql security invoker;
87	call foo4();
88	show warnings;
89	Level Code Message
90	select * from t1;
91	a
92	15
93	5
94	select * from t2;
95	a
96	3
97	3
98	3
99	select * from t1;
100	a
101	15
102	5
103	select * from t2;
104	a
105	3
106	3
107	3
108	delete from t2;
109	alter table t2 add unique (a);
110	drop procedure foo4;
111	create procedure foo4()
112	deterministic
113	begin
114	insert into t2 values(20),(20);
115	end\|
116	call foo4();
117	ERROR 23000: Duplicate entry '20' for key 'a'
118	show warnings;
119	Level Code Message
120	Error 1062 Duplicate entry '20' for key 'a'
121	select * from t2;
122	a
123	20
124	select * from t2;
125	a
126	20
127	select * from mysql.proc where name="foo4" and db='mysqltest1';
128	db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
129	mysqltest1 foo4 PROCEDURE foo4 SQL CONTAINS_SQL YES DEFINER begin
130	insert into t2 values(20),(20);
131	end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
132	insert into t2 values(20),(20);
133	end
134	drop procedure foo4;
135	select * from mysql.proc where name="foo4" and db='mysqltest1';
136	db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
137	select * from mysql.proc where name="foo4" and db='mysqltest1';
138	db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
139	drop procedure foo;
140	drop procedure foo2;
141	drop procedure foo3;
142	create function fn1(x int)
143	returns int
144	begin
145	insert into t1 values (x);
146	return x+2;
147	end\|
148	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)
149	create function fn1(x int)
150	returns int
151	deterministic
152	begin
153	insert into t1 values (x);
154	return x+2;
155	end\|
156	delete t1,t2 from t1,t2;
157	select fn1(20);
158	fn1(20)
159	22
160	insert into t2 values(fn1(21));
161	select * from t1;
162	a
163	21
164	20
165	select * from t2;
166	a
167	23
168	select * from t1;
169	a
170	21
171	20
172	select * from t2;
173	a
174	23
175	drop function fn1;
176	create function fn1()
177	returns int
178	no sql
179	begin
180	return unix_timestamp();
181	end\|
182	alter function fn1 contains sql;
183	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)
184	delete from t1;
185	set timestamp=1000000000;
186	insert into t1 values(fn1());
187	create function fn2()
188	returns int
189	no sql
190	begin
191	return unix_timestamp();
192	end\|
193	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)
194	set global log_bin_trust_function_creators=0;
195	set global log_bin_trust_function_creators=1;
196	set global log_bin_trust_function_creators=1;
197	create function fn2()
198	returns int
199	no sql
200	begin
201	return unix_timestamp();
202	end\|
203	create function fn3()
204	returns int
205	not deterministic
206	reads sql data
207	begin
208	return 0;
209	end\|
210	select fn3();
211	fn3()
212	0
213	select * from mysql.proc where db='mysqltest1';
214	db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
215	mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin
216	return unix_timestamp();
217	end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
218	return unix_timestamp();
219	end
220	mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin
221	return unix_timestamp();
222	end zedjzlcsjhd@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
223	return unix_timestamp();
224	end
225	mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin
226	return 0;
227	end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
228	return 0;
229	end
230	select * from t1;
231	a
232	1000000000
233	use mysqltest1;
234	select * from t1;
235	a
236	1000000000
237	select * from mysql.proc where db='mysqltest1';
238	db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
239	mysqltest1 fn1 FUNCTION fn1 SQL NO_SQL NO DEFINER int(11) begin
240	return unix_timestamp();
241	end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
242	return unix_timestamp();
243	end
244	mysqltest1 fn2 FUNCTION fn2 SQL NO_SQL NO DEFINER int(11) begin
245	return unix_timestamp();
246	end zedjzlcsjhd@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
247	return unix_timestamp();
248	end
249	mysqltest1 fn3 FUNCTION fn3 SQL READS_SQL_DATA NO DEFINER int(11) begin
250	return 0;
251	end root@localhost # # latin1 latin1_swedish_ci latin1_swedish_ci begin
252	return 0;
253	end
254	delete from t2;
255	alter table t2 add unique (a);
256	drop function fn1;
257	create function fn1(x int)
258	returns int
259	begin
260	insert into t2 values(x),(x);
261	return 10;
262	end\|
263	do fn1(100);
264	Warnings:
265	Error 1062 Duplicate entry '100' for key 'a'
266	select fn1(20);
267	ERROR 23000: Duplicate entry '20' for key 'a'
268	select * from t2;
269	a
270	20
271	100
272	select * from t2;
273	a
274	20
275	100
276	create trigger trg before insert on t1 for each row set new.a= 10;
277	ERROR 42000: TRIGGER command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
278	delete from t1;
279	create trigger trg before insert on t1 for each row set new.a= 10;
280	insert into t1 values (1);
281	select * from t1;
282	a
283	10
284	select * from t1;
285	a
286	10
287	delete from t1;
288	drop trigger trg;
289	insert into t1 values (1);
290	select * from t1;
291	a
292	1
293	select * from t1;
294	a
295	1
296	create procedure foo()
297	not deterministic
298	reads sql data
299	select * from t1;
300	call foo();
301	a
302	1
303	drop procedure foo;
304	drop function fn1;
305	drop database mysqltest1;
306	drop user "zedjzlcsjhd"@127.0.0.1;
307	use test;
308	use test;
309	drop function if exists f1;
310	create function f1() returns int reads sql data
311	begin
312	declare var integer;
313	declare c cursor for select a from v1;
314	open c;
315	fetch c into var;
316	close c;
317	return var;
318	end\|
319	create view v1 as select 1 as a;
320	create table t1 (a int);
321	insert into t1 (a) values (f1());
322	select * from t1;
323	a
324	1
325	drop view v1;
326	drop function f1;
327	select * from t1;
328	a
329	1
330	DROP PROCEDURE IF EXISTS p1;
331	DROP TABLE IF EXISTS t1;
332	CREATE TABLE t1(col VARCHAR(10));
333	CREATE PROCEDURE p1(arg VARCHAR(10))
334	INSERT INTO t1 VALUES(arg);
335	CALL p1('test');
336	SELECT * FROM t1;
337	col
338	test
339	SELECT * FROM t1;
340	col
341	test
342	DROP PROCEDURE p1;
343
344	---> Test for BUG#20438
345
346	---> Preparing environment...
347	---> connection: master
348	DROP PROCEDURE IF EXISTS p1;
349	DROP FUNCTION IF EXISTS f1;
350
351	---> Synchronizing slave with master...
352
353	---> connection: master
354
355	---> Creating procedure...
356	/!50003 CREATE PROCEDURE p1() SET @a = 1 /;
357	/!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 /;
358
359	---> Checking on master...
360	SHOW CREATE PROCEDURE p1;
361	Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
362	p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
363	SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci
364	SHOW CREATE FUNCTION f1;
365	Function sql_mode Create Function character_set_client collation_connection Database Collation
366	f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
367	RETURN 0 latin1 latin1_swedish_ci latin1_swedish_ci
368
369	---> Synchronizing slave with master...
370	---> connection: master
371
372	---> Checking on slave...
373	SHOW CREATE PROCEDURE p1;
374	Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
375	p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
376	SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci
377	SHOW CREATE FUNCTION f1;
378	Function sql_mode Create Function character_set_client collation_connection Database Collation
379	f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
380	RETURN 0 latin1 latin1_swedish_ci latin1_swedish_ci
381
382	---> connection: master
383
384	---> Cleaning up...
385	DROP PROCEDURE p1;
386	DROP FUNCTION f1;
387	drop table t1;
388	drop database if exists mysqltest;
389	drop database if exists mysqltest2;
390	create database mysqltest;
391	create database mysqltest2;
392	use mysqltest2;
393	create table t ( t integer );
394	create procedure mysqltest.test() begin end;
395	insert into t values ( 1 );
396	create procedure `\\`.test() begin end;
397	ERROR 42000: Unknown database '\\'
398	create function f1 () returns int
399	begin
400	insert into t values (1);
401	return 0;
402	end\|
403	use mysqltest;
404	set @a:= mysqltest2.f1();
405	show binlog events from <binlog_start>;
406	Log_name Pos Event_type Server_id End_log_pos Info
407	master-bin.000001 # Query # # drop database if exists mysqltest1
408	master-bin.000001 # Query # # create database mysqltest1
409	master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a varchar(100))
410	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo()
411	begin
412	declare b int;
413	set b = 8;
414	insert into t1 values (b);
415	insert into t1 values (unix_timestamp());
416	end
417	master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
418	master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (unix_timestamp())
419	master-bin.000001 # Query # # use `mysqltest1`; delete from t1
420	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo2()
421	select * from mysqltest1.t1
422	master-bin.000001 # Query # # use `mysqltest1`; alter procedure foo2 contains sql
423	master-bin.000001 # Query # # use `mysqltest1`; drop table t1
424	master-bin.000001 # Query # # use `mysqltest1`; create table t1 (a int)
425	master-bin.000001 # Query # # use `mysqltest1`; create table t2 like t1
426	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo3()
427	deterministic
428	insert into t1 values (15)
429	master-bin.000001 # Query # # use `mysqltest1`; grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
430	master-bin.000001 # Query # # use `mysqltest1`; grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
431	master-bin.000001 # Query # # use `mysqltest1`; grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
432	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` procedure foo4()
433	deterministic
434	begin
435	insert into t2 values(3);
436	insert into t1 values (5);
437	end
438	master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(3)
439	master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (15)
440	master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(3)
441	master-bin.000001 # Query # # use `mysqltest1`; alter procedure foo4 sql security invoker
442	master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(3)
443	master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (5)
444	master-bin.000001 # Query # # use `mysqltest1`; delete from t2
445	master-bin.000001 # Query # # use `mysqltest1`; alter table t2 add unique (a)
446	master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo4
447	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo4()
448	deterministic
449	begin
450	insert into t2 values(20),(20);
451	end
452	master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(20),(20)
453	master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo4
454	master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo
455	master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo2
456	master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo3
457	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1(x int)
458	returns int
459	deterministic
460	begin
461	insert into t1 values (x);
462	return x+2;
463	end
464	master-bin.000001 # Query # # use `mysqltest1`; delete t1,t2 from t1,t2
465	master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
466	master-bin.000001 # Query # # use `mysqltest1`; insert into t2 values(fn1(21))
467	master-bin.000001 # Query # # use `mysqltest1`; drop function fn1
468	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1()
469	returns int
470	no sql
471	begin
472	return unix_timestamp();
473	end
474	master-bin.000001 # Query # # use `mysqltest1`; delete from t1
475	master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values(fn1())
476	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` function fn2()
477	returns int
478	no sql
479	begin
480	return unix_timestamp();
481	end
482	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn3()
483	returns int
484	not deterministic
485	reads sql data
486	begin
487	return 0;
488	end
489	master-bin.000001 # Query # # use `mysqltest1`; delete from t2
490	master-bin.000001 # Query # # use `mysqltest1`; alter table t2 add unique (a)
491	master-bin.000001 # Query # # use `mysqltest1`; drop function fn1
492	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn1(x int)
493	returns int
494	begin
495	insert into t2 values(x),(x);
496	return 10;
497	end
498	master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(100)
499	master-bin.000001 # Query # # use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
500	master-bin.000001 # Query # # use `mysqltest1`; delete from t1
501	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10
502	master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (1)
503	master-bin.000001 # Query # # use `mysqltest1`; delete from t1
504	master-bin.000001 # Query # # use `mysqltest1`; drop trigger trg
505	master-bin.000001 # Query # # use `mysqltest1`; insert into t1 values (1)
506	master-bin.000001 # Query # # use `mysqltest1`; CREATE DEFINER=`root`@`localhost` procedure foo()
507	not deterministic
508	reads sql data
509	select * from t1
510	master-bin.000001 # Query # # use `mysqltest1`; drop procedure foo
511	master-bin.000001 # Query # # use `mysqltest1`; drop function fn1
512	master-bin.000001 # Query # # drop database mysqltest1
513	master-bin.000001 # Query # # drop user "zedjzlcsjhd"@127.0.0.1
514	master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` function f1() returns int reads sql data
515	begin
516	declare var integer;
517	declare c cursor for select a from v1;
518	open c;
519	fetch c into var;
520	close c;
521	return var;
522	end
523	master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 as a
524	master-bin.000001 # Query # # use `test`; create table t1 (a int)
525	master-bin.000001 # Query # # use `test`; insert into t1 (a) values (f1())
526	master-bin.000001 # Query # # use `test`; drop view v1
527	master-bin.000001 # Query # # use `test`; drop function f1
528	master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t1
529	master-bin.000001 # Query # # use `test`; CREATE TABLE t1(col VARCHAR(10))
530	master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE p1(arg VARCHAR(10))
531	INSERT INTO t1 VALUES(arg)
532	master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test'))
533	master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1
534	master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE p1() SET @a = 1
535	master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION f1() RETURNS INT RETURN 0
536	master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1
537	master-bin.000001 # Query # # use `test`; DROP FUNCTION f1
538	master-bin.000001 # Query # # use `test`; drop table t1
539	master-bin.000001 # Query # # drop database if exists mysqltest
540	master-bin.000001 # Query # # drop database if exists mysqltest2
541	master-bin.000001 # Query # # create database mysqltest
542	master-bin.000001 # Query # # create database mysqltest2
543	master-bin.000001 # Query # # use `mysqltest2`; create table t ( t integer )
544	master-bin.000001 # Query # # use `mysqltest2`; CREATE DEFINER=`root`@`localhost` procedure mysqltest.test() begin end
545	master-bin.000001 # Query # # use `mysqltest2`; insert into t values ( 1 )
546	master-bin.000001 # Query # # use `mysqltest2`; CREATE DEFINER=`root`@`localhost` function f1 () returns int
547	begin
548	insert into t values (1);
549	return 0;
550	end
551	master-bin.000001 # Query # # use `mysqltest`; SELECT `mysqltest2`.`f1`()
552	set global log_bin_trust_function_creators=0;
553	set global log_bin_trust_function_creators=0;
554	drop database mysqltest;
555	drop database mysqltest2;
556	End of 5.0 tests
557	End of 5.1 tests