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