Edit File - File Manager

Edit File: rpl_sp.result

include/master-slave.inc
[connection master]
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'
Warning	1196	Some non-transactional changed tables couldn't be rolled back
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 @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;
set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
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'
Warning	1196	Some non-transactional changed tables couldn't be rolled back
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	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values ( NAME_CONST('b',8))
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values (unix_timestamp())
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t1
master-bin.000001	#	Query	#	#	COMMIT
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` /* generated by server */
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	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t2 values(3)
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values (15)
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t2 values(3)
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	use `mysqltest1`; alter procedure foo4 sql security invoker
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t2 values(3)
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values (5)
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t2
master-bin.000001	#	Query	#	#	COMMIT
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	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t2 values(20),(20)
master-bin.000001	#	Query	#	#	COMMIT
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(11)
    DETERMINISTIC
begin
insert into t1 values (x);
return x+2;
end
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete t1,t2 from t1,t2
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t2 values(fn1(21))
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop function fn1
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`() RETURNS int(11)
    NO SQL
begin
return unix_timestamp();
end
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t1
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values(fn1())
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` FUNCTION `fn2`() RETURNS int(11)
    NO SQL
begin
return unix_timestamp();
end
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS int(11)
    READS SQL DATA
begin
return 0;
end
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t2
master-bin.000001	#	Query	#	#	COMMIT
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(11)
begin
insert into t2 values(x),(x);
return 10;
end
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; SELECT `mysqltest1`.`fn1`(100)
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; SELECT `mysqltest1`.`fn1`(20)
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t1
master-bin.000001	#	Query	#	#	COMMIT
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	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values (1)
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; delete from t1
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	use `mysqltest1`; drop trigger trg
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest1`; insert into t1 values (1)
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`()
    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`; drop function if exists f1
master-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
    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	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 (a) values (f1())
master-bin.000001	#	Query	#	#	COMMIT
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 PROCEDURE IF EXISTS p1
master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */
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	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci'))
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	use `test`; DROP PROCEDURE p1
master-bin.000001	#	Query	#	#	use `test`; DROP PROCEDURE IF EXISTS p1
master-bin.000001	#	Query	#	#	use `test`; DROP FUNCTION IF EXISTS f1
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(11)
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` /* generated by server */
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	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest2`; insert into t values ( 1 )
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	use `mysqltest2`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
begin
insert into t values (1);
return 0;
end
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `mysqltest`; SELECT `mysqltest2`.`f1`()
master-bin.000001	#	Query	#	#	COMMIT
set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
set @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
drop database mysqltest;
drop database mysqltest2;
use test;
/*!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|
show procedure status like '%mysqltestbug36570%';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	 mysqltestbug36570_p2	PROCEDURE	root@localhost	t	t	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
test	mysqltestbug36570_p1	PROCEDURE	root@localhost	t	t	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
show create procedure ` mysqltestbug36570_p2`;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
 mysqltestbug36570_p2		CREATE DEFINER=`root`@`localhost` PROCEDURE ` mysqltestbug36570_p2`( a int)
`label`:
begin
select a;
end	latin1	latin1_swedish_ci	latin1_swedish_ci
show procedure status like '%mysqltestbug36570%';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	 mysqltestbug36570_p2	PROCEDURE	root@localhost	t	t	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
test	mysqltestbug36570_p1	PROCEDURE	root@localhost	t	t	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
show create procedure ` mysqltestbug36570_p2`;
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
 mysqltestbug36570_p2		CREATE DEFINER=`root`@`localhost` PROCEDURE ` mysqltestbug36570_p2`( a int)
`label`:
begin
select a;
end	latin1	latin1_swedish_ci	latin1_swedish_ci
call ` mysqltestbug36570_p2`(42);
a
42
show function status like '%mysqltestbug36570%';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	mysqltestbug36570_f1	FUNCTION	root@localhost	t	t	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
flush logs;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
ROLLBACK/*!*/;
SET TIMESTAMP=t/*!*/;
SET @@session.pseudo_thread_id=999999999/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
drop database if exists mysqltest1
/*!*/;
SET TIMESTAMP=t/*!*/;
create database mysqltest1
/*!*/;
use `mysqltest1`/*!*/;
SET TIMESTAMP=t/*!*/;
create table t1 (a varchar(100))
/*!*/;
SET TIMESTAMP=t/*!*/;
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
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t1 values ( NAME_CONST('b',8))
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t1 values (unix_timestamp())
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
delete from t1
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `foo2`()
select * from mysqltest1.t1
/*!*/;
SET TIMESTAMP=t/*!*/;
alter procedure foo2 contains sql
/*!*/;
SET TIMESTAMP=t/*!*/;
DROP TABLE `t1` /* generated by server */
/*!*/;
SET TIMESTAMP=t/*!*/;
create table t1 (a int)
/*!*/;
SET TIMESTAMP=t/*!*/;
create table t2 like t1
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `foo3`()
    DETERMINISTIC
