mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
980108ceeb
A bit changed patch of Anders Karlsson with examples added. New parameters "ENFORCE" to slave-run-triggers-for-rbr added.
473 lines
11 KiB
Text
473 lines
11 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
# Test of row replication with triggers on the slave side
|
|
connection master;
|
|
CREATE TABLE t1 (C1 CHAR(1) primary key, C2 CHAR(1)) engine=innodb;
|
|
SELECT * FROM t1;
|
|
C1 C2
|
|
connection slave;
|
|
connection slave;
|
|
SET @old_slave_exec_mode= @@global.slave_exec_mode;
|
|
SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr;
|
|
SET @@global.slave_exec_mode= IDEMPOTENT;
|
|
SET @@global.slave_run_triggers_for_rbr= YES;
|
|
SELECT * FROM t1;
|
|
C1 C2
|
|
create table t2 (id char(2) primary key, cnt int, o char(1), n char(1));
|
|
insert into t2 values
|
|
('u0', 0, ' ', ' '),('u1', 0, ' ', ' '),
|
|
('d0', 0, ' ', ' '),('d1', 0, ' ', ' '),
|
|
('i0', 0, ' ', ' '),('i1', 0, ' ', ' ');
|
|
create trigger t1_cnt_b before update on t1 for each row
|
|
update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u0';
|
|
create trigger t1_cnt_db before delete on t1 for each row
|
|
update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd0';
|
|
create trigger t1_cnt_ib before insert on t1 for each row
|
|
update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i0';
|
|
create trigger t1_cnt_a after update on t1 for each row
|
|
update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u1';
|
|
create trigger t1_cnt_da after delete on t1 for each row
|
|
update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd1';
|
|
create trigger t1_cnt_ia after insert on t1 for each row
|
|
update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i1';
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 0
|
|
i1 0
|
|
u0 0
|
|
u1 0
|
|
connection master;
|
|
# INSERT triggers test
|
|
insert into t1 values ('a','b');
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 1 a
|
|
i1 1 a
|
|
u0 0
|
|
u1 0
|
|
connection master;
|
|
# UPDATE triggers test
|
|
update t1 set C1= 'd';
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 1 a
|
|
i1 1 a
|
|
u0 1 a d
|
|
u1 1 a d
|
|
connection master;
|
|
# DELETE triggers test
|
|
delete from t1 where C1='d';
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 1 d
|
|
d1 1 d
|
|
i0 1 a
|
|
i1 1 a
|
|
u0 1 a d
|
|
u1 1 a d
|
|
# INSERT triggers causing DELETE + INSERT (on unique key conflict)
|
|
insert into t1 values ('0','1');
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 1 d
|
|
d1 1 d
|
|
i0 2 0
|
|
i1 2 0
|
|
u0 1 a d
|
|
u1 1 a d
|
|
connection master;
|
|
insert into t1 values ('0','1');
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 2 0
|
|
d1 2 0
|
|
i0 3 0
|
|
i1 3 0
|
|
u0 1 a d
|
|
u1 1 a d
|
|
# INSERT triggers which cause also DELETE test
|
|
# (insert duplicate row in table referenced by foreign key)
|
|
insert into t1 values ('1','1');
|
|
connection master;
|
|
CREATE TABLE t3 (C1 CHAR(1) primary key, FOREIGN KEY (C1) REFERENCES t1(C1) ) engine=innodb;
|
|
insert into t1 values ('1','1');
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 3 1
|
|
d1 3 1
|
|
i0 5 1
|
|
i1 5 1
|
|
u0 1 a d
|
|
u1 1 a d
|
|
connection master;
|
|
drop table t3,t1;
|
|
connection slave;
|
|
connection slave;
|
|
SET @@global.slave_exec_mode= @old_slave_exec_mode;
|
|
SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr;
|
|
drop table t2;
|
|
connection master;
|
|
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (i INT) ENGINE=InnoDB;
|
|
connection slave;
|
|
SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr;
|
|
SET GLOBAL slave_run_triggers_for_rbr=YES;
|
|
CREATE TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW
|
|
INSERT INTO t2 VALUES (new.i);
|
|
connection master;
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t1 VALUES (2);
|
|
COMMIT;
|
|
connection slave;
|
|
select * from t2;
|
|
i
|
|
1
|
|
2
|
|
SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr;
|
|
connection master;
|
|
drop tables t2,t1;
|
|
connection slave;
|
|
# Triggers on slave do not work if master has some
|
|
connection master;
|
|
CREATE TABLE t1 (C1 CHAR(1) primary key, C2 CHAR(1)) engine=innodb;
|
|
SELECT * FROM t1;
|
|
C1 C2
|
|
create trigger t1_dummy before delete on t1 for each row
|
|
set @dummy= 1;
|
|
connection slave;
|
|
connection slave;
|
|
SET @old_slave_exec_mode= @@global.slave_exec_mode;
|
|
SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr;
|
|
SET @@global.slave_exec_mode= IDEMPOTENT;
|
|
SET @@global.slave_run_triggers_for_rbr= YES;
|
|
SELECT * FROM t1;
|
|
C1 C2
|
|
create table t2 (id char(2) primary key, cnt int, o char(1), n char(1));
|
|
insert into t2 values
|
|
('u0', 0, ' ', ' '),('u1', 0, ' ', ' '),
|
|
('d0', 0, ' ', ' '),('d1', 0, ' ', ' '),
|
|
('i0', 0, ' ', ' '),('i1', 0, ' ', ' ');
|
|
create trigger t1_cnt_b before update on t1 for each row
|
|
update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u0';
|
|
create trigger t1_cnt_ib before insert on t1 for each row
|
|
update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i0';
|
|
create trigger t1_cnt_a after update on t1 for each row
|
|
update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u1';
|
|
create trigger t1_cnt_da after delete on t1 for each row
|
|
update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd1';
|
|
create trigger t1_cnt_ia after insert on t1 for each row
|
|
update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i1';
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 0
|
|
i1 0
|
|
u0 0
|
|
u1 0
|
|
connection master;
|
|
# INSERT triggers test
|
|
insert into t1 values ('a','b');
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 0
|
|
i1 0
|
|
u0 0
|
|
u1 0
|
|
connection master;
|
|
# UPDATE triggers test
|
|
update t1 set C1= 'd';
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 0
|
|
i1 0
|
|
u0 0
|
|
u1 0
|
|
connection master;
|
|
# DELETE triggers test
|
|
delete from t1 where C1='d';
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 0
|
|
i1 0
|
|
u0 0
|
|
u1 0
|
|
# INSERT triggers which cause also UPDATE test (insert duplicate row)
|
|
insert into t1 values ('0','1');
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 1 0
|
|
i1 1 0
|
|
u0 0
|
|
u1 0
|
|
connection master;
|
|
insert into t1 values ('0','1');
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 1 0
|
|
i1 1 0
|
|
u0 0
|
|
u1 0
|
|
# INSERT triggers which cause also DELETE test
|
|
# (insert duplicate row in table referenced by foreign key)
|
|
insert into t1 values ('1','1');
|
|
connection master;
|
|
CREATE TABLE t3 (C1 CHAR(1) primary key, FOREIGN KEY (C1) REFERENCES t1(C1) ) engine=innodb;
|
|
insert into t1 values ('1','1');
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 2 1
|
|
i1 2 1
|
|
u0 0
|
|
u1 0
|
|
connection master;
|
|
drop table t3,t1;
|
|
connection slave;
|
|
connection slave;
|
|
SET @@global.slave_exec_mode= @old_slave_exec_mode;
|
|
SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr;
|
|
drop table t2;
|
|
#
|
|
# MDEV-5513: Trigger is applied to the rows after first one
|
|
#
|
|
connection master;
|
|
create table t1 (a int, b int);
|
|
create table tlog (a int);
|
|
set sql_log_bin=0;
|
|
create trigger tr1 after insert on t1 for each row insert into tlog values (1);
|
|
set sql_log_bin=1;
|
|
connection slave;
|
|
connection slave;
|
|
set @slave_run_triggers_for_rbr.saved = @@slave_run_triggers_for_rbr;
|
|
set global slave_run_triggers_for_rbr=1;
|
|
create trigger tr2 before insert on t1 for each row set new.b = new.a;
|
|
connection master;
|
|
insert into t1 values (1,10),(2,20),(3,30);
|
|
connection slave;
|
|
select * from t1;
|
|
a b
|
|
1 10
|
|
2 20
|
|
3 30
|
|
set global slave_run_triggers_for_rbr = @slave_run_triggers_for_rbr.saved;
|
|
connection master;
|
|
drop table t1, tlog;
|
|
connection slave;
|
|
#
|
|
# MDEV-8411 Assertion `is_stat_field || !table || (!table->write_set ||
|
|
# bitmap_is_set(table->write_set, field_index) ||
|
|
# bitmap_is_set(table->vcol_set, field_index))'
|
|
# failed in Field_timestamp::store_TIME_with_warning
|
|
#
|
|
#
|
|
# Create table on master, replicate it on slave.
|
|
#
|
|
connection master;
|
|
set @binlog_row_image.saved = @@binlog_row_image;
|
|
set binlog_row_image = MINIMAL;
|
|
create table t1 (pk int primary key, f int);
|
|
connection slave;
|
|
#
|
|
# Create a trigger on the slave.
|
|
#
|
|
create trigger tr before update on t1 for each row set new.f = 1000;
|
|
set @old_slave_run_triggers_for_rbr = @@global.slave_run_triggers_for_rbr;
|
|
set global slave_run_triggers_for_rbr = YES;
|
|
connection master;
|
|
#
|
|
# Update the table to have the trigger fire on the slave.,
|
|
#
|
|
insert into t1 values (1,1),(2,2);
|
|
update t1 set pk=pk+10;
|
|
select * from t1;
|
|
pk f
|
|
11 1
|
|
12 2
|
|
connection slave;
|
|
#
|
|
# Check to see if slave has the table updated.
|
|
#
|
|
select * from t1;
|
|
pk f
|
|
11 1000
|
|
12 1000
|
|
#
|
|
# Cleanup
|
|
#
|
|
set global slave_run_triggers_for_rbr = @old_slave_run_triggers_for_rbr;
|
|
connection master;
|
|
set binlog_row_image = @binlog_row_image.saved;
|
|
drop table t1;
|
|
connection slave;
|
|
#
|
|
# enterprise 10.4 tests start
|
|
#
|
|
#
|
|
# MENT-607 : Make slave_run_triggers_for_rbr enforce triggers to run
|
|
# on slave, even when there are triggers on the master
|
|
#
|
|
# Triggers on slave WILL work (with ENFORCE) if master has some
|
|
connection master;
|
|
CREATE TABLE t1 (C1 CHAR(1) primary key, C2 CHAR(1)) engine=innodb;
|
|
SELECT * FROM t1;
|
|
C1 C2
|
|
create trigger t1_dummy before delete on t1 for each row
|
|
set @dummy= 1;
|
|
connection slave;
|
|
connection slave;
|
|
SET @old_slave_exec_mode= @@global.slave_exec_mode;
|
|
SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr;
|
|
SET @@global.slave_exec_mode= IDEMPOTENT;
|
|
SET @@global.slave_run_triggers_for_rbr= ENFORCE;
|
|
SELECT * FROM t1;
|
|
C1 C2
|
|
create table t2 (id char(2) primary key, cnt int, o char(1), n char(1));
|
|
insert into t2 values
|
|
('u0', 0, ' ', ' '),('u1', 0, ' ', ' '),
|
|
('d0', 0, ' ', ' '),('d1', 0, ' ', ' '),
|
|
('i0', 0, ' ', ' '),('i1', 0, ' ', ' ');
|
|
create trigger t1_cnt_b before update on t1 for each row
|
|
update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u0';
|
|
create trigger t1_cnt_ib before insert on t1 for each row
|
|
update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i0';
|
|
create trigger t1_cnt_a after update on t1 for each row
|
|
update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u1';
|
|
create trigger t1_cnt_da after delete on t1 for each row
|
|
update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd1';
|
|
create trigger t1_cnt_ia after insert on t1 for each row
|
|
update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i1';
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 0
|
|
i1 0
|
|
u0 0
|
|
u1 0
|
|
connection master;
|
|
# INSERT triggers test
|
|
insert into t1 values ('a','b');
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 1 a
|
|
i1 1 a
|
|
u0 0
|
|
u1 0
|
|
connection master;
|
|
# UPDATE triggers test
|
|
update t1 set C1= 'd';
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 0
|
|
i0 1 a
|
|
i1 1 a
|
|
u0 1 a d
|
|
u1 1 a d
|
|
connection master;
|
|
# DELETE triggers test
|
|
delete from t1 where C1='d';
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 1 d
|
|
i0 1 a
|
|
i1 1 a
|
|
u0 1 a d
|
|
u1 1 a d
|
|
# INSERT triggers which cause also UPDATE test (insert duplicate row)
|
|
insert into t1 values ('0','1');
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 1 d
|
|
i0 2 0
|
|
i1 2 0
|
|
u0 1 a d
|
|
u1 1 a d
|
|
connection master;
|
|
insert into t1 values ('0','1');
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 2 0
|
|
i0 3 0
|
|
i1 3 0
|
|
u0 1 a d
|
|
u1 1 a d
|
|
# INSERT triggers which cause also DELETE test
|
|
# (insert duplicate row in table referenced by foreign key)
|
|
insert into t1 values ('1','1');
|
|
connection master;
|
|
CREATE TABLE t3 (C1 CHAR(1) primary key, FOREIGN KEY (C1) REFERENCES t1(C1) ) engine=innodb;
|
|
insert into t1 values ('1','1');
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t2 order by id;
|
|
id cnt o n
|
|
d0 0
|
|
d1 3 1
|
|
i0 5 1
|
|
i1 5 1
|
|
u0 1 a d
|
|
u1 1 a d
|
|
connection master;
|
|
drop table t3,t1;
|
|
connection slave;
|
|
connection slave;
|
|
SET @@global.slave_exec_mode= @old_slave_exec_mode;
|
|
SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr;
|
|
drop table t2;
|
|
#
|
|
# enterprise 10.4 tests end
|
|
#
|
|
include/rpl_end.inc
|