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
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	INVOKER			begin
insert into t2 values(3);
insert into t1 values (5);
end	zedjzlcsjhd@localhost	#	#			latin1	latin1_swedish_ci	latin1_swedish_ci	begin
insert into t2 values(3);
insert into t1 values (5);
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
20
21
select * from t2;
a
23
select * from t1;
a
20
21
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_routine_creators=1;
Warnings:
Warning	1287	'@@log_bin_trust_routine_creators' is deprecated and will be removed in a future release. Please 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	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
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;
set global log_bin_trust_function_creators=0;
set global log_bin_trust_function_creators=0;
End of 5.0 tests
reset master;
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 '\\'
drop database mysqltest;
drop database mysqltest2;
End of 5.1 tests