insert into t1 values (15)
/*!*/;
SET TIMESTAMP=t/*!*/;
grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1
/*!*/;
SET TIMESTAMP=t/*!*/;
grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1
/*!*/;
SET TIMESTAMP=t/*!*/;
grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` PROCEDURE `foo4`()
    DETERMINISTIC
begin
insert into t2 values(3);
insert into t1 values (5);
end
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t2 values(3)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t1 values (15)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t2 values(3)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
alter procedure foo4 sql security invoker
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t2 values(3)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t1 values (5)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
delete from t2
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
alter table t2 add unique (a)
/*!*/;
SET TIMESTAMP=t/*!*/;
drop procedure foo4
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `foo4`()
    DETERMINISTIC
begin
insert into t2 values(20),(20);
end
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t2 values(20),(20)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
drop procedure foo4
/*!*/;
SET TIMESTAMP=t/*!*/;
drop procedure foo
/*!*/;
SET TIMESTAMP=t/*!*/;
drop procedure foo2
/*!*/;
SET TIMESTAMP=t/*!*/;
drop procedure foo3
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
    DETERMINISTIC
begin
insert into t1 values (x);
return x+2;
end
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
delete t1,t2 from t1,t2
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
SELECT `mysqltest1`.`fn1`(20)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t2 values(fn1(21))
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
drop function fn1
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`() RETURNS int(11)
    NO SQL
