From af3180ab6fa13b58fa4a4a2b00a89f2969dfa68d Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 19 Mar 2014 11:00:56 +0200 Subject: MDEV-9095: Executing triggers on slave in row-based replication --- mysql-test/include/have_rbr_triggers.inc | 5 + mysql-test/r/mysqld--help.result | 1 + mysql-test/suite/rpl/r/rpl_row_triggers.result | 240 ++++++++++++++++++ mysql-test/suite/rpl/r/rpl_row_triggers_sbr.result | 14 ++ mysql-test/suite/rpl/t/rpl_row_triggers.test | 280 +++++++++++++++++++++ mysql-test/suite/rpl/t/rpl_row_triggers_sbr.test | 43 ++++ 6 files changed, 583 insertions(+) create mode 100644 mysql-test/include/have_rbr_triggers.inc create mode 100644 mysql-test/suite/rpl/r/rpl_row_triggers.result create mode 100644 mysql-test/suite/rpl/r/rpl_row_triggers_sbr.result create mode 100644 mysql-test/suite/rpl/t/rpl_row_triggers.test create mode 100644 mysql-test/suite/rpl/t/rpl_row_triggers_sbr.test (limited to 'mysql-test') diff --git a/mysql-test/include/have_rbr_triggers.inc b/mysql-test/include/have_rbr_triggers.inc new file mode 100644 index 00000000000..9ccfc18bfde --- /dev/null +++ b/mysql-test/include/have_rbr_triggers.inc @@ -0,0 +1,5 @@ +if (`select count(*) = 0 from information_schema.session_variables where variable_name = 'slave_run_triggers_for_rbr'`) +{ + skip RBR triggers are not available; +} + diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 5e37fde47a7..c123d305bc7 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1301,6 +1301,7 @@ slave-max-allowed-packet 1073741824 slave-net-timeout 3600 slave-parallel-max-queued 131072 slave-parallel-threads 0 +slave-run-triggers-for-rbr NO slave-skip-errors (No default value) slave-sql-verify-checksum TRUE slave-transaction-retries 10 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 00000000000..82dbc727993 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_triggers.result @@ -0,0 +1,240 @@ +include/master-slave.inc +[connection master] +# Test of row replication with triggers on the slave side +CREATE TABLE t1 (C1 CHAR(1) primary key, C2 CHAR(1)) engine=innodb; +SELECT * FROM t1; +C1 C2 +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 +# INSERT triggers test +insert into t1 values ('a','b'); +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 1 a +i1 1 a +u0 0 +u1 0 +# UPDATE triggers test +update t1 set C1= 'd'; +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 +# DELETE triggers test +delete from t1 where C1='d'; +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 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 1 d +d1 1 d +i0 2 0 +i1 2 0 +u0 1 a d +u1 1 a d +insert into t1 values ('0','1'); +SELECT * FROM t2 order by id; +id cnt o n +d0 1 d +d1 1 d +i0 3 0 +i1 3 0 +u0 2 0 0 +u1 2 0 0 +# INSERT triggers which cause also DELETE test +# (insert duplicate row in table referenced by foreign key) +insert into t1 values ('1','1'); +CREATE TABLE t3 (C1 CHAR(1) primary key, FOREIGN KEY (C1) REFERENCES t1(C1) ) engine=innodb; +insert into t1 values ('1','1'); +SELECT * FROM t2 order by id; +id cnt o n +d0 2 1 +d1 2 1 +i0 5 1 +i1 5 1 +u0 2 0 0 +u1 2 0 0 +drop table t3,t1; +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; +CREATE TABLE t1 (i INT) ENGINE=InnoDB; +CREATE TABLE t2 (i INT) ENGINE=InnoDB; +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); +BEGIN; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +COMMIT; +select * from t2; +i +1 +2 +SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr; +drop tables t2,t1; +# Triggers on slave do not work if master has some +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; +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 +# INSERT triggers test +insert into t1 values ('a','b'); +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 0 +i1 0 +u0 0 +u1 0 +# UPDATE triggers test +update t1 set C1= 'd'; +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 0 +i1 0 +u0 0 +u1 0 +# DELETE triggers test +delete from t1 where C1='d'; +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 +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 +# INSERT triggers which cause also DELETE test +# (insert duplicate row in table referenced by foreign key) +insert into t1 values ('1','1'); +CREATE TABLE t3 (C1 CHAR(1) primary key, FOREIGN KEY (C1) REFERENCES t1(C1) ) engine=innodb; +insert into t1 values ('1','1'); +SELECT * FROM t2 order by id; +id cnt o n +d0 0 +d1 0 +i0 2 1 +i1 2 1 +u0 0 +u1 0 +drop table t3,t1; +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 +# +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; +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; +insert into t1 values (1,10),(2,20),(3,30); +select * from t1; +a b +1 10 +2 20 +3 30 +set global slave_run_triggers_for_rbr = @slave_run_triggers_for_rbr.saved; +drop table t1, tlog; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_triggers_sbr.result b/mysql-test/suite/rpl/r/rpl_row_triggers_sbr.result new file mode 100644 index 00000000000..96197393de9 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_triggers_sbr.result @@ -0,0 +1,14 @@ +include/master-slave.inc +[connection master] +set binlog_format = row; +create table t1 (i int); +create table t2 (i int); +SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr; +set global slave_run_triggers_for_rbr=YES; +create trigger tr_before before insert on t1 for each row +insert into t2 values (1); +insert into t1 values (1); +include/wait_for_slave_sql_error_and_skip.inc [errno=1666] +drop tables t1,t2; +SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr; +include/rpl_end.inc 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 00000000000..3a884727325 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_triggers.test @@ -0,0 +1,280 @@ +-- source include/have_binlog_format_row.inc +-- source include/have_rbr_triggers.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 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; + +--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; + + + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_triggers_sbr.test b/mysql-test/suite/rpl/t/rpl_row_triggers_sbr.test new file mode 100644 index 00000000000..a801363b931 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_triggers_sbr.test @@ -0,0 +1,43 @@ +--source include/have_binlog_format_statement.inc +--source include/have_rbr_triggers.inc +--source include/master-slave.inc + +--disable_query_log +CALL mtr.add_suppression("Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT"); +--enable_query_log + +set binlog_format = row; + +create table t1 (i int); +create table t2 (i int); + +--sync_slave_with_master +--disable_query_log +CALL mtr.add_suppression("impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT"); +--enable_query_log + +SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr; +set global slave_run_triggers_for_rbr=YES; + +create trigger tr_before before insert on t1 for each row + insert into t2 values (1); + +--connection master + +insert into t1 values (1); + +--connection slave + +--let $slave_sql_errno= 1666 +--source include/wait_for_slave_sql_error_and_skip.inc + +--connection master + +drop tables t1,t2; + +--sync_slave_with_master +SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr; + +--connection master + +--source include/rpl_end.inc -- cgit v1.2.1