Debian Codegraph @

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

raw
1 ###################################
2 #
3 # Test of replicating user variables
4 #
5 ###################################
6
7 -- source include/master-slave.inc
8
9 disable_query_log;
10 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
11 enable_query_log;
12
13 # Disable PS as the log positions differs
14 --disable_ps_protocol
15
16
17 # Clean up old slave's binlogs.
18 # The slave is started with --log-slave-updates
19 # and this test does SHOW BINLOG EVENTS on the slave's
20 # binlog. But previous tests can influence the current test's
21 # binlog (e.g. a temporary table in the previous test has not
22 # been explicitly deleted, or it has but the slave hasn't had
23 # enough time to catch it before STOP SLAVE,
24 # and at the beginning of the current
25 # test the slave immediately writes DROP TEMPORARY TABLE this_old_table).
26 # We wait for the slave to have written all he wants to the binlog
27 # (otherwise RESET MASTER may come too early).
28 sync_slave_with_master;
29 reset master;
30 connection master;
31
32 create table t1(n char(30));
33 set @i1:=12345678901234, @i2:=-12345678901234, @i3:=0, @i4:=-1;
34 set @s1:='This is a test', @r1:=12.5, @r2:=-12.5;
35 set @n1:=null;
36 set @s2:='', @s3:='abc\'def', @s4:= 'abc\\def', @s5:= 'abc''def';
37 insert into t1 values (@i1), (@i2), (@i3), (@i4);
38 insert into t1 values (@r1), (@r2);
39 insert into t1 values (@s1), (@s2), (@s3), (@s4), (@s5);
40 insert into t1 values (@n1);
41 insert into t1 values (@n2); # not explicitly set before
42 insert into t1 values (@a:=0), (@a:=@a+1), (@a:=@a+1);
43 insert into t1 values (@a+(@b:=@a+1));
44 set @q:='abc';
45 insert t1 values (@q), (@q:=concat(@q, 'n1')), (@q:=concat(@q, 'n2'));
46 set @a:=5;
47 insert into t1 values (@a),(@a);
48 # To flush the pending event, we add the following statement. RBR can
49 # concatenate the result of several statements, which SBR cannot.
50 select * from t1 where n = '<nonexistant>';
51 connection master1; # see if variable is reset in binlog when thread changes
52 insert into t1 values (@a),(@a),(@a*5);
53 SELECT * FROM t1 ORDER BY n;
54 sync_slave_with_master;
55 SELECT * FROM t1 ORDER BY n;
56 connection master;
57 insert into t1 select * FROM (select @var1 union select @var2) AS t2;
58 drop table t1;
59 --echo End of 4.1 tests.
60
61 # BUG#20141
62 # The following tests ensure that if user-defined variables are used in SF/Triggers
63 # that they are replicated correctly. These tests should be run in both SBR and RBR
64 # modes.
65
66 # This test uses a procedure that inserts data values based on the value of a
67 # user-defined variable. It also has a trigger that inserts data based on the
68 # same variable. Successful test runs show that the @var is replicated
69 # properly and that the procedure and trigger insert the correct data on the
70 # slave.
71 #
72 # The test of stored procedure was included for completeness. Replication of stored
73 # procedures was not directly affected by BUG#20141.
74 #
75 # This test was constructed for BUG#20141
76
77 --disable_warnings
78 DROP TABLE IF EXISTS t20;
79 DROP TABLE IF EXISTS t21;
80 DROP PROCEDURE IF EXISTS test.insert;
81 --enable_warnings
82
83 CREATE TABLE t20 (a VARCHAR(20));
84 CREATE TABLE t21 (a VARCHAR(20));
85 DELIMITER |;
86
87 # Create a procedure that uses the @var for flow control
88
89 CREATE PROCEDURE test.insert()
90 BEGIN
91 IF (@VAR)
92 THEN
93 INSERT INTO test.t20 VALUES ('SP_TRUE');
94 ELSE
95 INSERT INTO test.t20 VALUES ('SP_FALSE');
96 END IF;
97 END|
98
99 # Create a trigger that uses the @var for flow control
100
101 CREATE TRIGGER test.insert_bi BEFORE INSERT
102 ON test.t20 FOR EACH ROW
103 BEGIN
104 IF (@VAR)
105 THEN
106 INSERT INTO test.t21 VALUES ('TRIG_TRUE');
107 ELSE
108 INSERT INTO test.t21 VALUES ('TRIG_FALSE');
109 END IF;
110 END|
111 DELIMITER ;|
112
113 sync_slave_with_master;
114 connection master;
115
116 # Set @var and call the procedure, repeat with different values
117
118 SET @VAR=0;
119 CALL test.insert();
120 SET @VAR=1;
121 CALL test.insert();
122
123 --echo Check the tables for correct data
124
125 SELECT * FROM t20;
126 SELECT * FROM t21;
127
128 sync_slave_with_master;
129
130 --echo Check the tables for correct data and it matches master
131
132 SELECT * FROM t20;
133 SELECT * FROM t21;
134 connection master;
135
136 # Cleanup
137
138 DROP TABLE t20;
139 DROP TABLE t21;
140 DROP PROCEDURE test.insert;
141
142 # This test uses a stored function that uses user-defined variables to return data
143 # This test was constructed for BUG#20141
144
145 --disable_warnings
146 DROP TABLE IF EXISTS t1;
147 DROP FUNCTION IF EXISTS test.square;
148 --enable_warnings
149
150 CREATE TABLE t1 (i INT);
151
152 # Create function that returns a value from @var. In this case, the square function
153
154 CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN
155 (@var * @var);
156
157 # Set the @var to different values and insert them into a table
158
159 SET @var = 1;
160 INSERT INTO t1 VALUES (square());
161 SET @var = 2;
162 INSERT INTO t1 VALUES (square());
163 SET @var = 3;
164 INSERT INTO t1 VALUES (square());
165 SET @var = 4;
166 INSERT INTO t1 VALUES (square());
167 SET @var = 5;
168 INSERT INTO t1 VALUES (square());
169
170 --echo Retrieve the values from the table
171
172 SELECT * FROM t1;
173
174 sync_slave_with_master;
175
176 --echo Retrieve the values from the table and verify they are the same as on master
177
178 SELECT * FROM t1;
179
180 connection master;
181
182 # Cleanup
183
184 DROP TABLE t1;
185 DROP FUNCTION test.square;
186
187 # This test uses stored functions that uses user-defined variables to return data
188 # based on the use of @vars inside a function body.
189 # This test was constructed for BUG#14914
190
191 --disable_warnings
192 DROP TABLE IF EXISTS t1;
193 DROP FUNCTION IF EXISTS f1;
194 DROP FUNCTION IF EXISTS f2;
195 --enable_warnings
196
197 CREATE TABLE t1(a int);
198 DELIMITER |;
199
200 # Create a function that simply returns the value of an @var.
201 # Create a function that uses an @var for flow control, creates and uses another
202 # @var and sets its value to a value based on another @var.
203
204 CREATE FUNCTION f1() returns int deterministic BEGIN
205 return @a;
206 END |
207
208 CREATE FUNCTION f2() returns int deterministic BEGIN
209 IF (@b > 0) then
210 SET @c = (@a + @b);
211 else
212 SET @c = (@a - 1);
213 END if;
214 return @c;
215 END |
216 DELIMITER ;|
217
218 sync_slave_with_master;
219 connection master;
220
221 # Set an @var to a value and insert data into a table using the first function.
222 # Set two more @vars to some values and insert data into a table using the second function.
223
224 SET @a=500;
225 INSERT INTO t1 values(f1());
226 SET @b = 125;
227 SET @c = 1;
228 INSERT INTO t1 values(f2());
229
230 --echo Retrieve the values from the table
231
232 sync_slave_with_master;
233 connection master;
234
235 SELECT * from t1;
236
237 connection slave;
238
239 --echo Check the tables for correct data and it matches master
240
241 SELECT * from t1;
242
243 connection master;
244
245 # Cleanup
246
247 DROP TABLE t1;
248 DROP FUNCTION f1;
249 DROP FUNCTION f2;
250
251 # This test uses a function that changes a user-defined variable in its body. This test
252 # will ensure the @vars are replicated when needed and not interrupt the normal execution
253 # of the function on the slave. This also applies to procedures and triggers.
254
255 # This test was constructed for BUG#25167
256
257 --disable_warnings
258 DROP TABLE IF EXISTS t1;
259 DROP TABLE IF EXISTS t2;
260 --enable_warnings
261 CREATE TABLE t1 (i int);
262 CREATE TABLE t2 (k int);
263 DELIMITER |;
264
265 # Create a trigger that inserts data into another table, changes the @var then inserts
266 # another row with the modified value.
267
268 CREATE trigger t1_bi before INSERT on t1 for each row BEGIN
269 INSERT INTO t2 values (@a);
270 SET @a:=42;
271 INSERT INTO t2 values (@a);
272 END |
273 DELIMITER ;|
274
275 sync_slave_with_master;
276 connection master;
277
278 # Set the @var to a value then insert data into first table.
279
280 SET @a:=100;
281 INSERT INTO t1 values (5);
282
283 --echo Check to see that data was inserted correctly in both tables
284
285 SELECT * from t1;
286 SELECT * from t2;
287
288 sync_slave_with_master;
289
290 --echo Check the tables for correct data and it matches master
291
292 SELECT * from t1;
293 SELECT * from t2;
294
295 connection master;
296 drop table t1, t2;
297
298 #
299 # Bug #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements
300 #
301 connection master;
302 create table t1(a int, b int);
303 prepare s1 from 'insert into t1 values (@x:=@x+1, ?)';
304 set @x=1; execute s1 using @x;
305 select * from t1;
306 sync_slave_with_master;
307 connection slave;
308 select * from t1;
309 connection master;
310 drop table t1;
311
312 #
313 # Bug#33851: Passing UNSIGNED param to EXECUTE returns ERROR 1210
314 #
315
316 connection master;
317 create table t1(a int);
318 insert into t1 values (1),(2);
319 prepare s1 from 'insert into t1 select a from t1 limit ?';
320 set @x='1.1';
321 --disable_warnings
322 execute s1 using @x;
323 --enable_warnings
324 select * from t1;
325 sync_slave_with_master;
326 connection slave;
327 select * from t1;
328 connection master;
329 drop table t1;
330
331 --echo End of 5.0 tests.
332
333 # This test uses a stored function that uses user-defined variables to return data
334 # The test ensures the value of the user-defined variable is replicated correctly
335 # and in the correct order of assignment.
336
337 # This test was constructed for BUG#20141
338
339 --disable_warnings
340 DROP FUNCTION IF EXISTS f1;
341 DROP FUNCTION IF EXISTS f2;
342 --enable_warnings
343
344 CREATE TABLE t1 (i INT);
345
346 # Create two functions. One simply returns the user-defined variable. The other
347 # returns a value based on the user-defined variable.
348
349 CREATE FUNCTION f1() RETURNS INT RETURN @a; DELIMITER |; CREATE
350 FUNCTION f2() RETURNS INT BEGIN
351 INSERT INTO t1 VALUES (10 + @a);
352 RETURN 0;
353 END|
354 DELIMITER ;|
355
356 sync_slave_with_master;
357 connection master;
358
359 # Set the variable and execute the functions.
360
361 SET @a:=123;
362 SELECT f1(), f2();
363
364 --echo Check to see that data was inserted correctly
365
366 INSERT INTO t1 VALUES(f1());
367 SELECT * FROM t1;
368
369 sync_slave_with_master;
370
371 --echo Check the table for correct data and it matches master
372
373 SELECT * FROM t1;
374
375 connection master;
376
377 # Cleanup
378
379 DROP FUNCTION f1;
380 DROP FUNCTION f2;
381 DROP TABLE t1;
382
383 sync_slave_with_master;
384
385 --source include/rpl_end.inc