Debian Codegraph @

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