Debian Codegraph @

mysql-test/suite/engines/funcs/t/rpl_trigger.test

raw
1 #
2 # Test of triggers with replication
3 # Adding statement include due to Bug 12574
4 # TODO: Remove statement include once 12574 is patched
5 --source include/have_binlog_format_mixed_or_statement.inc
6 --source include/master-slave.inc
7
8 connection slave;
9 --source include/stop_slave.inc
10 CHANGE MASTER TO MASTER_USE_GTID=NO;
11 --source include/start_slave.inc
12 --connection master
13
14 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
15
16 #
17 # #12482: Triggers has side effects with auto_increment values
18 #
19
20 create table t1 (a int auto_increment, primary key (a), b int, rand_value double not null);
21 create table t2 (a int auto_increment, primary key (a), b int);
22 create table t3 (a int auto_increment, primary key (a), name varchar(64) not null, old_a int, old_b int, rand_value double not null);
23
24 delimiter |;
25 create trigger t1 before insert on t1 for each row
26 begin
27 insert into t3 values (NULL, "t1", new.a, new.b, rand());
28 end|
29
30 create trigger t2 after insert on t2 for each row
31 begin
32 insert into t3 values (NULL, "t2", new.a, new.b, rand());
33 end|
34 delimiter ;|
35
36 insert into t3 values(100,"log",0,0,0);
37
38 # Ensure we always have same random numbers
39 SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
40
41 # Emulate that we have rows 2-9 deleted on the slave
42 --disable_warnings
43 insert into t1 values(1,1,rand()),(NULL,2,rand());
44 insert into t2 (b) values(last_insert_id());
45 insert into t2 values(3,0);
46 insert into t2 values(NULL,0);
47 insert into t2 values(NULL,0);
48 insert into t2 values(500,0);
49 --enable_warnings
50
51 select a,b, truncate(rand_value,4) from t1;
52 select * from t2;
53 select a,name, old_a, old_b, truncate(rand_value,4) from t3;
54 --sync_slave_with_master
55 select a,b, truncate(rand_value,4) from t1;
56 select * from t2;
57 select a,name, old_a, old_b, truncate(rand_value,4) from t3;
58 connection master;
59 drop table t1,t2,t3;
60
61 #
62 # #12480: NOW() is not constant in a trigger
63 # #12481: Using NOW() in a stored function breaks statement based replication
64 #
65
66 # Start by getting a lock on 'bug12480' to be able to use get_lock() as sleep()
67 connect (con2,localhost,root,,);
68 connection con2;
69 select get_lock("bug12480",2);
70 connection default;
71
72 create table t1 (a datetime,b datetime, c datetime);
73 --disable_warnings
74 drop function if exists bug12480;
75 --enable_warnings
76
77 delimiter |;
78
79 create function bug12480() returns datetime
80 begin
81 set @a=get_lock("bug12480",2);
82 return now();
83 end|
84
85 create trigger t1_first before insert on t1
86 for each row begin
87 set @a=get_lock("bug12480",2);
88 set new.b= now();
89 set new.c= bug12480();
90 end
91 |
92
93 delimiter ;|
94 --disable_warnings
95 insert into t1 set a = now();
96 --enable_warnings
97 select a=b && a=c from t1;
98 let $time=`select a from t1`;
99
100 # Check that definer attribute is replicated properly:
101 # - dump definers on the master;
102 # - wait for the slave to synchronize with the master;
103 # - dump definers on the slave;
104
105 SELECT routine_name, definer
106 FROM information_schema.routines
107 WHERE routine_name = 'bug12480';
108
109 SELECT trigger_name, definer
110 FROM information_schema.triggers
111 WHERE trigger_name = 't1_first';
112
113 --sync_slave_with_master
114
115 # XXX: Definers of stored procedures and functions are not replicated. WL#2897
116 # (Complete definer support in the stored routines) addresses this issue. So,
117 # the result file is expected to be changed after implementation of this WL
118 # item.
119
120 SELECT routine_name, definer
121 FROM information_schema.routines
122 WHERE routine_name = 'bug12480';
123
124 SELECT trigger_name, definer
125 FROM information_schema.triggers
126 WHERE trigger_name = 't1_first';
127
128 select a=b && a=c from t1;
129 --disable_query_log
130 eval select a='$time' as 'test' from t1;
131 --enable_query_log
132
133 connection master;
134 disconnect con2;
135
136 truncate table t1;
137 drop trigger t1_first;
138 --disable_warnings
139 insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now());
140 --enable_warnings
141 select a=b && a=c from t1;
142
143 drop function bug12480;
144 drop table t1;
145
146 #
147 # #14614: Replication of tables with trigger generates error message if databases is changed
148 # Note. The error message is emitted by _myfree() using fprintf() to the stderr
149 # and because of that does not fall into the .result file.
150 #
151
152 create table t1 (i int);
153 create table t2 (i int);
154
155 delimiter |;
156 create trigger tr1 before insert on t1 for each row
157 begin
158 insert into t2 values (1);
159 end|
160 delimiter ;|
161
162 create database other;
163 use other;
164 insert into test.t1 values (1);
165
166 --sync_slave_with_master
167
168 connection master;
169 use test;
170 drop table t1,t2;
171 drop database other;
172
173
174 #
175 # Test specific triggers including SELECT into var with replication
176 # BUG#13227:
177 # slave performs an update to the replicatable table, t1,
178 # and modifies its local data, t3, by mean of its local trigger that uses
179 # another local table t2.
180 # Expected values are commented into queries.
181 #
182 # Body of the test executes in a loop since the problem occurred randomly.
183 #
184
185 let $max_rows=5;
186 let $rnd=10;
187
188 --echo test case for BUG#13227
189 while ($rnd)
190 {
191 --echo -------------------
192 echo $rnd;
193 --echo -------------------
194
195 ### SETUP
196
197 --disable_warnings
198 connection master;
199 eval drop table if exists t1$rnd;
200 connection slave;
201 eval drop table if exists t2$rnd,t3$rnd;
202 --enable_warnings
203
204 connection master;
205 eval create table t1$rnd (f1 int) /* 2 replicate */;
206 let $i=$max_rows;
207 while ($i)
208 {
209 eval insert into t1$rnd values (-$i);
210 dec $i;
211 }
212
213 --sync_slave_with_master
214 #connection slave;
215 eval select * from t1$rnd;
216 delimiter |;
217 eval create trigger trg1$rnd before update on t1$rnd /* slave local */
218 for each row
219 begin
220 DECLARE r integer;
221 SELECT f2 INTO r FROM t2$rnd where f1=NEW.f1;
222 INSERT INTO t3$rnd values (r);
223 end|
224 delimiter ;|
225 eval create table t2$rnd (f1 int, f2 int) /* slave local */;
226 eval create table t3$rnd (f3 int) /* slave local */;
227 let $i=$max_rows;
228 while ($i)
229 {
230 eval insert into t2$rnd values ($i, $i*100);
231 dec $i;
232 }
233
234 ### Test
235
236 #connection slave;
237
238 # trigger works as specified when updates from slave
239 eval select * from t2$rnd;
240 eval UPDATE t1$rnd SET f1=$max_rows where f1=-$max_rows;
241 eval SELECT * from t1$rnd /* must be f1 $max_rows, 1 - $max_rows 2 - $max_rows ... -1 */;
242 eval SELECT * from t3$rnd /* must be f3 $max_rows*100 */;
243
244 connection master;
245 let $i=$max_rows;
246 while ($i)
247 {
248 eval UPDATE t1$rnd SET f1=$i where f1=-$i;
249 dec $i;
250 }
251
252 --sync_slave_with_master
253 #connection slave;
254 eval SELECT * from t1$rnd /* must be f1 $max_rows ... 1 */;
255 eval SELECT * from t3$rnd /* must be f3 $max_rows * 100 ... 100 */;
256
257 ### CLEANUP
258 #connection slave;
259 eval drop trigger trg1$rnd;
260 eval drop table t2$rnd,t3$rnd;
261
262 connection master;
263 eval drop table t1$rnd;
264 --sync_slave_with_master
265 connection master;
266
267 dec $rnd;
268 }
269
270
271 #
272 # BUG#16266: Definer is not fully qualified error during replication.
273 #
274 # The idea of this test is to emulate replication of a trigger from the old
275 # master (master w/o "DEFINER in triggers" support) to the new slave and check
276 # that:
277 # 1. the trigger on the slave will be replicated w/o errors;
278 # 2. the trigger on the slave will be non-SUID (will have no DEFINER);
279 # 3. the trigger can be activated later on the slave w/o errors.
280 #
281 # In order to emulate this kind of replication, we make the slave playing the binlog,
282 # recorded by 5.0.16 master. This binlog contains the following statements:
283 # CREATE TABLE t1(c INT);
284 # CREATE TABLE t2(s CHAR(200));
285 # CREATE TRIGGER trg1 AFTER INSERT ON t1
286 # FOR EACH ROW
287 # INSERT INTO t2 VALUES(CURRENT_USER());
288 # INSERT INTO t1 VALUES(1);
289 #
290
291 # 1. Check that the trigger's replication is succeeded.
292
293 # Stop the slave.
294
295 connection slave;
296 --source include/stop_slave.inc
297
298 # Replace master's binlog.
299
300 connection master;
301 let $MYSQLD_DATADIR= `select @@datadir`;
302 FLUSH LOGS;
303
304 # Stop master server
305 --let $rpl_server_number= 1
306 --source include/rpl_stop_server.inc
307
308 # Replace binlog
309 remove_file $MYSQLD_DATADIR/master-bin.000001;
310 copy_file $MYSQL_TEST_DIR/std_data/bug16266.000001 $MYSQLD_DATADIR/master-bin.000001;
311
312 --let $rpl_server_number= 1
313 --source include/rpl_start_server.inc
314
315 let $binlog_version= query_get_value(SHOW BINLOG EVENTS, Info, 1);
316
317
318 # Make the slave to replay the new binlog.
319 --echo --> Master binlog: $binlog_version
320
321 # Make the slave to replay the new binlog.
322
323 connection slave;
324 --let $master_use_gtid_option= No
325 --source include/reset_slave.inc
326 --source include/start_slave.inc
327
328 SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0;
329
330 # Check that the replication succeeded.
331 SHOW TABLES LIKE 't_';
332 --replace_column 6 #
333 SHOW TRIGGERS;
334 SELECT * FROM t1;
335 SELECT * FROM t2;
336
337 # 2. Check that the trigger is non-SUID on the slave;
338 # 3. Check that the trigger can be activated on the slave.
339 --disable_warnings
340 INSERT INTO t1 VALUES(2);
341 --enable_warnings
342 SELECT * FROM t1;
343 SELECT * FROM t2;
344
345 # That's all, cleanup.
346
347 DROP TRIGGER trg1;
348 DROP TABLE t1;
349 DROP TABLE t2;
350
351 --source include/stop_slave.inc
352 --source include/reset_slave.inc
353
354 # The master should be clean.
355
356 connection master;
357 SHOW TABLES LIKE 't_';
358 SHOW TRIGGERS;
359
360 RESET MASTER;
361
362 # Restart slave.
363
364 connection slave;
365 --source include/start_slave.inc
366
367
368 #
369 # BUG#20438: CREATE statements for views, stored routines and triggers can be
370 # not replicable.
371 #
372
373 --echo
374 --echo ---> Test for BUG#20438
375
376 # Prepare environment.
377
378 --echo
379 --echo ---> Preparing environment...
380 --connection master
381
382 --disable_warnings
383 DROP TABLE IF EXISTS t1;
384 DROP TABLE IF EXISTS t2;
385 --enable_warnings
386
387 --echo
388 --echo ---> Synchronizing slave with master...
389
390 --sync_slave_with_master
391
392 --echo
393 --connection master
394
395 # Test.
396
397 --echo
398 --echo ---> Creating objects...
399
400 CREATE TABLE t1(c INT);
401 CREATE TABLE t2(c INT);
402
403 /*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
404 FOR EACH ROW
405 INSERT INTO t2 VALUES(NEW.c * 10) */;
406
407 --echo
408 --echo ---> Inserting value...
409
410 INSERT INTO t1 VALUES(1);
411
412 --echo
413 --echo ---> Checking on master...
414
415 SELECT * FROM t1;
416 SELECT * FROM t2;
417
418 --echo
419 --echo ---> Synchronizing slave with master...
420
421 --sync_slave_with_master
422
423 --echo
424 --echo ---> Checking on slave...
425
426 SELECT * FROM t1;
427 SELECT * FROM t2;
428
429 # Cleanup.
430
431 --echo
432 --connection master
433
434 --echo
435 --echo ---> Cleaning up...
436
437 DROP TABLE t1;
438 DROP TABLE t2;
439
440 --sync_slave_with_master
441 --connection master
442
443 #
444 # BUG#23703: DROP TRIGGER needs an IF EXISTS
445 #
446
447 connection master;
448
449 --disable_warnings
450 drop table if exists t1;
451 --enable_warnings
452
453 create table t1(a int, b varchar(50));
454
455 -- error ER_TRG_DOES_NOT_EXIST
456 drop trigger not_a_trigger;
457
458 drop trigger if exists not_a_trigger;
459
460 create trigger t1_bi before insert on t1
461 for each row set NEW.b := "In trigger t1_bi";
462
463 insert into t1 values (1, "a");
464 drop trigger if exists t1_bi;
465 insert into t1 values (2, "b");
466 drop trigger if exists t1_bi;
467 insert into t1 values (3, "c");
468
469 select * from t1;
470
471 --sync_slave_with_master
472
473 select * from t1;
474
475 connection master;
476
477 drop table if exists t1,t11;
478 #
479 # End of tests
480 #
481 --sync_slave_with_master
482 --source include/rpl_end.inc