1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
|
# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
source include/master-slave.inc;
# ****************************************************************
connection master;
# cleanup
--disable_warnings
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;
--enable_warnings
create table t1 (a int);
# 1. Test simple variables use.
delimiter //;
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//
delimiter ;//
call p1();
sync_slave_with_master;
connection slave;
select * from t1;
connection master;
delete from t1;
# 2. Test SP variable name
delimiter //;
create procedure p2()
begin
declare a int default 4;
create table t2 as select a;
end//
delimiter ;//
call p2();
select * from t2;
sync_slave_with_master;
connection slave;
select * from t2;
connection master;
drop procedure p1;
drop procedure p2;
drop table t2;
# 3. Test FUNCTIONs in various places
delimiter //;
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//
delimiter ;//
call p1(f1(1), f1(2));
select * from t1;
create table t2(a int);
insert into t2 values (10),(11);
select a,f1(a) from t2;
# This shouldn't put separate 'call f1(3)' into binlog:
insert into t2 select f1(3);
select 'master:',a from t1;
sync_slave_with_master;
connection slave;
select 'slave:',a from t1;
connection master;
drop procedure p1;
delete from t1;
delete from t2;
# 4. VIEWs
delete from t1;
insert into t2 values(1),(2);
create view v1 as select f1(a) from t2;
select * from v1;
select 'master:',a from t1;
sync_slave_with_master;
connection slave;
select 'slave:',a from t1;
connection master;
drop view v1;
delete from t1;
# 5. Prepared statements.
prepare s1 from 'select f1(?)';
set @xx=123;
execute s1 using @xx;
select 'master:',a from t1;
sync_slave_with_master;
connection slave;
select 'slave:',a from t1;
connection master;
delete from t1;
# 5. Cursors.
# t2 has (1),(2);
delimiter //;
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//
delimiter ;//
call p1(15);
select 'master:',a from t1;
sync_slave_with_master;
connection slave;
select 'slave:',a from t1;
connection master;
drop procedure p1;
drop function f1;
drop table t1,t2;
# BUG#12637: User variables + SPs replication
create table t1 (a int);
delimiter //;
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//
delimiter ;//
set @x=10;
set @y=20;
set @z=100;
select f1();
set @x=30;
call p1();
select 'master', a from t1;
sync_slave_with_master;
connection slave;
select 'slave', a from t1;
connection master;
drop table t1;
drop function f1;
drop function f2;
drop procedure p1;
sync_slave_with_master;
|
