Debian Codegraph @

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