mirror of
https://github.com/MariaDB/server.git
synced 2025-08-08 11:22:35 +03:00
84da20e658
This is actually an existing problem in the old binlog implementation, and this patch is applicable to old binlog also. The problem is that RESET MASTER can run concurrently with binlog dump threads / connected slaves. This will remove the binlog from under the feet of the reader, which can cause all sorts of strange behaviour. This patch fixes the problem by disallowing to run RESET MASTER when dump threads (or other RESET MASTER or SHOW BINARY LOGS) are running. An error is thrown in this case, user must stop slaves and/or kill dump threads to make the RESET MASTER go through. A slave that connects in the middle of RESET MASTER will wait for it to complete. Fix a lot of test cases to kill any lingering dump threads before doing RESET MASTER, mostly just by sourcing include/kill_binlog_dump_threads.inc. Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
1176 lines
25 KiB
Plaintext
1176 lines
25 KiB
Plaintext
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
|
|
include/kill_binlog_dump_threads.inc
|
|
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
|