diff options
Diffstat (limited to 'mysql-test/suite/rpl/r/rpl_row_triggers.result')
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_row_triggers.result | 473 |
1 files changed, 473 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/r/rpl_row_triggers.result b/mysql-test/suite/rpl/r/rpl_row_triggers.result new file mode 100644 index 00000000..07a66d13 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_triggers.result @@ -0,0 +1,473 @@ +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 |