Debian Codegraph @

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

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