include/master-slave.inc
[connection master]
connection master;
SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
drop procedure if exists p1;
drop procedure if exists p2;
drop function if exists f1;
drop table if exists t1,t2;
drop view if exists v1;
create table t1 (a int);
SET GLOBAL log_bin_trust_function_creators = 1;
create procedure p1()
begin
declare spv int default 0;
while spv < 5 do
insert into t1 values(spv+1);
set spv=spv+1;
end while;
end//
call p1();
connection slave;
connection slave;
SELECT * FROM t1 ORDER BY a;
a
1
2
3
4
5
connection master;
SELECT * FROM t1 ORDER BY a;
a
1
2
3
4
5
create procedure p2()
begin
declare a int default 4;
create table t2 as select a;
end//
call p2();
SELECT * FROM t2 ORDER BY a;
a
4
connection slave;
connection slave;
SELECT * FROM t2 ORDER BY a;
a
4
connection master;
drop procedure p1;
drop procedure p2;
drop table t2;
create function f1(x int) returns int
begin
insert into t1 values(x);
return x+1;
end//
create procedure p1(a int, b int)
begin
declare v int default f1(5);
if (f1(6)) then
select 'yes';
end if;
set v = f1(7);
while f1(8) < 1 do 
select 'this cant be';
end while;
end//
call p1(f1(1), f1(2));
yes
yes
SELECT * FROM t1 ORDER BY a;
a
1
1
2
2
3
4
5
5
6
7
8
create table t2(a int);
insert into t2 values (10),(11);
SELECT a,f1(a) FROM t2 ORDER BY a;
a	f1(a)
10	11
11	12
insert into t2 select f1(3);
SELECT 'master:',a FROM t1 ORDER BY a;
master:	a
master:	1
master:	1
master:	2
master:	2
master:	3
master:	3
master:	4
master:	5
master:	5
master:	6
master:	7
master:	8
master:	10
master:	11
connection slave;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;
slave:	a
slave:	1
slave:	1
slave:	2
slave:	2
slave:	3
slave:	3
slave:	4
slave:	5
slave:	5
slave:	6
slave:	7
slave:	8
slave:	10
slave:	11
connection master;
drop procedure p1;
delete from t1;
delete from t2;
delete from t1;
insert into t2 values(1),(2);
create view v1 as select f1(a) as f from t2;
select * from v1 order by f;
f
2
3
SELECT 'master:',a FROM t1 ORDER BY a;
master:	a
master:	1
master:	2
connection slave;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;
slave:	a
slave:	1
slave:	2
connection master;
drop view v1;
delete from t1;
prepare s1 from 'select f1(?)';
set @xx=123;
execute s1 using @xx;
f1(?)
124
SELECT 'master:',a FROM t1 ORDER BY a;
master:	a
master:	123
connection slave;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;
slave:	a
slave:	123
connection master;
delete from t1;
create procedure p1(spv int)
begin
declare c cursor for select f1(spv) from t2;
while (spv > 2) do
open c;
fetch c into spv;
close c;
set spv= spv - 10;
end while;
end//
call p1(15);
SELECT 'master:',a FROM t1 ORDER BY a;
master:	a
master:	6
master:	6
master:	15
master:	15
connection slave;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;
slave:	a
slave:	6
slave:	6
slave:	15
slave:	15
connection master;
drop procedure p1;
drop function f1;
drop table t1,t2;
create table t1 (a int);
create procedure p1()
begin
insert into t1 values(@x);
set @x=@x+1;
insert into t1 values(@x);
if (f2()) then
insert into t1 values(1243);
end if;
end//
create function f2() returns int
begin
insert into t1 values(@z);
set @z=@z+1;
insert into t1 values(@z);
return 0;
end//
create function f1() returns int
begin
insert into t1 values(@y);
call p1();
return 0;
end//
set @x=10;
set @y=20;
set @z=100;
select f1();
f1()
0
set @x=30;
call p1();
SELECT 'master', a FROM t1 ORDER BY a;
master	a
master	10
master	11
master	20
master	30
master	31
master	100
master	101
master	101
master	102
connection slave;
connection slave;
SELECT 'slave', a FROM t1 ORDER BY a;
slave	a
slave	10
slave	11
slave	20
slave	30
slave	31
slave	100
slave	101
slave	101
slave	102
connection master;
drop table t1;
drop function f1;
drop function f2;
drop procedure p1;
connection slave;
connection master;
create table t2 (b BIT(7));
create procedure sp_bug26199(bitvalue BIT(7))
begin
insert into t2 set b = bitvalue;
end //
create function sf_bug26199(b BIT(7)) returns int
begin
insert into t2 values(b);
return 0;
end//
call   sp_bug26199(b'1110');
call   sp_bug26199('\0');
select sf_bug26199(b'1111111');
sf_bug26199(b'1111111')
0
SET STATEMENT sql_mode = '' FOR
select sf_bug26199(b'101111111');
sf_bug26199(b'101111111')
0
Warnings:
Warning	1264	Out of range value for column 'b' at row 1
select sf_bug26199('\'');
sf_bug26199('\'')
0
select hex(b) from t2;
hex(b)
E
0
7F
7F
27
connection slave;
select hex(b) from t2;
hex(b)
E
0
7F
7F
27
connection master;
drop table t2;
drop procedure sp_bug26199;
drop function sf_bug26199;
SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
connection slave;
set names utf8;
CREATE FUNCTION f() RETURNS timestamp DETERMINISTIC
BEGIN RETURN '2012-12-21 12:12:12'; END |
CREATE PROCEDURE p(t timestamp) 
BEGIN
SET @t = t;
PREPARE stmt FROM " 
  UPDATE t1 SET a = @t WHERE '2012-12-31 08:00:00' < f() ";
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END |
create table t1 (a timestamp);
call p('2012-12-31 08:00:00');
drop table t1;
drop procedure p;
drop function f;
end of the tests
include/rpl_end.inc