begin
return unix_timestamp();
end
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
delete from t1
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t1 values(fn1())
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` FUNCTION `fn2`() RETURNS int(11)
    NO SQL
begin
return unix_timestamp();
end
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS int(11)
    READS SQL DATA
begin
return 0;
end
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
delete from t2
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
alter table t2 add unique (a)
/*!*/;
SET TIMESTAMP=t/*!*/;
drop function fn1
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
begin
insert into t2 values(x),(x);
return 10;
end
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
SELECT `mysqltest1`.`fn1`(100)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
SELECT `mysqltest1`.`fn1`(20)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
delete from t1
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t1 values (1)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
delete from t1
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
drop trigger trg
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t1 values (1)
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `foo`()
    READS SQL DATA
select * from t1
/*!*/;
SET TIMESTAMP=t/*!*/;
drop procedure foo
/*!*/;
SET TIMESTAMP=t/*!*/;
drop function fn1
/*!*/;
SET TIMESTAMP=t/*!*/;
drop database mysqltest1
/*!*/;
SET TIMESTAMP=t/*!*/;
drop user "zedjzlcsjhd"@127.0.0.1
/*!*/;
use `test`/*!*/;
SET TIMESTAMP=t/*!*/;
drop function if exists f1
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
    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
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 as a
/*!*/;
SET TIMESTAMP=t/*!*/;
create table t1 (a int)
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t1 (a) values (f1())
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
drop view v1
/*!*/;
SET TIMESTAMP=t/*!*/;
drop function f1
/*!*/;
SET TIMESTAMP=t/*!*/;
DROP PROCEDURE IF EXISTS p1
/*!*/;
SET TIMESTAMP=t/*!*/;
DROP TABLE IF EXISTS `t1` /* generated by server */
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE TABLE t1(col VARCHAR(10))
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(arg VARCHAR(10))
INSERT INTO t1 VALUES(arg)
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci'))
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
DROP PROCEDURE p1
/*!*/;
SET TIMESTAMP=t/*!*/;
DROP PROCEDURE IF EXISTS p1
/*!*/;
SET TIMESTAMP=t/*!*/;
DROP FUNCTION IF EXISTS f1
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
SET @a = 1
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
RETURN 0
/*!*/;
SET TIMESTAMP=t/*!*/;
DROP PROCEDURE p1
/*!*/;
SET TIMESTAMP=t/*!*/;
DROP FUNCTION f1
/*!*/;
SET TIMESTAMP=t/*!*/;
DROP TABLE `t1` /* generated by server */
/*!*/;
SET TIMESTAMP=t/*!*/;
drop database if exists mysqltest
/*!*/;
SET TIMESTAMP=t/*!*/;
drop database if exists mysqltest2
/*!*/;
SET TIMESTAMP=t/*!*/;
create database mysqltest
/*!*/;
SET TIMESTAMP=t/*!*/;
create database mysqltest2
/*!*/;
use `mysqltest2`/*!*/;
SET TIMESTAMP=t/*!*/;
create table t ( t integer )
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `mysqltest`.`test`()
begin end
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=t/*!*/;
insert into t values ( 1 )
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
begin
insert into t values (1);
return 0;
end
/*!*/;
SET TIMESTAMP=t/*!*/;
BEGIN
/*!*/;
use `mysqltest`/*!*/;
SET TIMESTAMP=t/*!*/;
SELECT `mysqltest2`.`f1`()
/*!*/;
SET TIMESTAMP=t/*!*/;
COMMIT
/*!*/;
SET TIMESTAMP=t/*!*/;
drop database mysqltest
/*!*/;
SET TIMESTAMP=t/*!*/;
drop database mysqltest2
/*!*/;
use `test`/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `mysqltestbug36570_p1`()
begin
select 1;
end
/*!*/;
use `mysql`/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.` mysqltestbug36570_p2`( a int)
`label`:
begin
select a;
end
/*!*/;
SET TIMESTAMP=t/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `test`.`mysqltestbug36570_f1`() RETURNS int(11)
    DETERMINISTIC
begin
return 3;
end
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
use test;
drop procedure mysqltestbug36570_p1;
drop procedure ` mysqltestbug36570_p2`;
drop function mysqltestbug36570_f1;
End of 5.0 tests
# End of 5.1 tests
#
# Test Bug#30977 Concurrent statement using stored
# function and DROP FUNCTION breaks SBR.
#
# Demonstrate that stored function DDL can not go through,
# or, worse yet, make its way into the binary log, while
# the stored function is in use.
# For that, try to insert a result of a stored function
# into a table. Block the insert in the beginning, waiting
# on a table lock. While insert is blocked, attempt to
# drop the routine. Verify that this attempt 
# blocks and waits for INSERT to complete. Commit and 
# reap the chain of events. Master and slave must contain
# identical data. Statements in the binrary log must be
# consistent with data in the table.
#
# --> connection default
drop table if exists t1, t2;
drop function if exists t1;
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
# Sending 'insert into t1 (a) values (f1())'...
insert into t1 (a) values (f1());
# Waitng for 'insert into t1 ...' to get blocked on table lock...
# Sending 'drop function f1'. It will wait till insert finishes.
drop function f1;;
# --> connection default
# Check that 'drop function f1' gets blocked.
# Now let's let 'insert' go through...
unlock tables;
# --> connection master
# Reaping 'insert into t1 (a) values (f1())'...
# --> connection master1
# Reaping 'drop function f1'
# --> connection master
select * from t1;
a
1
select * from  t1;
a
1
drop table t1, t2;
drop function f1;
ERROR 42000: FUNCTION test.f1 does not exist
#
# Bug #11918 Can't use a declared variable in LIMIT clause
#
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;
call p1(NULL);
call p1(0);
call p1(1);
call p1(2);
call p1(3);
select * from t1;
c1
7
8
9
10
select * from t1;
c1
7
8
9
10
call p1(-1);
select * from t1;
c1
select * from t1;
c1
# Cleanup
set @@session.binlog_format=@save_binlog_format;
drop table t1;
drop procedure p1;
# End of 5.5 tests.
include/rpl_end.inc

Back to File Manager