diff options
Diffstat (limited to 'mysql-test/suite/rpl/r/rpl_trigger.result')
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_trigger.result | 1174 |
1 files changed, 1174 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..f4f700ac --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_trigger.result @@ -0,0 +1,1174 @@ +include/master-slave.inc +[connection master] +connection slave; +include/stop_slave.inc +CHANGE MASTER TO MASTER_USE_GTID=NO; +include/start_slave.inc +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; +include/reset_slave.inc +Warnings: +Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'No' to 'Slave_Pos' +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; +include/reset_slave.inc +Warnings: +Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'No' to 'Slave_Pos' +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 +Warnings: +Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'No' to 'Slave_Pos' +connection slave; +include/stop_slave.inc +CHANGE MASTER TO MASTER_USE_GTID=NO; +include/start_slave.inc +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 |