Debian Codegraph @

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

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