rpl/t/rpl_row_triggers.test

-- 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

--source include/rpl_end.inc