diff options
Diffstat (limited to 'mysql-test/suite/rpl/t/rpl_row_triggers.test')
| -rw-r--r-- | mysql-test/suite/rpl/t/rpl_row_triggers.test | 442 |
1 files changed, 442 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/t/rpl_row_triggers.test b/mysql-test/suite/rpl/t/rpl_row_triggers.test new file mode 100644 index 00000000..68002dbe --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_triggers.test @@ -0,0 +1,442 @@ +-- source include/have_binlog_format_row.inc +-- source include/have_innodb.inc +-- source include/master-slave.inc + +-- echo # 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; + +sync_slave_with_master; + +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; +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; + +connection master; +--echo # INSERT triggers test +insert into t1 values ('a','b'); + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +connection master; + +--echo # UPDATE triggers test +update t1 set C1= 'd'; + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +connection master; +--echo # DELETE triggers test +delete from t1 where C1='d'; + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +--echo # INSERT triggers causing DELETE + INSERT (on unique key conflict) +insert into t1 values ('0','1'); + +SELECT * FROM t2 order by id; + +connection master; + +insert into t1 values ('0','1'); + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + + +--echo # INSERT triggers which cause also DELETE test +--echo # (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'); + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +connection master; + +drop table t3,t1; + +sync_slave_with_master; + +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; + +--sync_slave_with_master + +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; + +--sync_slave_with_master +select * from t2; +SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr; + +--connection master +drop tables t2,t1; + +--sync_slave_with_master + +-- echo # 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; + +create trigger t1_dummy before delete on t1 for each row + set @dummy= 1; + +sync_slave_with_master; + +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; +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; + +connection master; +--echo # INSERT triggers test +insert into t1 values ('a','b'); + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +connection master; + +--echo # UPDATE triggers test +update t1 set C1= 'd'; + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +connection master; +--echo # DELETE triggers test +delete from t1 where C1='d'; + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +--echo # INSERT triggers which cause also UPDATE test (insert duplicate row) +insert into t1 values ('0','1'); + +SELECT * FROM t2 order by id; + +connection master; + +insert into t1 values ('0','1'); + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + + +--echo # INSERT triggers which cause also DELETE test +--echo # (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'); + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +connection master; + +drop table t3,t1; + +sync_slave_with_master; + +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; + +--echo # +--echo # MDEV-5513: Trigger is applied to the rows after first one +--echo # + +--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; + +sync_slave_with_master; +--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); + +--sync_slave_with_master + +select * from t1; + +# Cleanup + +set global slave_run_triggers_for_rbr = @slave_run_triggers_for_rbr.saved; + +--connection master + +drop table t1, tlog; + +sync_slave_with_master; + + +--echo # +--echo # MDEV-8411 Assertion `is_stat_field || !table || (!table->write_set || +--echo # bitmap_is_set(table->write_set, field_index) || +--echo # bitmap_is_set(table->vcol_set, field_index))' +--echo # failed in Field_timestamp::store_TIME_with_warning +--echo # +--echo # +--echo # Create table on master, replicate it on slave. +--echo # +--connection master +set @binlog_row_image.saved = @@binlog_row_image; +set binlog_row_image = MINIMAL; +create table t1 (pk int primary key, f int); + +--sync_slave_with_master +--echo # +--echo # Create a trigger on the slave. +--echo # +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 +--echo # +--echo # Update the table to have the trigger fire on the slave., +--echo # +insert into t1 values (1,1),(2,2); +update t1 set pk=pk+10; +select * from t1; + +--sync_slave_with_master +--echo # +--echo # Check to see if slave has the table updated. +--echo # +select * from t1; + +--echo # +--echo # Cleanup +--echo # + +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; + +--sync_slave_with_master + +--echo # +--echo # enterprise 10.4 tests start +--echo # + +--echo # +--echo # MENT-607 : Make slave_run_triggers_for_rbr enforce triggers to run +--echo # on slave, even when there are triggers on the master +--echo # + +--echo # 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; + +create trigger t1_dummy before delete on t1 for each row + set @dummy= 1; + +sync_slave_with_master; + +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; +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; + +connection master; +--echo # INSERT triggers test +insert into t1 values ('a','b'); + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +connection master; + +--echo # UPDATE triggers test +update t1 set C1= 'd'; + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +connection master; +--echo # DELETE triggers test +delete from t1 where C1='d'; + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +--echo # INSERT triggers which cause also UPDATE test (insert duplicate row) +insert into t1 values ('0','1'); + +SELECT * FROM t2 order by id; + +connection master; + +insert into t1 values ('0','1'); + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + + +--echo # INSERT triggers which cause also DELETE test +--echo # (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'); + +sync_slave_with_master; + +connection slave; +SELECT * FROM t2 order by id; + +connection master; + +drop table t3,t1; + +sync_slave_with_master; + +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; + +--echo # +--echo # enterprise 10.4 tests end +--echo # + +--source include/rpl_end.inc |