~drizzle-trunk/drizzle/development

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
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
##########################################
# Change Author: JBM
# Change Date: 2006-05-02
# Change: Added Order By for NDB testing
##########################################

# 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);

SET GLOBAL log_bin_trust_function_creators = 1;

# 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 ORDER BY a;
connection master;
SELECT * FROM t1 ORDER BY a;

# 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 ORDER BY a;
sync_slave_with_master;
connection slave;
SELECT * FROM t2 ORDER BY a;

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 ORDER BY a;

create table t2(a int);
insert into t2 values (10),(11);
SELECT a,f1(a) FROM t2 ORDER BY a;

# This shouldn't put separate 'call f1(3)' into binlog:
insert into t2 select f1(3);
SELECT 'master:',a FROM t1 ORDER BY a;

sync_slave_with_master;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;

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) as f from t2;
select * from v1 order by f;
SELECT 'master:',a FROM t1 ORDER BY a;

sync_slave_with_master;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;

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 ORDER BY a;

sync_slave_with_master;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;

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 ORDER BY a;
sync_slave_with_master;
connection slave;
SELECT 'slave:',a FROM t1 ORDER BY a;

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 ORDER BY a;
sync_slave_with_master;
connection slave;
SELECT 'slave', a FROM t1 ORDER BY a;

#
# cleanup
#

connection master;
drop table t1;
drop function f1;
drop function f2;
drop procedure p1;
sync_slave_with_master;

#
# bug#26199 Replication Failure on Slave when using stored procs
#           with bit-type parameters

connection master;

create table t2 (b BIT(7));
delimiter //;
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//

DELIMITER ;//



call   sp_bug26199(b'1110');
call   sp_bug26199('\0');
select sf_bug26199(b'1111111');
select sf_bug26199(b'101111111');
select sf_bug26199('\'');
select hex(b) from t2;

sync_slave_with_master;
#connection slave;
select hex(b) from t2;

#
# cleanup bug#26199
#
connection master;
drop table t2;
drop procedure sp_bug26199;
drop function sf_bug26199;

sync_slave_with_master;

SET GLOBAL log_bin_trust_function_creators = 0;

--echo end of the tests