Debian Codegraph @

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

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 DROP TABLE IF EXISTS t1;
7 DROP TABLE IF EXISTS t2;
8 DROP TABLE IF EXISTS t3;
9 create table t1 (a int auto_increment, primary key (a), b int, rand_value double not null);
10 create table t2 (a int auto_increment, primary key (a), b int);
11 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);
12 create trigger t1 before insert on t1 for each row
13 begin
14 insert into t3 values (NULL, "t1", new.a, new.b, rand());
15 end|
16 create trigger t2 after insert on t2 for each row
17 begin
18 insert into t3 values (NULL, "t2", new.a, new.b, rand());
19 end|
20 insert into t3 values(100,"log",0,0,0);
21 SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
22 insert into t1 values(1,1,rand()),(NULL,2,rand());
23 insert into t2 (b) values(last_insert_id());
24 insert into t2 values(3,0),(NULL,0);
25 insert into t2 values(NULL,0),(500,0);
26 select a,b, truncate(rand_value,4) from t1;
27 a b truncate(rand_value,4)
28 1 1 0.432
29 2 2 0.3055
30 select * from t2;
31 a b
32 1 2
33 3 0
34 4 0
35 6 0
36 500 0
37 select a,name, old_a, old_b, truncate(rand_value,4) from t3;
38 a name old_a old_b truncate(rand_value,4)
39 100 log 0 0 0
40 101 t1 1 1 0.3203
41 102 t1 0 2 0.5666
42 103 t2 1 2 0.9164
43 104 t2 3 0 0.8826
44 105 t2 4 0 0.6635
45 106 t2 6 0 0.6699
46 107 t2 500 0 0.3593
47 include/sync_slave_sql_with_master.inc
48
49 --- On slave --
50 select a,b, truncate(rand_value,4) from t1;
51 a b truncate(rand_value,4)
52 1 1 0.432
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
64 101 t1 1 1 0.3203
65 102 t1 0 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 drop table t1,t2,t3;
72 select get_lock("bug12480",2);
73 get_lock("bug12480",2)
74 1
75 create table t1 (a datetime,b datetime, c datetime);
76 drop function if exists bug12480;
77 create function bug12480() returns datetime
78 begin
79 set @a=get_lock("bug12480",2);
80 return now();
81 end|
82 create trigger t1_first before insert on t1
83 for each row begin
84 set @a=get_lock("bug12480",2);
85 set new.b= now();
86 set new.c= bug12480();
87 end
88 |
89 insert into t1 set a = now();
90 select a=b && a=c from t1;
91 a=b && a=c
92 1
93 Warnings:
94 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
95 SELECT routine_name, definer
96 FROM information_schema.routines
97 WHERE routine_name = 'bug12480';
98 ROUTINE_NAME DEFINER
99 bug12480 root@localhost
100 SELECT trigger_name, definer
101 FROM information_schema.triggers
102 WHERE trigger_name = 't1_first';
103 TRIGGER_NAME DEFINER
104 t1_first root@localhost
105 include/sync_slave_sql_with_master.inc
106
107 --- On 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 Warnings:
122 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
123 test
124 1
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 Warnings:
134 Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
135 drop function bug12480;
136 drop table t1;
137 create table t1 (i int);
138 create table t2 (i int);
139 create trigger tr1 before insert on t1 for each row
140 begin
141 insert into t2 values (1);
142 end|
143 create database other;
144 use other;
145 insert into test.t1 values (1);
146 include/sync_slave_sql_with_master.inc
147 use test;
148 drop table t1,t2;
149 drop database other;
150 test case for BUG#13227
151 -------------------
152 10
153 -------------------
154 drop table if exists t110;
155 drop table if exists t210,t310;
156 create table t110 (f1 int) /* 2 replicate */;
157 insert into t110 values (-5);
158 insert into t110 values (-4);
159 insert into t110 values (-3);
160 insert into t110 values (-2);
161 insert into t110 values (-1);
162 include/sync_slave_sql_with_master.inc
163 select * from t110;
164 f1
165 -5
166 -4
167 -3
168 -2
169 -1
170 create trigger trg110 before update on t110 /* slave local */
171 for each row
172 begin
173 DECLARE r integer;
174 SELECT f2 INTO r FROM t210 where f1=NEW.f1;
175 INSERT INTO t310 values (r);
176 end|
177 create table t210 (f1 int, f2 int) /* slave local */;
178 create table t310 (f3 int) /* slave local */;
179 insert into t210 values (5, 5*100);
180 insert into t210 values (4, 4*100);
181 insert into t210 values (3, 3*100);
182 insert into t210 values (2, 2*100);
183 insert into t210 values (1, 1*100);
184 select * from t210;
185 f1 f2
186 5 500
187 4 400
188 3 300
189 2 200
190 1 100
191 UPDATE t110 SET f1=5 where f1=-5;
192 SELECT * from t110 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
193 f1
194 5
195 -4
196 -3
197 -2
198 -1
199 SELECT * from t310 /* must be f3 5*100 */;
200 f3
201 500
202 UPDATE t110 SET f1=5 where f1=-5;
203 UPDATE t110 SET f1=4 where f1=-4;
204 UPDATE t110 SET f1=3 where f1=-3;
205 UPDATE t110 SET f1=2 where f1=-2;
206 UPDATE t110 SET f1=1 where f1=-1;
207 include/sync_slave_sql_with_master.inc
208 SELECT * from t110 /* must be f1 5 ... 1 */;
209 f1
210 5
211 4
212 3
213 2
214 1
215 SELECT * from t310 /* must be f3 5 * 100 ... 100 */;
216 f3
217 500
218 400
219 300
220 200
221 100
222 drop trigger trg110;
223 drop table t210,t310;
224 drop table t110;
225 include/sync_slave_sql_with_master.inc
226 -------------------
227 9
228 -------------------
229 drop table if exists t19;
230 drop table if exists t29,t39;
231 create table t19 (f1 int) /* 2 replicate */;
232 insert into t19 values (-5);
233 insert into t19 values (-4);
234 insert into t19 values (-3);
235 insert into t19 values (-2);
236 insert into t19 values (-1);
237 include/sync_slave_sql_with_master.inc
238 select * from t19;
239 f1
240 -5
241 -4
242 -3
243 -2
244 -1
245 create trigger trg19 before update on t19 /* slave local */
246 for each row
247 begin
248 DECLARE r integer;
249 SELECT f2 INTO r FROM t29 where f1=NEW.f1;
250 INSERT INTO t39 values (r);
251 end|
252 create table t29 (f1 int, f2 int) /* slave local */;
253 create table t39 (f3 int) /* slave local */;
254 insert into t29 values (5, 5*100);
255 insert into t29 values (4, 4*100);
256 insert into t29 values (3, 3*100);
257 insert into t29 values (2, 2*100);
258 insert into t29 values (1, 1*100);
259 select * from t29;
260 f1 f2
261 5 500
262 4 400
263 3 300
264 2 200
265 1 100
266 UPDATE t19 SET f1=5 where f1=-5;
267 SELECT * from t19 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
268 f1
269 5
270 -4
271 -3
272 -2
273 -1
274 SELECT * from t39 /* must be f3 5*100 */;
275 f3
276 500
277 UPDATE t19 SET f1=5 where f1=-5;
278 UPDATE t19 SET f1=4 where f1=-4;
279 UPDATE t19 SET f1=3 where f1=-3;
280 UPDATE t19 SET f1=2 where f1=-2;
281 UPDATE t19 SET f1=1 where f1=-1;
282 include/sync_slave_sql_with_master.inc
283 SELECT * from t19 /* must be f1 5 ... 1 */;
284 f1
285 5
286 4
287 3
288 2
289 1
290 SELECT * from t39 /* must be f3 5 * 100 ... 100 */;
291 f3
292 500
293 400
294 300
295 200
296 100
297 drop trigger trg19;
298 drop table t29,t39;
299 drop table t19;
300 include/sync_slave_sql_with_master.inc
301 -------------------
302 8
303 -------------------
304 drop table if exists t18;
305 drop table if exists t28,t38;
306 create table t18 (f1 int) /* 2 replicate */;
307 insert into t18 values (-5);
308 insert into t18 values (-4);
309 insert into t18 values (-3);
310 insert into t18 values (-2);
311 insert into t18 values (-1);
312 include/sync_slave_sql_with_master.inc
313 select * from t18;
314 f1
315 -5
316 -4
317 -3
318 -2
319 -1
320 create trigger trg18 before update on t18 /* slave local */
321 for each row
322 begin
323 DECLARE r integer;
324 SELECT f2 INTO r FROM t28 where f1=NEW.f1;
325 INSERT INTO t38 values (r);
326 end|
327 create table t28 (f1 int, f2 int) /* slave local */;
328 create table t38 (f3 int) /* slave local */;
329 insert into t28 values (5, 5*100);
330 insert into t28 values (4, 4*100);
331 insert into t28 values (3, 3*100);
332 insert into t28 values (2, 2*100);
333 insert into t28 values (1, 1*100);
334 select * from t28;
335 f1 f2
336 5 500
337 4 400
338 3 300
339 2 200
340 1 100
341 UPDATE t18 SET f1=5 where f1=-5;
342 SELECT * from t18 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
343 f1
344 5
345 -4
346 -3
347 -2
348 -1
349 SELECT * from t38 /* must be f3 5*100 */;
350 f3
351 500
352 UPDATE t18 SET f1=5 where f1=-5;
353 UPDATE t18 SET f1=4 where f1=-4;
354 UPDATE t18 SET f1=3 where f1=-3;
355 UPDATE t18 SET f1=2 where f1=-2;
356 UPDATE t18 SET f1=1 where f1=-1;
357 include/sync_slave_sql_with_master.inc
358 SELECT * from t18 /* must be f1 5 ... 1 */;
359 f1
360 5
361 4
362 3
363 2
364 1
365 SELECT * from t38 /* must be f3 5 * 100 ... 100 */;
366 f3
367 500
368 400
369 300
370 200
371 100
372 drop trigger trg18;
373 drop table t28,t38;
374 drop table t18;
375 include/sync_slave_sql_with_master.inc
376 -------------------
377 7
378 -------------------
379 drop table if exists t17;
380 drop table if exists t27,t37;
381 create table t17 (f1 int) /* 2 replicate */;
382 insert into t17 values (-5);
383 insert into t17 values (-4);
384 insert into t17 values (-3);
385 insert into t17 values (-2);
386 insert into t17 values (-1);
387 include/sync_slave_sql_with_master.inc
388 select * from t17;
389 f1
390 -5
391 -4
392 -3
393 -2
394 -1
395 create trigger trg17 before update on t17 /* slave local */
396 for each row
397 begin
398 DECLARE r integer;
399 SELECT f2 INTO r FROM t27 where f1=NEW.f1;
400 INSERT INTO t37 values (r);
401 end|
402 create table t27 (f1 int, f2 int) /* slave local */;
403 create table t37 (f3 int) /* slave local */;
404 insert into t27 values (5, 5*100);
405 insert into t27 values (4, 4*100);
406 insert into t27 values (3, 3*100);
407 insert into t27 values (2, 2*100);
408 insert into t27 values (1, 1*100);
409 select * from t27;
410 f1 f2
411 5 500
412 4 400
413 3 300
414 2 200
415 1 100
416 UPDATE t17 SET f1=5 where f1=-5;
417 SELECT * from t17 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
418 f1
419 5
420 -4
421 -3
422 -2
423 -1
424 SELECT * from t37 /* must be f3 5*100 */;
425 f3
426 500
427 UPDATE t17 SET f1=5 where f1=-5;
428 UPDATE t17 SET f1=4 where f1=-4;
429 UPDATE t17 SET f1=3 where f1=-3;
430 UPDATE t17 SET f1=2 where f1=-2;
431 UPDATE t17 SET f1=1 where f1=-1;
432 include/sync_slave_sql_with_master.inc
433 SELECT * from t17 /* must be f1 5 ... 1 */;
434 f1
435 5
436 4
437 3
438 2
439 1
440 SELECT * from t37 /* must be f3 5 * 100 ... 100 */;
441 f3
442 500
443 400
444 300
445 200
446 100
447 drop trigger trg17;
448 drop table t27,t37;
449 drop table t17;
450 include/sync_slave_sql_with_master.inc
451 -------------------
452 6
453 -------------------
454 drop table if exists t16;
455 drop table if exists t26,t36;
456 create table t16 (f1 int) /* 2 replicate */;
457 insert into t16 values (-5);
458 insert into t16 values (-4);
459 insert into t16 values (-3);
460 insert into t16 values (-2);
461 insert into t16 values (-1);
462 include/sync_slave_sql_with_master.inc
463 select * from t16;
464 f1
465 -5
466 -4
467 -3
468 -2
469 -1
470 create trigger trg16 before update on t16 /* slave local */
471 for each row
472 begin
473 DECLARE r integer;
474 SELECT f2 INTO r FROM t26 where f1=NEW.f1;
475 INSERT INTO t36 values (r);
476 end|
477 create table t26 (f1 int, f2 int) /* slave local */;
478 create table t36 (f3 int) /* slave local */;
479 insert into t26 values (5, 5*100);
480 insert into t26 values (4, 4*100);
481 insert into t26 values (3, 3*100);
482 insert into t26 values (2, 2*100);
483 insert into t26 values (1, 1*100);
484 select * from t26;
485 f1 f2
486 5 500
487 4 400
488 3 300
489 2 200
490 1 100
491 UPDATE t16 SET f1=5 where f1=-5;
492 SELECT * from t16 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
493 f1
494 5
495 -4
496 -3
497 -2
498 -1
499 SELECT * from t36 /* must be f3 5*100 */;
500 f3
501 500
502 UPDATE t16 SET f1=5 where f1=-5;
503 UPDATE t16 SET f1=4 where f1=-4;
504 UPDATE t16 SET f1=3 where f1=-3;
505 UPDATE t16 SET f1=2 where f1=-2;
506 UPDATE t16 SET f1=1 where f1=-1;
507 include/sync_slave_sql_with_master.inc
508 SELECT * from t16 /* must be f1 5 ... 1 */;
509 f1
510 5
511 4
512 3
513 2
514 1
515 SELECT * from t36 /* must be f3 5 * 100 ... 100 */;
516 f3
517 500
518 400
519 300
520 200
521 100
522 drop trigger trg16;
523 drop table t26,t36;
524 drop table t16;
525 include/sync_slave_sql_with_master.inc
526 -------------------
527 5
528 -------------------
529 drop table if exists t15;
530 drop table if exists t25,t35;
531 create table t15 (f1 int) /* 2 replicate */;
532 insert into t15 values (-5);
533 insert into t15 values (-4);
534 insert into t15 values (-3);
535 insert into t15 values (-2);
536 insert into t15 values (-1);
537 include/sync_slave_sql_with_master.inc
538 select * from t15;
539 f1
540 -5
541 -4
542 -3
543 -2
544 -1
545 create trigger trg15 before update on t15 /* slave local */
546 for each row
547 begin
548 DECLARE r integer;
549 SELECT f2 INTO r FROM t25 where f1=NEW.f1;
550 INSERT INTO t35 values (r);
551 end|
552 create table t25 (f1 int, f2 int) /* slave local */;
553 create table t35 (f3 int) /* slave local */;
554 insert into t25 values (5, 5*100);
555 insert into t25 values (4, 4*100);
556 insert into t25 values (3, 3*100);
557 insert into t25 values (2, 2*100);
558 insert into t25 values (1, 1*100);
559 select * from t25;
560 f1 f2
561 5 500
562 4 400
563 3 300
564 2 200
565 1 100
566 UPDATE t15 SET f1=5 where f1=-5;
567 SELECT * from t15 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
568 f1
569 5
570 -4
571 -3
572 -2
573 -1
574 SELECT * from t35 /* must be f3 5*100 */;
575 f3
576 500
577 UPDATE t15 SET f1=5 where f1=-5;
578 UPDATE t15 SET f1=4 where f1=-4;
579 UPDATE t15 SET f1=3 where f1=-3;
580 UPDATE t15 SET f1=2 where f1=-2;
581 UPDATE t15 SET f1=1 where f1=-1;
582 include/sync_slave_sql_with_master.inc
583 SELECT * from t15 /* must be f1 5 ... 1 */;
584 f1
585 5
586 4
587 3
588 2
589 1
590 SELECT * from t35 /* must be f3 5 * 100 ... 100 */;
591 f3
592 500
593 400
594 300
595 200
596 100
597 drop trigger trg15;
598 drop table t25,t35;
599 drop table t15;
600 include/sync_slave_sql_with_master.inc
601 -------------------
602 4
603 -------------------
604 drop table if exists t14;
605 drop table if exists t24,t34;
606 create table t14 (f1 int) /* 2 replicate */;
607 insert into t14 values (-5);
608 insert into t14 values (-4);
609 insert into t14 values (-3);
610 insert into t14 values (-2);
611 insert into t14 values (-1);
612 include/sync_slave_sql_with_master.inc
613 select * from t14;
614 f1
615 -5
616 -4
617 -3
618 -2
619 -1
620 create trigger trg14 before update on t14 /* slave local */
621 for each row
622 begin
623 DECLARE r integer;
624 SELECT f2 INTO r FROM t24 where f1=NEW.f1;
625 INSERT INTO t34 values (r);
626 end|
627 create table t24 (f1 int, f2 int) /* slave local */;
628 create table t34 (f3 int) /* slave local */;
629 insert into t24 values (5, 5*100);
630 insert into t24 values (4, 4*100);
631 insert into t24 values (3, 3*100);
632 insert into t24 values (2, 2*100);
633 insert into t24 values (1, 1*100);
634 select * from t24;
635 f1 f2
636 5 500
637 4 400
638 3 300
639 2 200
640 1 100
641 UPDATE t14 SET f1=5 where f1=-5;
642 SELECT * from t14 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
643 f1
644 5
645 -4
646 -3
647 -2
648 -1
649 SELECT * from t34 /* must be f3 5*100 */;
650 f3
651 500
652 UPDATE t14 SET f1=5 where f1=-5;
653 UPDATE t14 SET f1=4 where f1=-4;
654 UPDATE t14 SET f1=3 where f1=-3;
655 UPDATE t14 SET f1=2 where f1=-2;
656 UPDATE t14 SET f1=1 where f1=-1;
657 include/sync_slave_sql_with_master.inc
658 SELECT * from t14 /* must be f1 5 ... 1 */;
659 f1
660 5
661 4
662 3
663 2
664 1
665 SELECT * from t34 /* must be f3 5 * 100 ... 100 */;
666 f3
667 500
668 400
669 300
670 200
671 100
672 drop trigger trg14;
673 drop table t24,t34;
674 drop table t14;
675 include/sync_slave_sql_with_master.inc
676 -------------------
677 3
678 -------------------
679 drop table if exists t13;
680 drop table if exists t23,t33;
681 create table t13 (f1 int) /* 2 replicate */;
682 insert into t13 values (-5);
683 insert into t13 values (-4);
684 insert into t13 values (-3);
685 insert into t13 values (-2);
686 insert into t13 values (-1);
687 include/sync_slave_sql_with_master.inc
688 select * from t13;
689 f1
690 -5
691 -4
692 -3
693 -2
694 -1
695 create trigger trg13 before update on t13 /* slave local */
696 for each row
697 begin
698 DECLARE r integer;
699 SELECT f2 INTO r FROM t23 where f1=NEW.f1;
700 INSERT INTO t33 values (r);
701 end|
702 create table t23 (f1 int, f2 int) /* slave local */;
703 create table t33 (f3 int) /* slave local */;
704 insert into t23 values (5, 5*100);
705 insert into t23 values (4, 4*100);
706 insert into t23 values (3, 3*100);
707 insert into t23 values (2, 2*100);
708 insert into t23 values (1, 1*100);
709 select * from t23;
710 f1 f2
711 5 500
712 4 400
713 3 300
714 2 200
715 1 100
716 UPDATE t13 SET f1=5 where f1=-5;
717 SELECT * from t13 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
718 f1
719 5
720 -4
721 -3
722 -2
723 -1
724 SELECT * from t33 /* must be f3 5*100 */;
725 f3
726 500
727 UPDATE t13 SET f1=5 where f1=-5;
728 UPDATE t13 SET f1=4 where f1=-4;
729 UPDATE t13 SET f1=3 where f1=-3;
730 UPDATE t13 SET f1=2 where f1=-2;
731 UPDATE t13 SET f1=1 where f1=-1;
732 include/sync_slave_sql_with_master.inc
733 SELECT * from t13 /* must be f1 5 ... 1 */;
734 f1
735 5
736 4
737 3
738 2
739 1
740 SELECT * from t33 /* must be f3 5 * 100 ... 100 */;
741 f3
742 500
743 400
744 300
745 200
746 100
747 drop trigger trg13;
748 drop table t23,t33;
749 drop table t13;
750 include/sync_slave_sql_with_master.inc
751 -------------------
752 2
753 -------------------
754 drop table if exists t12;
755 drop table if exists t22,t32;
756 create table t12 (f1 int) /* 2 replicate */;
757 insert into t12 values (-5);
758 insert into t12 values (-4);
759 insert into t12 values (-3);
760 insert into t12 values (-2);
761 insert into t12 values (-1);
762 include/sync_slave_sql_with_master.inc
763 select * from t12;
764 f1
765 -5
766 -4
767 -3
768 -2
769 -1
770 create trigger trg12 before update on t12 /* slave local */
771 for each row
772 begin
773 DECLARE r integer;
774 SELECT f2 INTO r FROM t22 where f1=NEW.f1;
775 INSERT INTO t32 values (r);
776 end|
777 create table t22 (f1 int, f2 int) /* slave local */;
778 create table t32 (f3 int) /* slave local */;
779 insert into t22 values (5, 5*100);
780 insert into t22 values (4, 4*100);
781 insert into t22 values (3, 3*100);
782 insert into t22 values (2, 2*100);
783 insert into t22 values (1, 1*100);
784 select * from t22;
785 f1 f2
786 5 500
787 4 400
788 3 300
789 2 200
790 1 100
791 UPDATE t12 SET f1=5 where f1=-5;
792 SELECT * from t12 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
793 f1
794 5
795 -4
796 -3
797 -2
798 -1
799 SELECT * from t32 /* must be f3 5*100 */;
800 f3
801 500
802 UPDATE t12 SET f1=5 where f1=-5;
803 UPDATE t12 SET f1=4 where f1=-4;
804 UPDATE t12 SET f1=3 where f1=-3;
805 UPDATE t12 SET f1=2 where f1=-2;
806 UPDATE t12 SET f1=1 where f1=-1;
807 include/sync_slave_sql_with_master.inc
808 SELECT * from t12 /* must be f1 5 ... 1 */;
809 f1
810 5
811 4
812 3
813 2
814 1
815 SELECT * from t32 /* must be f3 5 * 100 ... 100 */;
816 f3
817 500
818 400
819 300
820 200
821 100
822 drop trigger trg12;
823 drop table t22,t32;
824 drop table t12;
825 include/sync_slave_sql_with_master.inc
826 -------------------
827 1
828 -------------------
829 drop table if exists t11;
830 drop table if exists t21,t31;
831 create table t11 (f1 int) /* 2 replicate */;
832 insert into t11 values (-5);
833 insert into t11 values (-4);
834 insert into t11 values (-3);
835 insert into t11 values (-2);
836 insert into t11 values (-1);
837 include/sync_slave_sql_with_master.inc
838 select * from t11;
839 f1
840 -5
841 -4
842 -3
843 -2
844 -1
845 create trigger trg11 before update on t11 /* slave local */
846 for each row
847 begin
848 DECLARE r integer;
849 SELECT f2 INTO r FROM t21 where f1=NEW.f1;
850 INSERT INTO t31 values (r);
851 end|
852 create table t21 (f1 int, f2 int) /* slave local */;
853 create table t31 (f3 int) /* slave local */;
854 insert into t21 values (5, 5*100);
855 insert into t21 values (4, 4*100);
856 insert into t21 values (3, 3*100);
857 insert into t21 values (2, 2*100);
858 insert into t21 values (1, 1*100);
859 select * from t21;
860 f1 f2
861 5 500
862 4 400
863 3 300
864 2 200
865 1 100
866 UPDATE t11 SET f1=5 where f1=-5;
867 SELECT * from t11 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
868 f1
869 5
870 -4
871 -3
872 -2
873 -1
874 SELECT * from t31 /* must be f3 5*100 */;
875 f3
876 500
877 UPDATE t11 SET f1=5 where f1=-5;
878 UPDATE t11 SET f1=4 where f1=-4;
879 UPDATE t11 SET f1=3 where f1=-3;
880 UPDATE t11 SET f1=2 where f1=-2;
881 UPDATE t11 SET f1=1 where f1=-1;
882 include/sync_slave_sql_with_master.inc
883 SELECT * from t11 /* must be f1 5 ... 1 */;
884 f1
885 5
886 4
887 3
888 2
889 1
890 SELECT * from t31 /* must be f3 5 * 100 ... 100 */;
891 f3
892 500
893 400
894 300
895 200
896 100
897 drop trigger trg11;
898 drop table t21,t31;
899 drop table t11;
900 include/sync_slave_sql_with_master.inc
901 STOP SLAVE;
902 Warnings:
903 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
904 FLUSH LOGS;
905 include/rpl_stop_server.inc [server_number=1]
906 include/rpl_start_server.inc [server_number=1]
907 --> Master binlog: Server ver: 5.0.16-debug-log, Binlog ver: 4
908 RESET SLAVE;
909 Warnings:
910 Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
911 START SLAVE;
912 Warnings:
913 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
914 SELECT SOURCE_POS_WAIT('master-bin.000001', 513) >= 0;
915 SOURCE_POS_WAIT('master-bin.000001', 513) >= 0
916 NULL
917 SHOW TABLES LIKE 't_';
918 Tables_in_test (t_)
919 t1
920 t2
921 SHOW TRIGGERS;
922 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
923 DROP TABLE t1;
924 DROP TABLE t2;
925 STOP SLAVE;
926 Warnings:
927 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
928 RESET SLAVE;
929 Warnings:
930 Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
931 SHOW TABLES LIKE 't_';
932 Tables_in_test (t_)
933 SHOW TRIGGERS;
934 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
935 RESET MASTER;
936 START SLAVE;
937 Warnings:
938 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
939 include/rpl_reset.inc
940
941 ---> Test for BUG#20438
942
943 ---> Preparing environment...
944 ---> connection: master
945 DROP TABLE IF EXISTS t1;
946 DROP TABLE IF EXISTS t2;
947
948 ---> Synchronizing slave with master...
949 include/sync_slave_sql_with_master.inc
950
951 ---> connection: master
952
953 ---> Creating objects...
954 CREATE TABLE t1(c INT);
955 CREATE TABLE t2(c INT);
956 /*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
957 FOR EACH ROW
958 INSERT INTO t2 VALUES(NEW.c * 10) */;
959
960 ---> Inserting value...
961 INSERT INTO t1 VALUES(1);
962
963 ---> Checking on master...
964 SELECT * FROM t1;
965 c
966 1
967 SELECT * FROM t2;
968 c
969 10
970
971 ---> Synchronizing slave with master...
972 include/sync_slave_sql_with_master.inc
973 ---> connection: master
974
975 ---> Checking on slave...
976 SELECT * FROM t1;
977 c
978 1
979 SELECT * FROM t2;
980 c
981 10
982
983 ---> connection: master
984
985 ---> Cleaning up...
986 DROP TABLE t1;
987 DROP TABLE t2;
988 include/sync_slave_sql_with_master.inc
989 drop table if exists t1;
990 create table t1(a int, b varchar(50));
991 drop trigger not_a_trigger;
992 ERROR HY000: Trigger does not exist
993 drop trigger if exists not_a_trigger;
994 Warnings:
995 Note 1360 Trigger does not exist
996 create trigger t1_bi before insert on t1
997 for each row set NEW.b := "In trigger t1_bi";
998 insert into t1 values (1, "a");
999 drop trigger if exists t1_bi;
1000 insert into t1 values (2, "b");
1001 drop trigger if exists t1_bi;
1002 Warnings:
1003 Note 1360 Trigger does not exist
1004 insert into t1 values (3, "c");
1005 select * from t1;
1006 a b
1007 1 In trigger t1_bi
1008 2 b
1009 3 c
1010 include/sync_slave_sql_with_master.inc
1011 select * from t1;
1012 a b
1013 1 In trigger t1_bi
1014 2 b
1015 3 c
1016 drop table t1;
1017 include/sync_slave_sql_with_master.inc
1018 #
1019 # WL#3253: Multiple triggers with the same value TIME/ACTION per table
1020 #
1021 include/rpl_reset.inc
1022 SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00');
1023 CREATE TABLE t1 (a INT);
1024 CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
1025 CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
1026 CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi SET @a:=0;
1027 CREATE TRIGGER tr1_1_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=11;
1028 CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @b:=1;
1029 CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @b:=2;
1030
1031 ---> Synchronizing slave with master...
1032 ---> connection: slave
1033 SHOW TRIGGERS;
1034 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
1035 tr0_bi INSERT t1 SET @a:=0 BEFORE 2013-01-31 09:00:00.00 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
1036 tr1_bi INSERT t1 SET @a:=1 BEFORE 2013-01-31 09:00:00.00 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
1037 tr1_1_bi INSERT t1 SET @a:=11 BEFORE 2013-01-31 09:00:00.00 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
1038 tr2_bi INSERT t1 SET @a:=2 BEFORE 2013-01-31 09:00:00.00 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
1039 tr1_bu UPDATE t1 SET @b:=1 BEFORE 2013-01-31 09:00:00.00 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
1040 tr2_bu UPDATE t1 SET @b:=2 BEFORE 2013-01-31 09:00:00.00 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
1041 SELECT trigger_name, action_order, created
1042 FROM information_schema.triggers
1043 WHERE trigger_schema='test'
1044 ORDER BY trigger_name, action_order;
1045 TRIGGER_NAME ACTION_ORDER CREATED
1046 tr0_bi 1 2013-01-31 09:00:00.00
1047 tr1_1_bi 3 2013-01-31 09:00:00.00
1048 tr1_bi 2 2013-01-31 09:00:00.00
1049 tr1_bu 1 2013-01-31 09:00:00.00
1050 tr2_bi 4 2013-01-31 09:00:00.00
1051 tr2_bu 2 2013-01-31 09:00:00.00
1052 DROP TABLE t1;
1053 include/rpl_end.inc