summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/rpl/r/rpl_trigger.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/rpl/r/rpl_trigger.result')
-rw-r--r--mysql-test/suite/rpl/r/rpl_trigger.result1161
1 files changed, 1161 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/r/rpl_trigger.result b/mysql-test/suite/rpl/r/rpl_trigger.result
new file mode 100644
index 00000000..06d5a3c8
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_trigger.result
@@ -0,0 +1,1161 @@
+include/master-slave.inc
+[connection master]
+connection master;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP TABLE IF EXISTS t3;
+create table t1 (a int auto_increment, primary key (a), b int, rand_value double not null);
+create table t2 (a int auto_increment, primary key (a), b int) engine=innodb;
+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);
+create trigger t1 before insert on t1 for each row
+begin
+insert into t3 values (NULL, "t1", new.a, new.b, rand());
+end|
+create trigger t2 after insert on t2 for each row
+begin
+insert into t3 values (NULL, "t2", new.a, new.b, rand());
+end|
+insert into t3 values(100,"log",0,0,0);
+SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
+insert into t1 values(1,1,rand()),(NULL,2,rand());
+insert into t2 (b) values(last_insert_id());
+insert into t2 values(3,0),(NULL,0);
+insert into t2 values(NULL,0),(500,0);
+select a,b, truncate(rand_value,4) from t1;
+a b truncate(rand_value,4)
+1 1 0.4320
+2 2 0.3055
+select * from t2;
+a b
+1 2
+3 0
+4 0
+6 0
+500 0
+select a,name, old_a, old_b, truncate(rand_value,4) from t3;
+a name old_a old_b truncate(rand_value,4)
+100 log 0 0 0.0000
+101 t1 1 1 0.3203
+102 t1 NULL 2 0.5666
+103 t2 1 2 0.9164
+104 t2 3 0 0.8826
+105 t2 4 0 0.6635
+106 t2 6 0 0.6699
+107 t2 500 0 0.3593
+connection slave;
+select a,b, truncate(rand_value,4) from t1;
+a b truncate(rand_value,4)
+1 1 0.4320
+2 2 0.3055
+select * from t2;
+a b
+1 2
+3 0
+4 0
+6 0
+500 0
+select a,name, old_a, old_b, truncate(rand_value,4) from t3;
+a name old_a old_b truncate(rand_value,4)
+100 log 0 0 0.0000
+101 t1 1 1 0.3203
+102 t1 NULL 2 0.5666
+103 t2 1 2 0.9164
+104 t2 3 0 0.8826
+105 t2 4 0 0.6635
+106 t2 6 0 0.6699
+107 t2 500 0 0.3593
+connection master;
+drop table t1,t2,t3;
+connect con2,localhost,root,,;
+connection con2;
+select get_lock("bug12480",2);
+get_lock("bug12480",2)
+1
+connection default;
+create table t1 (a datetime,b datetime, c datetime);
+drop function if exists bug12480;
+create function bug12480() returns datetime
+begin
+set @a=get_lock("bug12480",2);
+return now();
+end|
+create trigger t1_first before insert on t1
+for each row begin
+set @a=get_lock("bug12480",2);
+set new.b= now();
+set new.c= bug12480();
+end
+|
+insert into t1 set a = now();
+select a=b && a=c from t1;
+a=b && a=c
+1
+SELECT routine_name, definer
+FROM information_schema.routines
+WHERE routine_name = 'bug12480';
+routine_name definer
+bug12480 root@localhost
+SELECT trigger_name, definer
+FROM information_schema.triggers
+WHERE trigger_name = 't1_first';
+trigger_name definer
+t1_first root@localhost
+connection slave;
+SELECT routine_name, definer
+FROM information_schema.routines
+WHERE routine_name = 'bug12480';
+routine_name definer
+bug12480 root@localhost
+SELECT trigger_name, definer
+FROM information_schema.triggers
+WHERE trigger_name = 't1_first';
+trigger_name definer
+t1_first root@localhost
+select a=b && a=c from t1;
+a=b && a=c
+1
+test
+1
+connection master;
+disconnect con2;
+truncate table t1;
+drop trigger t1_first;
+insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now());
+select a=b && a=c from t1;
+a=b && a=c
+1
+1
+1
+drop function bug12480;
+drop table t1;
+create table t1 (i int);
+create table t2 (i int);
+create trigger tr1 before insert on t1 for each row
+begin
+insert into t2 values (1);
+end|
+create database other;
+use other;
+insert into test.t1 values (1);
+connection slave;
+connection master;
+use test;
+drop table t1,t2;
+drop database other;
+test case for BUG#13227
+-------------------
+10
+-------------------
+connection master;
+drop table if exists t110;
+connection slave;
+drop table if exists t210,t310;
+connection master;
+create table t110 (f1 int) /* 2 replicate */;
+insert into t110 values (-5);
+insert into t110 values (-4);
+insert into t110 values (-3);
+insert into t110 values (-2);
+insert into t110 values (-1);
+connection slave;
+select * from t110;
+f1
+-5
+-4
+-3
+-2
+-1
+create trigger trg110 before update on t110 /* slave local */
+for each row
+begin
+DECLARE r integer;
+SELECT f2 INTO r FROM t210 where f1=NEW.f1;
+INSERT INTO t310 values (r);
+end|
+create table t210 (f1 int, f2 int) /* slave local */;
+create table t310 (f3 int) /* slave local */;
+insert into t210 values (5, 5*100);
+insert into t210 values (4, 4*100);
+insert into t210 values (3, 3*100);
+insert into t210 values (2, 2*100);
+insert into t210 values (1, 1*100);
+select * from t210;
+f1 f2
+5 500
+4 400
+3 300
+2 200
+1 100
+UPDATE t110 SET f1=5 where f1=-5;
+SELECT * from t110 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
+f1
+5
+-4
+-3
+-2
+-1
+SELECT * from t310 /* must be f3 5*100 */;
+f3
+500
+connection master;
+UPDATE t110 SET f1=5 where f1=-5;
+UPDATE t110 SET f1=4 where f1=-4;
+UPDATE t110 SET f1=3 where f1=-3;
+UPDATE t110 SET f1=2 where f1=-2;
+UPDATE t110 SET f1=1 where f1=-1;
+connection slave;
+SELECT * from t110 /* must be f1 5 ... 1 */;
+f1
+5
+4
+3
+2
+1
+SELECT * from t310 /* must be f3 5 * 100 ... 100 */;
+f3
+500
+400
+300
+200
+100
+drop trigger trg110;
+drop table t210,t310;
+connection master;
+drop table t110;
+connection slave;
+connection master;
+-------------------
+9
+-------------------
+connection master;
+drop table if exists t19;
+connection slave;
+drop table if exists t29,t39;
+connection master;
+create table t19 (f1 int) /* 2 replicate */;
+insert into t19 values (-5);
+insert into t19 values (-4);
+insert into t19 values (-3);
+insert into t19 values (-2);
+insert into t19 values (-1);
+connection slave;
+select * from t19;
+f1
+-5
+-4
+-3
+-2
+-1
+create trigger trg19 before update on t19 /* slave local */
+for each row
+begin
+DECLARE r integer;
+SELECT f2 INTO r FROM t29 where f1=NEW.f1;
+INSERT INTO t39 values (r);
+end|
+create table t29 (f1 int, f2 int) /* slave local */;
+create table t39 (f3 int) /* slave local */;
+insert into t29 values (5, 5*100);
+insert into t29 values (4, 4*100);
+insert into t29 values (3, 3*100);
+insert into t29 values (2, 2*100);
+insert into t29 values (1, 1*100);
+select * from t29;
+f1 f2
+5 500
+4 400
+3 300
+2 200
+1 100
+UPDATE t19 SET f1=5 where f1=-5;
+SELECT * from t19 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
+f1
+5
+-4
+-3
+-2
+-1
+SELECT * from t39 /* must be f3 5*100 */;
+f3
+500
+connection master;
+UPDATE t19 SET f1=5 where f1=-5;
+UPDATE t19 SET f1=4 where f1=-4;
+UPDATE t19 SET f1=3 where f1=-3;
+UPDATE t19 SET f1=2 where f1=-2;
+UPDATE t19 SET f1=1 where f1=-1;
+connection slave;
+SELECT * from t19 /* must be f1 5 ... 1 */;
+f1
+5
+4
+3
+2
+1
+SELECT * from t39 /* must be f3 5 * 100 ... 100 */;
+f3
+500
+400
+300
+200
+100
+drop trigger trg19;
+drop table t29,t39;
+connection master;
+drop table t19;
+connection slave;
+connection master;
+-------------------
+8
+-------------------
+connection master;
+drop table if exists t18;
+connection slave;
+drop table if exists t28,t38;
+connection master;
+create table t18 (f1 int) /* 2 replicate */;
+insert into t18 values (-5);
+insert into t18 values (-4);
+insert into t18 values (-3);
+insert into t18 values (-2);
+insert into t18 values (-1);
+connection slave;
+select * from t18;
+f1
+-5
+-4
+-3
+-2
+-1
+create trigger trg18 before update on t18 /* slave local */
+for each row
+begin
+DECLARE r integer;
+SELECT f2 INTO r FROM t28 where f1=NEW.f1;
+INSERT INTO t38 values (r);
+end|
+create table t28 (f1 int, f2 int) /* slave local */;
+create table t38 (f3 int) /* slave local */;
+insert into t28 values (5, 5*100);
+insert into t28 values (4, 4*100);
+insert into t28 values (3, 3*100);
+insert into t28 values (2, 2*100);
+insert into t28 values (1, 1*100);
+select * from t28;
+f1 f2
+5 500
+4 400
+3 300
+2 200
+1 100
+UPDATE t18 SET f1=5 where f1=-5;
+SELECT * from t18 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
+f1
+5
+-4
+-3
+-2
+-1
+SELECT * from t38 /* must be f3 5*100 */;
+f3
+500
+connection master;
+UPDATE t18 SET f1=5 where f1=-5;
+UPDATE t18 SET f1=4 where f1=-4;
+UPDATE t18 SET f1=3 where f1=-3;
+UPDATE t18 SET f1=2 where f1=-2;
+UPDATE t18 SET f1=1 where f1=-1;
+connection slave;
+SELECT * from t18 /* must be f1 5 ... 1 */;
+f1
+5
+4
+3
+2
+1
+SELECT * from t38 /* must be f3 5 * 100 ... 100 */;
+f3
+500
+400
+300
+200
+100
+drop trigger trg18;
+drop table t28,t38;
+connection master;
+drop table t18;
+connection slave;
+connection master;
+-------------------
+7
+-------------------
+connection master;
+drop table if exists t17;
+connection slave;
+drop table if exists t27,t37;
+connection master;
+create table t17 (f1 int) /* 2 replicate */;
+insert into t17 values (-5);
+insert into t17 values (-4);
+insert into t17 values (-3);
+insert into t17 values (-2);
+insert into t17 values (-1);
+connection slave;
+select * from t17;
+f1
+-5
+-4
+-3
+-2
+-1
+create trigger trg17 before update on t17 /* slave local */
+for each row
+begin
+DECLARE r integer;
+SELECT f2 INTO r FROM t27 where f1=NEW.f1;
+INSERT INTO t37 values (r);
+end|
+create table t27 (f1 int, f2 int) /* slave local */;
+create table t37 (f3 int) /* slave local */;
+insert into t27 values (5, 5*100);
+insert into t27 values (4, 4*100);
+insert into t27 values (3, 3*100);
+insert into t27 values (2, 2*100);
+insert into t27 values (1, 1*100);
+select * from t27;
+f1 f2
+5 500
+4 400
+3 300
+2 200
+1 100
+UPDATE t17 SET f1=5 where f1=-5;
+SELECT * from t17 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
+f1
+5
+-4
+-3
+-2
+-1
+SELECT * from t37 /* must be f3 5*100 */;
+f3
+500
+connection master;
+UPDATE t17 SET f1=5 where f1=-5;
+UPDATE t17 SET f1=4 where f1=-4;
+UPDATE t17 SET f1=3 where f1=-3;
+UPDATE t17 SET f1=2 where f1=-2;
+UPDATE t17 SET f1=1 where f1=-1;
+connection slave;
+SELECT * from t17 /* must be f1 5 ... 1 */;
+f1
+5
+4
+3
+2
+1
+SELECT * from t37 /* must be f3 5 * 100 ... 100 */;
+f3
+500
+400
+300
+200
+100
+drop trigger trg17;
+drop table t27,t37;
+connection master;
+drop table t17;
+connection slave;
+connection master;
+-------------------
+6
+-------------------
+connection master;
+drop table if exists t16;
+connection slave;
+drop table if exists t26,t36;
+connection master;
+create table t16 (f1 int) /* 2 replicate */;
+insert into t16 values (-5);
+insert into t16 values (-4);
+insert into t16 values (-3);
+insert into t16 values (-2);
+insert into t16 values (-1);
+connection slave;
+select * from t16;
+f1
+-5
+-4
+-3
+-2
+-1
+create trigger trg16 before update on t16 /* slave local */
+for each row
+begin
+DECLARE r integer;
+SELECT f2 INTO r FROM t26 where f1=NEW.f1;
+INSERT INTO t36 values (r);
+end|
+create table t26 (f1 int, f2 int) /* slave local */;
+create table t36 (f3 int) /* slave local */;
+insert into t26 values (5, 5*100);
+insert into t26 values (4, 4*100);
+insert into t26 values (3, 3*100);
+insert into t26 values (2, 2*100);
+insert into t26 values (1, 1*100);
+select * from t26;
+f1 f2
+5 500
+4 400
+3 300
+2 200
+1 100
+UPDATE t16 SET f1=5 where f1=-5;
+SELECT * from t16 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
+f1
+5
+-4
+-3
+-2
+-1
+SELECT * from t36 /* must be f3 5*100 */;
+f3
+500
+connection master;
+UPDATE t16 SET f1=5 where f1=-5;
+UPDATE t16 SET f1=4 where f1=-4;
+UPDATE t16 SET f1=3 where f1=-3;
+UPDATE t16 SET f1=2 where f1=-2;
+UPDATE t16 SET f1=1 where f1=-1;
+connection slave;
+SELECT * from t16 /* must be f1 5 ... 1 */;
+f1
+5
+4
+3
+2
+1
+SELECT * from t36 /* must be f3 5 * 100 ... 100 */;
+f3
+500
+400
+300
+200
+100
+drop trigger trg16;
+drop table t26,t36;
+connection master;
+drop table t16;
+connection slave;
+connection master;
+-------------------
+5
+-------------------
+connection master;
+drop table if exists t15;
+connection slave;
+drop table if exists t25,t35;
+connection master;
+create table t15 (f1 int) /* 2 replicate */;
+insert into t15 values (-5);
+insert into t15 values (-4);
+insert into t15 values (-3);
+insert into t15 values (-2);
+insert into t15 values (-1);
+connection slave;
+select * from t15;
+f1
+-5
+-4
+-3
+-2
+-1
+create trigger trg15 before update on t15 /* slave local */
+for each row
+begin
+DECLARE r integer;
+SELECT f2 INTO r FROM t25 where f1=NEW.f1;
+INSERT INTO t35 values (r);
+end|
+create table t25 (f1 int, f2 int) /* slave local */;
+create table t35 (f3 int) /* slave local */;
+insert into t25 values (5, 5*100);
+insert into t25 values (4, 4*100);
+insert into t25 values (3, 3*100);
+insert into t25 values (2, 2*100);
+insert into t25 values (1, 1*100);
+select * from t25;
+f1 f2
+5 500
+4 400
+3 300
+2 200
+1 100
+UPDATE t15 SET f1=5 where f1=-5;
+SELECT * from t15 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
+f1
+5
+-4
+-3
+-2
+-1
+SELECT * from t35 /* must be f3 5*100 */;
+f3
+500
+connection master;
+UPDATE t15 SET f1=5 where f1=-5;
+UPDATE t15 SET f1=4 where f1=-4;
+UPDATE t15 SET f1=3 where f1=-3;
+UPDATE t15 SET f1=2 where f1=-2;
+UPDATE t15 SET f1=1 where f1=-1;
+connection slave;
+SELECT * from t15 /* must be f1 5 ... 1 */;
+f1
+5
+4
+3
+2
+1
+SELECT * from t35 /* must be f3 5 * 100 ... 100 */;
+f3
+500
+400
+300
+200
+100
+drop trigger trg15;
+drop table t25,t35;
+connection master;
+drop table t15;
+connection slave;
+connection master;
+-------------------
+4
+-------------------
+connection master;
+drop table if exists t14;
+connection slave;
+drop table if exists t24,t34;
+connection master;
+create table t14 (f1 int) /* 2 replicate */;
+insert into t14 values (-5);
+insert into t14 values (-4);
+insert into t14 values (-3);
+insert into t14 values (-2);
+insert into t14 values (-1);
+connection slave;
+select * from t14;
+f1
+-5
+-4
+-3
+-2
+-1
+create trigger trg14 before update on t14 /* slave local */
+for each row
+begin
+DECLARE r integer;
+SELECT f2 INTO r FROM t24 where f1=NEW.f1;
+INSERT INTO t34 values (r);
+end|
+create table t24 (f1 int, f2 int) /* slave local */;
+create table t34 (f3 int) /* slave local */;
+insert into t24 values (5, 5*100);
+insert into t24 values (4, 4*100);
+insert into t24 values (3, 3*100);
+insert into t24 values (2, 2*100);
+insert into t24 values (1, 1*100);
+select * from t24;
+f1 f2
+5 500
+4 400
+3 300
+2 200
+1 100
+UPDATE t14 SET f1=5 where f1=-5;
+SELECT * from t14 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
+f1
+5
+-4
+-3
+-2
+-1
+SELECT * from t34 /* must be f3 5*100 */;
+f3
+500
+connection master;
+UPDATE t14 SET f1=5 where f1=-5;
+UPDATE t14 SET f1=4 where f1=-4;
+UPDATE t14 SET f1=3 where f1=-3;
+UPDATE t14 SET f1=2 where f1=-2;
+UPDATE t14 SET f1=1 where f1=-1;
+connection slave;
+SELECT * from t14 /* must be f1 5 ... 1 */;
+f1
+5
+4
+3
+2
+1
+SELECT * from t34 /* must be f3 5 * 100 ... 100 */;
+f3
+500
+400
+300
+200
+100
+drop trigger trg14;
+drop table t24,t34;
+connection master;
+drop table t14;
+connection slave;
+connection master;
+-------------------
+3
+-------------------
+connection master;
+drop table if exists t13;
+connection slave;
+drop table if exists t23,t33;
+connection master;
+create table t13 (f1 int) /* 2 replicate */;
+insert into t13 values (-5);
+insert into t13 values (-4);
+insert into t13 values (-3);
+insert into t13 values (-2);
+insert into t13 values (-1);
+connection slave;
+select * from t13;
+f1
+-5
+-4
+-3
+-2
+-1
+create trigger trg13 before update on t13 /* slave local */
+for each row
+begin
+DECLARE r integer;
+SELECT f2 INTO r FROM t23 where f1=NEW.f1;
+INSERT INTO t33 values (r);
+end|
+create table t23 (f1 int, f2 int) /* slave local */;
+create table t33 (f3 int) /* slave local */;
+insert into t23 values (5, 5*100);
+insert into t23 values (4, 4*100);
+insert into t23 values (3, 3*100);
+insert into t23 values (2, 2*100);
+insert into t23 values (1, 1*100);
+select * from t23;
+f1 f2
+5 500
+4 400
+3 300
+2 200
+1 100
+UPDATE t13 SET f1=5 where f1=-5;
+SELECT * from t13 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
+f1
+5
+-4
+-3
+-2
+-1
+SELECT * from t33 /* must be f3 5*100 */;
+f3
+500
+connection master;
+UPDATE t13 SET f1=5 where f1=-5;
+UPDATE t13 SET f1=4 where f1=-4;
+UPDATE t13 SET f1=3 where f1=-3;
+UPDATE t13 SET f1=2 where f1=-2;
+UPDATE t13 SET f1=1 where f1=-1;
+connection slave;
+SELECT * from t13 /* must be f1 5 ... 1 */;
+f1
+5
+4
+3
+2
+1
+SELECT * from t33 /* must be f3 5 * 100 ... 100 */;
+f3
+500
+400
+300
+200
+100
+drop trigger trg13;
+drop table t23,t33;
+connection master;
+drop table t13;
+connection slave;
+connection master;
+-------------------
+2
+-------------------
+connection master;
+drop table if exists t12;
+connection slave;
+drop table if exists t22,t32;
+connection master;
+create table t12 (f1 int) /* 2 replicate */;
+insert into t12 values (-5);
+insert into t12 values (-4);
+insert into t12 values (-3);
+insert into t12 values (-2);
+insert into t12 values (-1);
+connection slave;
+select * from t12;
+f1
+-5
+-4
+-3
+-2
+-1
+create trigger trg12 before update on t12 /* slave local */
+for each row
+begin
+DECLARE r integer;
+SELECT f2 INTO r FROM t22 where f1=NEW.f1;
+INSERT INTO t32 values (r);
+end|
+create table t22 (f1 int, f2 int) /* slave local */;
+create table t32 (f3 int) /* slave local */;
+insert into t22 values (5, 5*100);
+insert into t22 values (4, 4*100);
+insert into t22 values (3, 3*100);
+insert into t22 values (2, 2*100);
+insert into t22 values (1, 1*100);
+select * from t22;
+f1 f2
+5 500
+4 400
+3 300
+2 200
+1 100
+UPDATE t12 SET f1=5 where f1=-5;
+SELECT * from t12 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
+f1
+5
+-4
+-3
+-2
+-1
+SELECT * from t32 /* must be f3 5*100 */;
+f3
+500
+connection master;
+UPDATE t12 SET f1=5 where f1=-5;
+UPDATE t12 SET f1=4 where f1=-4;
+UPDATE t12 SET f1=3 where f1=-3;
+UPDATE t12 SET f1=2 where f1=-2;
+UPDATE t12 SET f1=1 where f1=-1;
+connection slave;
+SELECT * from t12 /* must be f1 5 ... 1 */;
+f1
+5
+4
+3
+2
+1
+SELECT * from t32 /* must be f3 5 * 100 ... 100 */;
+f3
+500
+400
+300
+200
+100
+drop trigger trg12;
+drop table t22,t32;
+connection master;
+drop table t12;
+connection slave;
+connection master;
+-------------------
+1
+-------------------
+connection master;
+drop table if exists t11;
+connection slave;
+drop table if exists t21,t31;
+connection master;
+create table t11 (f1 int) /* 2 replicate */;
+insert into t11 values (-5);
+insert into t11 values (-4);
+insert into t11 values (-3);
+insert into t11 values (-2);
+insert into t11 values (-1);
+connection slave;
+select * from t11;
+f1
+-5
+-4
+-3
+-2
+-1
+create trigger trg11 before update on t11 /* slave local */
+for each row
+begin
+DECLARE r integer;
+SELECT f2 INTO r FROM t21 where f1=NEW.f1;
+INSERT INTO t31 values (r);
+end|
+create table t21 (f1 int, f2 int) /* slave local */;
+create table t31 (f3 int) /* slave local */;
+insert into t21 values (5, 5*100);
+insert into t21 values (4, 4*100);
+insert into t21 values (3, 3*100);
+insert into t21 values (2, 2*100);
+insert into t21 values (1, 1*100);
+select * from t21;
+f1 f2
+5 500
+4 400
+3 300
+2 200
+1 100
+UPDATE t11 SET f1=5 where f1=-5;
+SELECT * from t11 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;
+f1
+5
+-4
+-3
+-2
+-1
+SELECT * from t31 /* must be f3 5*100 */;
+f3
+500
+connection master;
+UPDATE t11 SET f1=5 where f1=-5;
+UPDATE t11 SET f1=4 where f1=-4;
+UPDATE t11 SET f1=3 where f1=-3;
+UPDATE t11 SET f1=2 where f1=-2;
+UPDATE t11 SET f1=1 where f1=-1;
+connection slave;
+SELECT * from t11 /* must be f1 5 ... 1 */;
+f1
+5
+4
+3
+2
+1
+SELECT * from t31 /* must be f3 5 * 100 ... 100 */;
+f3
+500
+400
+300
+200
+100
+drop trigger trg11;
+drop table t21,t31;
+connection master;
+drop table t11;
+connection slave;
+connection master;
+connection slave;
+STOP SLAVE;
+connection master;
+FLUSH LOGS;
+include/rpl_stop_server.inc [server_number=1]
+include/rpl_start_server.inc [server_number=1]
+--> Master binlog: Server ver: 5.0.16-debug-log, Binlog ver: 4
+connection slave;
+RESET SLAVE;
+START SLAVE;
+SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0;
+MASTER_POS_WAIT('master-bin.000001', 513) >= 0
+1
+SHOW TABLES LIKE 't_';
+Tables_in_test (t_)
+t1
+t2
+SHOW TRIGGERS;
+Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
+trg1 INSERT t1 INSERT INTO t2 VALUES(CURRENT_USER()) AFTER # latin1 latin1_swedish_ci latin1_swedish_ci
+SELECT * FROM t1;
+c
+1
+SELECT * FROM t2;
+s
+@
+INSERT INTO t1 VALUES(2);
+SELECT * FROM t1;
+c
+1
+2
+SELECT * FROM t2;
+s
+@
+root@localhost
+DROP TRIGGER trg1;
+DROP TABLE t1;
+DROP TABLE t2;
+STOP SLAVE;
+RESET SLAVE;
+connection master;
+SHOW TABLES LIKE 't_';
+Tables_in_test (t_)
+SHOW TRIGGERS;
+Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
+RESET MASTER;
+connection slave;
+START SLAVE;
+
+---> Test for BUG#20438
+
+---> Preparing environment...
+connection master;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+
+---> Synchronizing slave with master...
+connection slave;
+
+connection master;
+
+---> Creating objects...
+CREATE TABLE t1(c INT);
+CREATE TABLE t2(c INT);
+/*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
+FOR EACH ROW
+INSERT INTO t2 VALUES(NEW.c * 10) */;
+
+---> Inserting value...
+INSERT INTO t1 VALUES(1);
+
+---> Checking on master...
+SELECT * FROM t1;
+c
+1
+SELECT * FROM t2;
+c
+10
+
+---> Synchronizing slave with master...
+connection slave;
+
+---> Checking on slave...
+SELECT * FROM t1;
+c
+1
+SELECT * FROM t2;
+c
+10
+
+connection master;
+
+---> Cleaning up...
+DROP TABLE t1;
+DROP TABLE t2;
+connection slave;
+connection master;
+connection master;
+drop table if exists t1;
+create table t1(a int, b varchar(50));
+drop trigger not_a_trigger;
+ERROR HY000: Trigger does not exist
+drop trigger if exists not_a_trigger;
+Warnings:
+Note 1360 Trigger does not exist
+create trigger t1_bi before insert on t1
+for each row set NEW.b := "In trigger t1_bi";
+insert into t1 values (1, "a");
+drop trigger if exists t1_bi;
+insert into t1 values (2, "b");
+drop trigger if exists t1_bi;
+Warnings:
+Note 1360 Trigger does not exist
+insert into t1 values (3, "c");
+select * from t1;
+a b
+1 In trigger t1_bi
+2 b
+3 c
+connection slave;
+select * from t1;
+a b
+1 In trigger t1_bi
+2 b
+3 c
+connection master;
+drop table t1;
+connection slave;
+connection master;
+include/rpl_reset.inc
+connection slave;
+connection master;
+create table t1 ( f int ) engine = innodb;
+create table log ( r int ) engine = myisam;
+create trigger tr
+after insert on t1
+for each row insert into log values ( new.f );
+set autocommit = 0;
+insert into t1 values ( 1 );
+rollback;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+connection slave;
+include/diff_tables.inc [master:t1, slave:t1]
+include/diff_tables.inc [master:log, slave:log]
+connection master;
+drop table t1, log;
+connection slave;
+connection master;
+drop trigger if exists notexistent;
+Warnings:
+Note 1360 Trigger does not exist
+include/show_binlog_events.inc
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Gtid # # GTID #-#-#
+master-bin.000001 # Query # # use `test`; drop trigger if exists notexistent
+connection slave;
+include/show_binlog_events.inc
+Log_name Pos Event_type Server_id End_log_pos Info
+slave-bin.000001 # Gtid # # GTID #-#-#
+slave-bin.000001 # Query # # use `test`; drop trigger if exists notexistent
+connection master;
+create table t1 (a int primary key, b int);
+create trigger tr3
+before insert on t1
+for each row set NEW.b := (NEW.b+1)*4;
+create trigger tr1
+before insert on t1
+for each row precedes tr3 set NEW.b := (NEW.b+1)*2;
+create trigger tr2
+before insert on t1
+for each row follows tr1 set NEW.b := (NEW.b+1)*3;
+show triggers;
+Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
+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 latin1_swedish_ci
+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 latin1_swedish_ci
+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 latin1_swedish_ci
+insert into t1 values (1,1);
+select * from t1;
+a b
+1 64
+connection slave;
+show triggers;
+Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
+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 latin1_swedish_ci
+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 latin1_swedish_ci
+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 latin1_swedish_ci
+select * from t1;
+a b
+1 64
+connection master;
+drop table t1;
+CREATE TABLE t1 (i INT);
+CREATE OR REPLACE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW SET @a=1;
+CREATE OR REPLACE TRIGGER tr BEFORE DELETE ON t1 FOR EACH ROW PRECEDES non_existing_trigger SET @a=2;
+ERROR HY000: Referenced trigger 'non_existing_trigger' for the given action time and event type does not exist
+SHOW TRIGGERS;
+Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
+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 latin1_swedish_ci
+connection slave;
+SHOW TRIGGERS;
+Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
+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 latin1_swedish_ci
+connection master;
+drop table t1;
+include/rpl_end.inc
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-10 09:21:16 +0000