Debian Codegraph @

mysql-test/suite/rpl/t/rpl_sp_effects.test

raw
1 ##########################################
2 # Change Author: JBM
3 # Change Date: 2006-05-02
4 ##########################################
5
6 # Test of replication of stored procedures (WL#2146 for MySQL 5.0)
7 -- source include/master-slave.inc
8
9 # ****************************************************************
10 connection master;
11
12 SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
13
14 # cleanup
15 --disable_warnings
16 drop procedure if exists p1;
17 drop procedure if exists p2;
18 drop function if exists f1;
19 drop table if exists t1,t2;
20 drop view if exists v1;
21 --enable_warnings
22 create table t1 (a int);
23
24 SET GLOBAL log_bin_trust_function_creators = 1;
25
26 # 1. Test simple variables use.
27 delimiter //;
28 create procedure p1()
29 begin
30 declare spv int default 0;
31 while spv < 5 do
32 insert into t1 values(spv+1);
33 set spv=spv+1;
34 end while;
35 end//
36 delimiter ;//
37
38 call p1();
39
40 sync_slave_with_master;
41 connection slave;
42 SELECT * FROM t1 ORDER BY a;
43 connection master;
44 SELECT * FROM t1 ORDER BY a;
45
46 # 2. Test SP variable name
47 delimiter //;
48 create procedure p2()
49 begin
50 declare a int default 4;
51 create table t2 as select a;
52 end//
53 delimiter ;//
54
55 call p2();
56 SELECT * FROM t2 ORDER BY a;
57 sync_slave_with_master;
58 connection slave;
59 SELECT * FROM t2 ORDER BY a;
60
61 connection master;
62 drop procedure p1;
63 drop procedure p2;
64 drop table t2;
65
66 # 3. Test FUNCTIONs in various places
67
68 delimiter //;
69 create function f1(x int) returns int
70 begin
71 insert into t1 values(x);
72 return x+1;
73 end//
74
75 create procedure p1(a int, b int)
76 begin
77 declare v int default f1(5);
78 if (f1(6)) then
79 select 'yes';
80 end if;
81 set v = f1(7);
82 while f1(8) < 1 do
83 select 'this cant be';
84 end while;
85
86 end//
87 delimiter ;//
88
89 call p1(f1(1), f1(2));
90 SELECT * FROM t1 ORDER BY a;
91
92 create table t2(a int);
93 insert into t2 values (10),(11);
94 --disable_ps2_protocol
95 SELECT a,f1(a) FROM t2 ORDER BY a;
96 --enable_ps2_protocol
97
98 # This shouldn't put separate 'call f1(3)' into binlog:
99 insert into t2 select f1(3);
100 SELECT 'master:',a FROM t1 ORDER BY a;
101
102 sync_slave_with_master;
103 connection slave;
104 SELECT 'slave:',a FROM t1 ORDER BY a;
105
106 connection master;
107 drop procedure p1;
108 delete from t1;
109 delete from t2;
110
111 # 4. VIEWs
112 delete from t1;
113 insert into t2 values(1),(2);
114 create view v1 as select f1(a) as f from t2;
115 --disable_ps2_protocol
116 select * from v1 order by f;
117 --enable_ps2_protocol
118 SELECT 'master:',a FROM t1 ORDER BY a;
119
120 sync_slave_with_master;
121 connection slave;
122 SELECT 'slave:',a FROM t1 ORDER BY a;
123
124 connection master;
125 drop view v1;
126 delete from t1;
127
128 # 5. Prepared statements.
129 prepare s1 from 'select f1(?)';
130 set @xx=123;
131 execute s1 using @xx;
132 SELECT 'master:',a FROM t1 ORDER BY a;
133
134 sync_slave_with_master;
135 connection slave;
136 SELECT 'slave:',a FROM t1 ORDER BY a;
137
138 connection master;
139 delete from t1;
140
141 # 5. Cursors.
142 # t2 has (1),(2);
143 delimiter //;
144 create procedure p1(spv int)
145 begin
146 declare c cursor for select f1(spv) from t2;
147 while (spv > 2) do
148 open c;
149 fetch c into spv;
150 close c;
151 set spv= spv - 10;
152 end while;
153 end//
154 delimiter ;//
155 call p1(15);
156 SELECT 'master:',a FROM t1 ORDER BY a;
157 sync_slave_with_master;
158 connection slave;
159 SELECT 'slave:',a FROM t1 ORDER BY a;
160
161 connection master;
162 drop procedure p1;
163 drop function f1;
164 drop table t1,t2;
165
166 # BUG#12637: User variables + SPs replication
167 create table t1 (a int);
168 delimiter //;
169 create procedure p1()
170 begin
171 insert into t1 values(@x);
172 set @x=@x+1;
173 insert into t1 values(@x);
174 if (f2()) then
175 insert into t1 values(1243);
176 end if;
177 end//
178
179 create function f2() returns int
180 begin
181 insert into t1 values(@z);
182 set @z=@z+1;
183 insert into t1 values(@z);
184 return 0;
185 end//
186
187 create function f1() returns int
188 begin
189 insert into t1 values(@y);
190 call p1();
191 return 0;
192 end//
193
194 delimiter ;//
195
196 set @x=10;
197 set @y=20;
198 set @z=100;
199 --disable_ps2_protocol
200 select f1();
201 --enable_ps2_protocol
202
203 set @x=30;
204 call p1();
205
206 SELECT 'master', a FROM t1 ORDER BY a;
207 sync_slave_with_master;
208 connection slave;
209 SELECT 'slave', a FROM t1 ORDER BY a;
210
211 #
212 # cleanup
213 #
214
215 connection master;
216 drop table t1;
217 drop function f1;
218 drop function f2;
219 drop procedure p1;
220 sync_slave_with_master;
221
222 #
223 # bug#26199 Replication Failure on Slave when using stored procs
224 # with bit-type parameters
225
226 connection master;
227
228 create table t2 (b BIT(7));
229 delimiter //;
230 create procedure sp_bug26199(bitvalue BIT(7))
231 begin
232 insert into t2 set b = bitvalue;
233 end //
234
235 create function sf_bug26199(b BIT(7)) returns int
236 begin
237 insert into t2 values(b);
238 return 0;
239 end//
240
241 DELIMITER ;//
242
243
244
245 call sp_bug26199(b'1110');
246 call sp_bug26199('\0');
247 --disable_ps2_protocol
248 select sf_bug26199(b'1111111');
249 SET STATEMENT sql_mode = '' FOR
250 select sf_bug26199(b'101111111');
251 select sf_bug26199('\'');
252 --enable_ps2_protocol
253 select hex(b) from t2;
254
255 sync_slave_with_master;
256 #connection slave;
257 select hex(b) from t2;
258
259 #
260 # cleanup bug#26199
261 #
262 connection master;
263 drop table t2;
264 drop procedure sp_bug26199;
265 drop function sf_bug26199;
266
267 SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
268
269 sync_slave_with_master;
270
271 #
272 # Bug#16056537: MYSQLD CRASHES IN ITEM_FUNC_GET_USER_VAR::FIX_LENGTH_AND_DEC()
273 #
274 set names utf8;
275 --delimiter |
276 CREATE FUNCTION f() RETURNS timestamp DETERMINISTIC
277 BEGIN RETURN '2012-12-21 12:12:12'; END |
278 CREATE PROCEDURE p(t timestamp)
279 BEGIN
280 SET @t = t;
281 PREPARE stmt FROM "
282 UPDATE t1 SET a = @t WHERE '2012-12-31 08:00:00' < f() ";
283 EXECUTE stmt;
284 DEALLOCATE PREPARE stmt;
285 END |
286 --delimiter ;
287 create table t1 (a timestamp);
288 call p('2012-12-31 08:00:00');
289 drop table t1;
290 drop procedure p;
291 drop function f;
292
293 --echo end of the tests
294 --source include/rpl_end.inc