Debian Codegraph @

mysql-test/suite/rpl/r/rpl_trigger.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection slave;
4 include/stop_slave.inc
5 CHANGE MASTER TO MASTER_USE_GTID=NO;
6 include/start_slave.inc
7 connection master;
8 DROP TABLE IF EXISTS t1;
9 DROP TABLE IF EXISTS t2;
10 DROP TABLE IF EXISTS t3;
11 create table t1 (a int auto_increment, primary key (a), b int, rand_value double not null);
12 create table t2 (a int auto_increment, primary key (a), b int) engine=innodb;
13 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);
14 create trigger t1 before insert on t1 for each row
15 begin
16 insert into t3 values (NULL, "t1", new.a, new.b, rand());
17 end|
18 create trigger t2 after insert on t2 for each row
19 begin
20 insert into t3 values (NULL, "t2", new.a, new.b, rand());
21 end|
22 insert into t3 values(100,"log",0,0,0);
23 SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
24 insert into t1 values(1,1,rand()),(NULL,2,rand());
25 insert into t2 (b) values(last_insert_id());
26 insert into t2 values(3,0),(NULL,0);
27 insert into t2 values(NULL,0),(500,0);
28 select a,b, truncate(rand_value,4) from t1;
29 a b truncate(rand_value,4)
30 1 1 0.4320
31 2 2 0.3055
32 select * from t2;
33 a b
34 1 2
35 3 0
36 4 0
37 6 0
38 500 0
39 select a,name, old_a, old_b, truncate(rand_value,4) from t3;
40 a name old_a old_b truncate(rand_value,4)
41 100 log 0 0 0.0000
42 101 t1 1 1 0.3203
43 102 t1 NULL 2 0.5666
44 103 t2 1 2 0.9164
45 104 t2 3 0 0.8826
46 105 t2 4 0 0.6635
47 106 t2 6 0 0.6699
48 107 t2 500 0 0.3593
49 connection slave;
50 select a,b, truncate(rand_value,4) from t1;
51 a b truncate(rand_value,4)
52 1 1 0.4320
53 2 2 0.3055
54 select * from t2;
55 a b
56 1 2
57 3 0
58 4 0
59 6 0
60 500 0
61 select a,name, old_a, old_b, truncate(rand_value,4) from t3;
62 a name old_a old_b truncate(rand_value,4)
63 100 log 0 0 0.0000
64 101 t1 1 1 0.3203
65 102 t1 NULL 2 0.5666
66 103 t2 1 2 0.9164
67 104 t2 3 0 0.8826
68 105 t2 4 0 0.6635
69 106 t2 6 0 0.6699
70 107 t2 500 0 0.3593
71 connection master;
72 drop table t1,t2,t3;
73 connect con2,localhost,root,,;
74 connection con2;
75 select get_lock("bug12480",2);
76 get_lock("bug12480",2)
77 1
78 connection default;
79 create table t1 (a datetime,b datetime, c datetime);
80 drop function if exists bug12480;
81 create function bug12480() returns datetime
82 begin
83 set @a=get_lock("bug12480",2);
84 return now();
85 end|
86 create trigger t1_first before insert on t1
87 for each row begin
88 set @a=get_lock("bug12480",2);
89 set new.b= now();
90 set new.c= bug12480();
91 end
92 |
93 insert into t1 set a = now();
94 select a=b && a=c from t1;
95 a=b && a=c
96 1
97 SELECT routine_name, definer
98 FROM information_schema.routines
99 WHERE routine_name = 'bug12480';
100 routine_name definer
101 bug12480 root@localhost
102 SELECT trigger_name, definer
103 FROM information_schema.triggers
104 WHERE trigger_name = 't1_first';
105 trigger_name definer
106 t1_first root@localhost
107 connection slave;
108 SELECT routine_name, definer
109 FROM information_schema.routines
110 WHERE routine_name = 'bug12480';
111 routine_name definer
112 bug12480 root@localhost
113 SELECT trigger_name, definer
114 FROM information_schema.triggers
115 WHERE trigger_name = 't1_first';
116 trigger_name definer
117 t1_first root@localhost
118 select a=b && a=c from t1;
119 a=b && a=c
120 1
121 test
122 1
123 connection master;
124 disconnect con2;
125 truncate table t1;
126 drop trigger t1_first;
127 insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now());
128 select a=b && a=c from t1;
129 a=b && a=c
130 1
131 1
132 1
133 drop function bug12480;
134 drop table t1;
135 create table t1 (i int);
136 create table t2 (i int);
137 create trigger tr1 before insert on t1 for each row
138 begin
139 insert into t2 values (1);
140 end|
141 create database other;
142 use other;
143 insert into test.t1 values (1);
144 connection slave;
145 connection master;
146 use test;
147 drop table t1,t2;
148 drop database other;
149 test case for BUG#13227
150 -------------------
151 10
152 -------------------
153 connection master;
154 drop table if exists t110;
155 connection slave;
156 drop table if exists t210,t310;
157 connection master;
158 create table t110 (f1 int) /* 2 replicate */;
159 insert into t110 values (-5);
160 insert into t110 values (-4);
161 insert into t110 values (-3);
162 insert into t110 values (-2);
163 insert into t110 values (-1);
164 connection slave;
165 select * from t110;
166 f1
167 -5
168 -4
169 -3
170 -2
171 -1
172 create trigger trg110 before update on t110 /* slave local */
173 for each row
174 begin
175 DECLARE r integer;
176 SELECT f2 INTO r FROM t210 where f1=NEW.f1;
177 INSERT INTO t310 values (r);
178 end|
179 create table t210 (f1 int, f2 int) /* slave local */;
180 create table t310 (f3 int) /* slave local */;
181 insert into t210 values (5, 5*100);
182 insert into t210 values (4, 4*100);
183 insert into t210 values (3, 3*100);
184 insert into t210 values (2, 2*100);
185 insert into t210 values (1, 1*100);
186 select * from t210;
187 f1 f2
188 5 500
189 4 400
190 3 300
191 2 200
192 1 100
193 UPDATE t110 SET f1=5 where f1=-5;
194 SELECT * from t110 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
195 f1
196 5
197 -4
198 -3
199 -2
200 -1
201 SELECT * from t310 /* must be f3 5*100 */;
202 f3
203 500
204 connection master;
205 UPDATE t110 SET f1=5 where f1=-5;
206 UPDATE t110 SET f1=4 where f1=-4;
207 UPDATE t110 SET f1=3 where f1=-3;
208 UPDATE t110 SET f1=2 where f1=-2;
209 UPDATE t110 SET f1=1 where f1=-1;
210 connection slave;
211 SELECT * from t110 /* must be f1 5 ... 1 */;
212 f1
213 5
214 4
215 3
216 2
217 1
218 SELECT * from t310 /* must be f3 5 * 100 ... 100 */;
219 f3
220 500
221 400
222 300
223 200
224 100
225 drop trigger trg110;
226 drop table t210,t310;
227 connection master;
228 drop table t110;
229 connection slave;
230 connection master;
231 -------------------
232 9
233 -------------------
234 connection master;
235 drop table if exists t19;
236 connection slave;
237 drop table if exists t29,t39;
238 connection master;
239 create table t19 (f1 int) /* 2 replicate */;
240 insert into t19 values (-5);
241 insert into t19 values (-4);
242 insert into t19 values (-3);
243 insert into t19 values (-2);
244 insert into t19 values (-1);
245 connection slave;
246 select * from t19;
247 f1
248 -5
249 -4
250 -3
251 -2
252 -1
253 create trigger trg19 before update on t19 /* slave local */
254 for each row
255 begin
256 DECLARE r integer;
257 SELECT f2 INTO r FROM t29 where f1=NEW.f1;
258 INSERT INTO t39 values (r);
259 end|
260 create table t29 (f1 int, f2 int) /* slave local */;
261 create table t39 (f3 int) /* slave local */;
262 insert into t29 values (5, 5*100);
263 insert into t29 values (4, 4*100);
264 insert into t29 values (3, 3*100);
265 insert into t29 values (2, 2*100);
266 insert into t29 values (1, 1*100);
267 select * from t29;
268 f1 f2
269 5 500
270 4 400
271 3 300
272 2 200
273 1 100
274 UPDATE t19 SET f1=5 where f1=-5;
275 SELECT * from t19 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
276 f1
277 5
278 -4
279 -3
280 -2
281 -1
282 SELECT * from t39 /* must be f3 5*100 */;
283 f3
284 500
285 connection master;
286 UPDATE t19 SET f1=5 where f1=-5;
287 UPDATE t19 SET f1=4 where f1=-4;
288 UPDATE t19 SET f1=3 where f1=-3;
289 UPDATE t19 SET f1=2 where f1=-2;
290 UPDATE t19 SET f1=1 where f1=-1;
291 connection slave;
292 SELECT * from t19 /* must be f1 5 ... 1 */;
293 f1
294 5
295 4
296 3
297 2
298 1
299 SELECT * from t39 /* must be f3 5 * 100 ... 100 */;
300 f3
301 500
302 400
303 300
304 200
305 100
306 drop trigger trg19;
307 drop table t29,t39;
308 connection master;
309 drop table t19;
310 connection slave;
311 connection master;
312 -------------------
313 8
314 -------------------
315 connection master;
316 drop table if exists t18;
317 connection slave;
318 drop table if exists t28,t38;
319 connection master;
320 create table t18 (f1 int) /* 2 replicate */;
321 insert into t18 values (-5);
322 insert into t18 values (-4);
323 insert into t18 values (-3);
324 insert into t18 values (-2);
325 insert into t18 values (-1);
326 connection slave;
327 select * from t18;
328 f1
329 -5
330 -4
331 -3
332 -2
333 -1
334 create trigger trg18 before update on t18 /* slave local */
335 for each row
336 begin
337 DECLARE r integer;
338 SELECT f2 INTO r FROM t28 where f1=NEW.f1;
339 INSERT INTO t38 values (r);
340 end|
341 create table t28 (f1 int, f2 int) /* slave local */;
342 create table t38 (f3 int) /* slave local */;
343 insert into t28 values (5, 5*100);
344 insert into t28 values (4, 4*100);
345 insert into t28 values (3, 3*100);
346 insert into t28 values (2, 2*100);
347 insert into t28 values (1, 1*100);
348 select * from t28;
349 f1 f2
350 5 500
351 4 400
352 3 300
353 2 200
354 1 100
355 UPDATE t18 SET f1=5 where f1=-5;
356 SELECT * from t18 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
357 f1
358 5
359 -4
360 -3
361 -2
362 -1
363 SELECT * from t38 /* must be f3 5*100 */;
364 f3
365 500
366 connection master;
367 UPDATE t18 SET f1=5 where f1=-5;
368 UPDATE t18 SET f1=4 where f1=-4;
369 UPDATE t18 SET f1=3 where f1=-3;
370 UPDATE t18 SET f1=2 where f1=-2;
371 UPDATE t18 SET f1=1 where f1=-1;
372 connection slave;
373 SELECT * from t18 /* must be f1 5 ... 1 */;
374 f1
375 5
376 4
377 3
378 2
379 1
380 SELECT * from t38 /* must be f3 5 * 100 ... 100 */;
381 f3
382 500
383 400
384 300
385 200
386 100
387 drop trigger trg18;
388 drop table t28,t38;
389 connection master;
390 drop table t18;
391 connection slave;
392 connection master;
393 -------------------
394 7
395 -------------------
396 connection master;
397 drop table if exists t17;
398 connection slave;
399 drop table if exists t27,t37;
400 connection master;
401 create table t17 (f1 int) /* 2 replicate */;
402 insert into t17 values (-5);
403 insert into t17 values (-4);
404 insert into t17 values (-3);
405 insert into t17 values (-2);
406 insert into t17 values (-1);
407 connection slave;
408 select * from t17;
409 f1
410 -5
411 -4
412 -3
413 -2
414 -1
415 create trigger trg17 before update on t17 /* slave local */
416 for each row
417 begin
418 DECLARE r integer;
419 SELECT f2 INTO r FROM t27 where f1=NEW.f1;
420 INSERT INTO t37 values (r);
421 end|
422 create table t27 (f1 int, f2 int) /* slave local */;
423 create table t37 (f3 int) /* slave local */;
424 insert into t27 values (5, 5*100);
425 insert into t27 values (4, 4*100);
426 insert into t27 values (3, 3*100);
427 insert into t27 values (2, 2*100);
428 insert into t27 values (1, 1*100);
429 select * from t27;
430 f1 f2
431 5 500
432 4 400
433 3 300
434 2 200
435 1 100
436 UPDATE t17 SET f1=5 where f1=-5;
437 SELECT * from t17 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
438 f1
439 5
440 -4
441 -3
442 -2
443 -1
444 SELECT * from t37 /* must be f3 5*100 */;
445 f3
446 500
447 connection master;
448 UPDATE t17 SET f1=5 where f1=-5;
449 UPDATE t17 SET f1=4 where f1=-4;
450 UPDATE t17 SET f1=3 where f1=-3;
451 UPDATE t17 SET f1=2 where f1=-2;
452 UPDATE t17 SET f1=1 where f1=-1;
453 connection slave;
454 SELECT * from t17 /* must be f1 5 ... 1 */;
455 f1
456 5
457 4
458 3
459 2
460 1
461 SELECT * from t37 /* must be f3 5 * 100 ... 100 */;
462 f3
463 500
464 400
465 300
466 200
467 100
468 drop trigger trg17;
469 drop table t27,t37;
470 connection master;
471 drop table t17;
472 connection slave;
473 connection master;
474 -------------------
475 6
476 -------------------
477 connection master;
478 drop table if exists t16;
479 connection slave;
480 drop table if exists t26,t36;
481 connection master;
482 create table t16 (f1 int) /* 2 replicate */;
483 insert into t16 values (-5);
484 insert into t16 values (-4);
485 insert into t16 values (-3);
486 insert into t16 values (-2);
487 insert into t16 values (-1);
488 connection slave;
489 select * from t16;
490 f1
491 -5
492 -4
493 -3
494 -2
495 -1
496 create trigger trg16 before update on t16 /* slave local */
497 for each row
498 begin
499 DECLARE r integer;
500 SELECT f2 INTO r FROM t26 where f1=NEW.f1;
501 INSERT INTO t36 values (r);
502 end|
503 create table t26 (f1 int, f2 int) /* slave local */;
504 create table t36 (f3 int) /* slave local */;
505 insert into t26 values (5, 5*100);
506 insert into t26 values (4, 4*100);
507 insert into t26 values (3, 3*100);
508 insert into t26 values (2, 2*100);
509 insert into t26 values (1, 1*100);
510 select * from t26;
511 f1 f2
512 5 500
513 4 400
514 3 300
515 2 200
516 1 100
517 UPDATE t16 SET f1=5 where f1=-5;
518 SELECT * from t16 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
519 f1
520 5
521 -4
522 -3
523 -2
524 -1
525 SELECT * from t36 /* must be f3 5*100 */;
526 f3
527 500
528 connection master;
529 UPDATE t16 SET f1=5 where f1=-5;
530 UPDATE t16 SET f1=4 where f1=-4;
531 UPDATE t16 SET f1=3 where f1=-3;
532 UPDATE t16 SET f1=2 where f1=-2;
533 UPDATE t16 SET f1=1 where f1=-1;
534 connection slave;
535 SELECT * from t16 /* must be f1 5 ... 1 */;
536 f1
537 5
538 4
539 3
540 2
541 1
542 SELECT * from t36 /* must be f3 5 * 100 ... 100 */;
543 f3
544 500
545 400
546 300
547 200
548 100
549 drop trigger trg16;
550 drop table t26,t36;
551 connection master;
552 drop table t16;
553 connection slave;
554 connection master;
555 -------------------
556 5
557 -------------------
558 connection master;
559 drop table if exists t15;
560 connection slave;
561 drop table if exists t25,t35;
562 connection master;
563 create table t15 (f1 int) /* 2 replicate */;
564 insert into t15 values (-5);
565 insert into t15 values (-4);
566 insert into t15 values (-3);
567 insert into t15 values (-2);
568 insert into t15 values (-1);
569 connection slave;
570 select * from t15;
571 f1
572 -5
573 -4
574 -3
575 -2
576 -1
577 create trigger trg15 before update on t15 /* slave local */
578 for each row
579 begin
580 DECLARE r integer;
581 SELECT f2 INTO r FROM t25 where f1=NEW.f1;
582 INSERT INTO t35 values (r);
583 end|
584 create table t25 (f1 int, f2 int) /* slave local */;
585 create table t35 (f3 int) /* slave local */;
586 insert into t25 values (5, 5*100);
587 insert into t25 values (4, 4*100);
588 insert into t25 values (3, 3*100);
589 insert into t25 values (2, 2*100);
590 insert into t25 values (1, 1*100);
591 select * from t25;
592 f1 f2
593 5 500
594 4 400
595 3 300
596 2 200
597 1 100
598 UPDATE t15 SET f1=5 where f1=-5;
599 SELECT * from t15 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
600 f1
601 5
602 -4
603 -3
604 -2
605 -1
606 SELECT * from t35 /* must be f3 5*100 */;
607 f3
608 500
609 connection master;
610 UPDATE t15 SET f1=5 where f1=-5;
611 UPDATE t15 SET f1=4 where f1=-4;
612 UPDATE t15 SET f1=3 where f1=-3;
613 UPDATE t15 SET f1=2 where f1=-2;
614 UPDATE t15 SET f1=1 where f1=-1;
615 connection slave;
616 SELECT * from t15 /* must be f1 5 ... 1 */;
617 f1
618 5
619 4
620 3
621 2
622 1
623 SELECT * from t35 /* must be f3 5 * 100 ... 100 */;
624 f3
625 500
626 400
627 300
628 200
629 100
630 drop trigger trg15;
631 drop table t25,t35;
632 connection master;
633 drop table t15;
634 connection slave;
635 connection master;
636 -------------------
637 4
638 -------------------
639 connection master;
640 drop table if exists t14;
641 connection slave;
642 drop table if exists t24,t34;
643 connection master;
644 create table t14 (f1 int) /* 2 replicate */;
645 insert into t14 values (-5);
646 insert into t14 values (-4);
647 insert into t14 values (-3);
648 insert into t14 values (-2);
649 insert into t14 values (-1);
650 connection slave;
651 select * from t14;
652 f1
653 -5
654 -4
655 -3
656 -2
657 -1
658 create trigger trg14 before update on t14 /* slave local */
659 for each row
660 begin
661 DECLARE r integer;
662 SELECT f2 INTO r FROM t24 where f1=NEW.f1;
663 INSERT INTO t34 values (r);
664 end|
665 create table t24 (f1 int, f2 int) /* slave local */;
666 create table t34 (f3 int) /* slave local */;
667 insert into t24 values (5, 5*100);
668 insert into t24 values (4, 4*100);
669 insert into t24 values (3, 3*100);
670 insert into t24 values (2, 2*100);
671 insert into t24 values (1, 1*100);
672 select * from t24;
673 f1 f2
674 5 500
675 4 400
676 3 300
677 2 200
678 1 100
679 UPDATE t14 SET f1=5 where f1=-5;
680 SELECT * from t14 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
681 f1
682 5
683 -4
684 -3
685 -2
686 -1
687 SELECT * from t34 /* must be f3 5*100 */;
688 f3
689 500
690 connection master;
691 UPDATE t14 SET f1=5 where f1=-5;
692 UPDATE t14 SET f1=4 where f1=-4;
693 UPDATE t14 SET f1=3 where f1=-3;
694 UPDATE t14 SET f1=2 where f1=-2;
695 UPDATE t14 SET f1=1 where f1=-1;
696 connection slave;
697 SELECT * from t14 /* must be f1 5 ... 1 */;
698 f1
699 5
700 4
701 3
702 2
703 1
704 SELECT * from t34 /* must be f3 5 * 100 ... 100 */;
705 f3
706 500
707 400
708 300
709 200
710 100
711 drop trigger trg14;
712 drop table t24,t34;
713 connection master;
714 drop table t14;
715 connection slave;
716 connection master;
717 -------------------
718 3
719 -------------------
720 connection master;
721 drop table if exists t13;
722 connection slave;
723 drop table if exists t23,t33;
724 connection master;
725 create table t13 (f1 int) /* 2 replicate */;
726 insert into t13 values (-5);
727 insert into t13 values (-4);
728 insert into t13 values (-3);
729 insert into t13 values (-2);
730 insert into t13 values (-1);
731 connection slave;
732 select * from t13;
733 f1
734 -5
735 -4
736 -3
737 -2
738 -1
739 create trigger trg13 before update on t13 /* slave local */
740 for each row
741 begin
742 DECLARE r integer;
743 SELECT f2 INTO r FROM t23 where f1=NEW.f1;
744 INSERT INTO t33 values (r);
745 end|
746 create table t23 (f1 int, f2 int) /* slave local */;
747 create table t33 (f3 int) /* slave local */;
748 insert into t23 values (5, 5*100);
749 insert into t23 values (4, 4*100);
750 insert into t23 values (3, 3*100);
751 insert into t23 values (2, 2*100);
752 insert into t23 values (1, 1*100);
753 select * from t23;
754 f1 f2
755 5 500
756 4 400
757 3 300
758 2 200
759 1 100
760 UPDATE t13 SET f1=5 where f1=-5;
761 SELECT * from t13 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
762 f1
763 5
764 -4
765 -3
766 -2
767 -1
768 SELECT * from t33 /* must be f3 5*100 */;
769 f3
770 500
771 connection master;
772 UPDATE t13 SET f1=5 where f1=-5;
773 UPDATE t13 SET f1=4 where f1=-4;
774 UPDATE t13 SET f1=3 where f1=-3;
775 UPDATE t13 SET f1=2 where f1=-2;
776 UPDATE t13 SET f1=1 where f1=-1;
777 connection slave;
778 SELECT * from t13 /* must be f1 5 ... 1 */;
779 f1
780 5
781 4
782 3
783 2
784 1
785 SELECT * from t33 /* must be f3 5 * 100 ... 100 */;
786 f3
787 500
788 400
789 300
790 200
791 100
792 drop trigger trg13;
793 drop table t23,t33;
794 connection master;
795 drop table t13;
796 connection slave;
797 connection master;
798 -------------------
799 2
800 -------------------
801 connection master;
802 drop table if exists t12;
803 connection slave;
804 drop table if exists t22,t32;
805 connection master;
806 create table t12 (f1 int) /* 2 replicate */;
807 insert into t12 values (-5);
808 insert into t12 values (-4);
809 insert into t12 values (-3);
810 insert into t12 values (-2);
811 insert into t12 values (-1);
812 connection slave;
813 select * from t12;
814 f1
815 -5
816 -4
817 -3
818 -2
819 -1
820 create trigger trg12 before update on t12 /* slave local */
821 for each row
822 begin
823 DECLARE r integer;
824 SELECT f2 INTO r FROM t22 where f1=NEW.f1;
825 INSERT INTO t32 values (r);
826 end|
827 create table t22 (f1 int, f2 int) /* slave local */;
828 create table t32 (f3 int) /* slave local */;
829 insert into t22 values (5, 5*100);
830 insert into t22 values (4, 4*100);
831 insert into t22 values (3, 3*100);
832 insert into t22 values (2, 2*100);
833 insert into t22 values (1, 1*100);
834 select * from t22;
835 f1 f2
836 5 500
837 4 400
838 3 300
839 2 200
840 1 100
841 UPDATE t12 SET f1=5 where f1=-5;
842 SELECT * from t12 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
843 f1
844 5
845 -4
846 -3
847 -2
848 -1
849 SELECT * from t32 /* must be f3 5*100 */;
850 f3
851 500
852 connection master;
853 UPDATE t12 SET f1=5 where f1=-5;
854 UPDATE t12 SET f1=4 where f1=-4;
855 UPDATE t12 SET f1=3 where f1=-3;
856 UPDATE t12 SET f1=2 where f1=-2;
857 UPDATE t12 SET f1=1 where f1=-1;
858 connection slave;
859 SELECT * from t12 /* must be f1 5 ... 1 */;
860 f1
861 5
862 4
863 3
864 2
865 1
866 SELECT * from t32 /* must be f3 5 * 100 ... 100 */;
867 f3
868 500
869 400
870 300
871 200
872 100
873 drop trigger trg12;
874 drop table t22,t32;
875 connection master;
876 drop table t12;
877 connection slave;
878 connection master;
879 -------------------
880 1
881 -------------------
882 connection master;
883 drop table if exists t11;
884 connection slave;
885 drop table if exists t21,t31;
886 connection master;
887 create table t11 (f1 int) /* 2 replicate */;
888 insert into t11 values (-5);
889 insert into t11 values (-4);
890 insert into t11 values (-3);
891 insert into t11 values (-2);
892 insert into t11 values (-1);
893 connection slave;
894 select * from t11;
895 f1
896 -5
897 -4
898 -3
899 -2
900 -1
901 create trigger trg11 before update on t11 /* slave local */
902 for each row
903 begin
904 DECLARE r integer;
905 SELECT f2 INTO r FROM t21 where f1=NEW.f1;
906 INSERT INTO t31 values (r);
907 end|
908 create table t21 (f1 int, f2 int) /* slave local */;
909 create table t31 (f3 int) /* slave local */;
910 insert into t21 values (5, 5*100);
911 insert into t21 values (4, 4*100);
912 insert into t21 values (3, 3*100);
913 insert into t21 values (2, 2*100);
914 insert into t21 values (1, 1*100);
915 select * from t21;
916 f1 f2
917 5 500
918 4 400
919 3 300
920 2 200
921 1 100
922 UPDATE t11 SET f1=5 where f1=-5;
923 SELECT * from t11 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
924 f1
925 5
926 -4
927 -3
928 -2
929 -1
930 SELECT * from t31 /* must be f3 5*100 */;
931 f3
932 500
933 connection master;
934 UPDATE t11 SET f1=5 where f1=-5;
935 UPDATE t11 SET f1=4 where f1=-4;
936 UPDATE t11 SET f1=3 where f1=-3;
937 UPDATE t11 SET f1=2 where f1=-2;
938 UPDATE t11 SET f1=1 where f1=-1;
939 connection slave;
940 SELECT * from t11 /* must be f1 5 ... 1 */;
941 f1
942 5
943 4
944 3
945 2
946 1
947 SELECT * from t31 /* must be f3 5 * 100 ... 100 */;
948 f3
949 500
950 400
951 300
952 200
953 100
954 drop trigger trg11;
955 drop table t21,t31;
956 connection master;
957 drop table t11;
958 connection slave;
959 connection master;
960 connection slave;
961 STOP SLAVE;
962 connection master;
963 FLUSH LOGS;
964 include/rpl_stop_server.inc [server_number=1]
965 include/rpl_start_server.inc [server_number=1]
966 --> Master binlog: Server ver: 5.0.16-debug-log, Binlog ver: 4
967 connection slave;
968 include/reset_slave.inc
969 Warnings:
970 Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'No' to 'Slave_Pos'
971 START SLAVE;
972 SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0;
973 MASTER_POS_WAIT('master-bin.000001', 513) >= 0
974 1
975 SHOW TABLES LIKE 't_';
976 Tables_in_test (t_)
977 t1
978 t2
979 SHOW TRIGGERS;
980 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
981 trg1 INSERT t1 INSERT INTO t2 VALUES(CURRENT_USER()) AFTER # latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
982 SELECT * FROM t1;
983 c
984 1
985 SELECT * FROM t2;
986 s
987 @
988 INSERT INTO t1 VALUES(2);
989 SELECT * FROM t1;
990 c
991 1
992 2
993 SELECT * FROM t2;
994 s
995 @
996 root@localhost
997 DROP TRIGGER trg1;
998 DROP TABLE t1;
999 DROP TABLE t2;
1000 STOP SLAVE;
1001 include/reset_slave.inc
1002 Warnings:
1003 Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'No' to 'Slave_Pos'
1004 connection master;
1005 SHOW TABLES LIKE 't_';
1006 Tables_in_test (t_)
1007 SHOW TRIGGERS;
1008 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
1009 RESET MASTER;
1010 connection slave;
1011 START SLAVE;
1012
1013 ---> Test for BUG#20438
1014
1015 ---> Preparing environment...
1016 connection master;
1017 DROP TABLE IF EXISTS t1;
1018 DROP TABLE IF EXISTS t2;
1019
1020 ---> Synchronizing slave with master...
1021 connection slave;
1022
1023 connection master;
1024
1025 ---> Creating objects...
1026 CREATE TABLE t1(c INT);
1027 CREATE TABLE t2(c INT);
1028 /*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
1029 FOR EACH ROW
1030 INSERT INTO t2 VALUES(NEW.c * 10) */;
1031
1032 ---> Inserting value...
1033 INSERT INTO t1 VALUES(1);
1034
1035 ---> Checking on master...
1036 SELECT * FROM t1;
1037 c
1038 1
1039 SELECT * FROM t2;
1040 c
1041 10
1042
1043 ---> Synchronizing slave with master...
1044 connection slave;
1045
1046 ---> Checking on slave...
1047 SELECT * FROM t1;
1048 c
1049 1
1050 SELECT * FROM t2;
1051 c
1052 10
1053
1054 connection master;
1055
1056 ---> Cleaning up...
1057 DROP TABLE t1;
1058 DROP TABLE t2;
1059 connection slave;
1060 connection master;
1061 connection master;
1062 drop table if exists t1;
1063 create table t1(a int, b varchar(50));
1064 drop trigger not_a_trigger;
1065 ERROR HY000: Trigger does not exist
1066 drop trigger if exists not_a_trigger;
1067 Warnings:
1068 Note 1360 Trigger does not exist
1069 create trigger t1_bi before insert on t1
1070 for each row set NEW.b := "In trigger t1_bi";
1071 insert into t1 values (1, "a");
1072 drop trigger if exists t1_bi;
1073 insert into t1 values (2, "b");
1074 drop trigger if exists t1_bi;
1075 Warnings:
1076 Note 1360 Trigger does not exist
1077 insert into t1 values (3, "c");
1078 select * from t1;
1079 a b
1080 1 In trigger t1_bi
1081 2 b
1082 3 c
1083 connection slave;
1084 select * from t1;
1085 a b
1086 1 In trigger t1_bi
1087 2 b
1088 3 c
1089 connection master;
1090 drop table t1;
1091 connection slave;
1092 connection master;
1093 include/rpl_reset.inc
1094 Warnings:
1095 Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'No' to 'Slave_Pos'
1096 connection slave;
1097 include/stop_slave.inc
1098 CHANGE MASTER TO MASTER_USE_GTID=NO;
1099 include/start_slave.inc
1100 connection master;
1101 create table t1 ( f int ) engine = innodb;
1102 create table log ( r int ) engine = myisam;
1103 create trigger tr
1104 after insert on t1
1105 for each row insert into log values ( new.f );
1106 set autocommit = 0;
1107 insert into t1 values ( 1 );
1108 rollback;
1109 Warnings:
1110 Warning 1196 Some non-transactional changed tables couldn't be rolled back
1111 connection slave;
1112 include/diff_tables.inc [master:t1, slave:t1]
1113 include/diff_tables.inc [master:log, slave:log]
1114 connection master;
1115 drop table t1, log;
1116 connection slave;
1117 connection master;
1118 drop trigger if exists notexistent;
1119 Warnings:
1120 Note 1360 Trigger does not exist
1121 include/show_binlog_events.inc
1122 Log_name Pos Event_type Server_id End_log_pos Info
1123 master-bin.000001 # Gtid # # GTID #-#-#
1124 master-bin.000001 # Query # # use `test`; drop trigger if exists notexistent
1125 connection slave;
1126 include/show_binlog_events.inc
1127 Log_name Pos Event_type Server_id End_log_pos Info
1128 slave-bin.000001 # Gtid # # GTID #-#-#
1129 slave-bin.000001 # Query # # use `test`; drop trigger if exists notexistent
1130 connection master;
1131 create table t1 (a int primary key, b int);
1132 create trigger tr3
1133 before insert on t1
1134 for each row set NEW.b := (NEW.b+1)*4;
1135 create trigger tr1
1136 before insert on t1
1137 for each row precedes tr3 set NEW.b := (NEW.b+1)*2;
1138 create trigger tr2
1139 before insert on t1
1140 for each row follows tr1 set NEW.b := (NEW.b+1)*3;
1141 show triggers;
1142 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
1143 tr1 INSERT t1 set NEW.b := (NEW.b+1)*2 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
1144 tr2 INSERT t1 set NEW.b := (NEW.b+1)*3 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
1145 tr3 INSERT t1 set NEW.b := (NEW.b+1)*4 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
1146 insert into t1 values (1,1);
1147 select * from t1;
1148 a b
1149 1 64
1150 connection slave;
1151 show triggers;
1152 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
1153 tr1 INSERT t1 set NEW.b := (NEW.b+1)*2 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
1154 tr2 INSERT t1 set NEW.b := (NEW.b+1)*3 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
1155 tr3 INSERT t1 set NEW.b := (NEW.b+1)*4 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
1156 select * from t1;
1157 a b
1158 1 64
1159 connection master;
1160 drop table t1;
1161 CREATE TABLE t1 (i INT);
1162 CREATE OR REPLACE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW SET @a=1;
1163 CREATE OR REPLACE TRIGGER tr BEFORE DELETE ON t1 FOR EACH ROW PRECEDES non_existing_trigger SET @a=2;
1164 ERROR HY000: Referenced trigger 'non_existing_trigger' for the given action time and event type does not exist
1165 SHOW TRIGGERS;
1166 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
1167 tr INSERT t1 SET @a=1 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
1168 connection slave;
1169 SHOW TRIGGERS;
1170 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
1171 tr INSERT t1 SET @a=1 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
1172 connection master;
1173 drop table t1;
1174 include/rpl_end.inc