1
##########################################
3
# Change Date: 2006-05-02
4
# Change: Added Order By for NDB testing
5
##########################################
7
# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
8
-- source include/master-slave.inc
10
# ****************************************************************
15
drop procedure if exists p1;
16
drop procedure if exists p2;
17
drop function if exists f1;
18
drop table if exists t1,t2;
19
drop view if exists v1;
21
create table t1 (a int);
23
SET GLOBAL log_bin_trust_function_creators = 1;
25
# 1. Test simple variables use.
29
declare spv int default 0;
31
insert into t1 values(spv+1);
39
sync_slave_with_master;
41
SELECT * FROM t1 ORDER BY a;
43
SELECT * FROM t1 ORDER BY a;
45
# 2. Test SP variable name
49
declare a int default 4;
50
create table t2 as select a;
55
SELECT * FROM t2 ORDER BY a;
56
sync_slave_with_master;
58
SELECT * FROM t2 ORDER BY a;
65
# 3. Test FUNCTIONs in various places
68
create function f1(x int) returns int
70
insert into t1 values(x);
74
create procedure p1(a int, b int)
76
declare v int default f1(5);
82
select 'this cant be';
88
call p1(f1(1), f1(2));
89
SELECT * FROM t1 ORDER BY a;
91
create table t2(a int);
92
insert into t2 values (10),(11);
93
SELECT a,f1(a) FROM t2 ORDER BY a;
95
# This shouldn't put separate 'call f1(3)' into binlog:
96
insert into t2 select f1(3);
97
SELECT 'master:',a FROM t1 ORDER BY a;
99
sync_slave_with_master;
101
SELECT 'slave:',a FROM t1 ORDER BY a;
110
insert into t2 values(1),(2);
111
create view v1 as select f1(a) as f from t2;
112
select * from v1 order by f;
113
SELECT 'master:',a FROM t1 ORDER BY a;
115
sync_slave_with_master;
117
SELECT 'slave:',a FROM t1 ORDER BY a;
123
# 5. Prepared statements.
124
prepare s1 from 'select f1(?)';
126
execute s1 using @xx;
127
SELECT 'master:',a FROM t1 ORDER BY a;
129
sync_slave_with_master;
131
SELECT 'slave:',a FROM t1 ORDER BY a;
139
create procedure p1(spv int)
141
declare c cursor for select f1(spv) from t2;
151
SELECT 'master:',a FROM t1 ORDER BY a;
152
sync_slave_with_master;
154
SELECT 'slave:',a FROM t1 ORDER BY a;
161
# BUG#12637: User variables + SPs replication
162
create table t1 (a int);
164
create procedure p1()
166
insert into t1 values(@x);
168
insert into t1 values(@x);
170
insert into t1 values(1243);
174
create function f2() returns int
176
insert into t1 values(@z);
178
insert into t1 values(@z);
182
create function f1() returns int
184
insert into t1 values(@y);
199
SELECT 'master', a FROM t1 ORDER BY a;
200
sync_slave_with_master;
202
SELECT 'slave', a FROM t1 ORDER BY a;
213
sync_slave_with_master;
216
# bug#26199 Replication Failure on Slave when using stored procs
217
# with bit-type parameters
221
create table t2 (b BIT(7));
223
create procedure sp_bug26199(bitvalue BIT(7))
225
insert into t2 set b = bitvalue;
228
create function sf_bug26199(b BIT(7)) returns int
230
insert into t2 values(b);
238
call sp_bug26199(b'1110');
239
call sp_bug26199('\0');
240
select sf_bug26199(b'1111111');
241
select sf_bug26199(b'101111111');
242
select sf_bug26199('\'');
243
select hex(b) from t2;
245
sync_slave_with_master;
247
select hex(b) from t2;
254
drop procedure sp_bug26199;
255
drop function sf_bug26199;
257
sync_slave_with_master;
259
SET GLOBAL log_bin_trust_function_creators = 0;
261
--echo end of the tests