include/master-slave.inc [connection master] # Test case1: INVOKES A TRIGGER with after insert action connection master; create table t1(a int, b int) engine=innodb; create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create trigger tr1 after insert on t1 for each row insert into t2(a) values(6); create table t3(a int, b int) engine=innodb; create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create table t5(a int) engine=innodb; create trigger tr2 after insert on t3 for each row begin insert into t4(a) values(f1_insert_triggered()); insert into t4(a) values(f1_insert_triggered()); insert into t5(a) values(8); end | create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER BEGIN INSERT INTO t6(a) values(2),(3); RETURN 1; END// begin; insert into t1(a,b) values(1,1),(2,1); insert into t3(a,b) values(1,1),(2,1); update t1 set a = a + 5 where b = 1; update t3 set a = a + 5 where b = 1; delete from t1 where b = 1; delete from t3 where b = 1; connection master1; insert into t2(a) values(3); insert into t4(a) values(3); connection master; commit; insert into t1(a,b) values(4,2); insert into t3(a,b) values(4,2); update t1 set a = a + 5 where b = 2; update t3 set a = a + 5 where b = 2; delete from t1 where b = 2; delete from t3 where b = 2; # To verify if insert/update in an autoinc column causes statement to be logged in row format include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=5 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t1(a,b) values(1,1),(2,1) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Annotate_rows # # insert into t3(a,b) values(1,1),(2,1) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t1(a,b) values(4,2) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t3(a,b) values(4,2) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ commit; connection master; connection slave; #Test if the results are consistent on master and slave #for 'INVOKES A TRIGGER with after insert action' include/diff_tables.inc [master:t2, slave:t2] include/diff_tables.inc [master:t4, slave:t4] include/diff_tables.inc [master:t6, slave:t6] connection master; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; DROP TABLE t5; DROP TABLE t6; DROP FUNCTION f1_insert_triggered; connection slave; # Test case2: INVOKES A TRIGGER with before insert action connection master; create table t1(a int, b int) engine=innodb; create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create trigger tr1 before insert on t1 for each row insert into t2(a) values(6); create table t3(a int, b int) engine=innodb; create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create table t5(a int) engine=innodb; create trigger tr2 before insert on t3 for each row begin insert into t4(a) values(f1_insert_triggered()); insert into t4(a) values(f1_insert_triggered()); insert into t5(a) values(8); end | create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER BEGIN INSERT INTO t6(a) values(2),(3); RETURN 1; END// begin; insert into t1(a,b) values(1,1),(2,1); insert into t3(a,b) values(1,1),(2,1); update t1 set a = a + 5 where b = 1; update t3 set a = a + 5 where b = 1; delete from t1 where b = 1; delete from t3 where b = 1; connection master1; insert into t2(a) values(3); insert into t4(a) values(3); connection master; commit; insert into t1(a,b) values(4,2); insert into t3(a,b) values(4,2); update t1 set a = a + 5 where b = 2; update t3 set a = a + 5 where b = 2; delete from t1 where b = 2; delete from t3 where b = 2; # To verify if insert/update in an autoinc column causes statement to be logged in row format include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=5 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t2(a) values(6) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t2(a) values(6) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ commit; connection master; connection slave; #Test if the results are consistent on master and slave #for 'INVOKES A TRIGGER with before insert action' include/diff_tables.inc [master:t2, slave:t2] include/diff_tables.inc [master:t4, slave:t4] include/diff_tables.inc [master:t6, slave:t6] connection master; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; DROP TABLE t5; DROP TABLE t6; DROP FUNCTION f1_insert_triggered; connection slave; # Test case3: INVOKES A TRIGGER with after update action connection master; create table t1(a int, b int) engine=innodb; create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create trigger tr1 after update on t1 for each row insert into t2(a) values(6); create table t3(a int, b int) engine=innodb; create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create table t5(a int) engine=innodb; create trigger tr2 after update on t3 for each row begin insert into t4(a) values(f1_insert_triggered()); insert into t4(a) values(f1_insert_triggered()); insert into t5(a) values(8); end | create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER BEGIN INSERT INTO t6(a) values(2),(3); RETURN 1; END// begin; insert into t1(a,b) values(1,1),(2,1); insert into t3(a,b) values(1,1),(2,1); update t1 set a = a + 5 where b = 1; update t3 set a = a + 5 where b = 1; delete from t1 where b = 1; delete from t3 where b = 1; connection master1; insert into t2(a) values(3); insert into t4(a) values(3); connection master; commit; insert into t1(a,b) values(4,2); insert into t3(a,b) values(4,2); update t1 set a = a + 5 where b = 2; update t3 set a = a + 5 where b = 2; delete from t1 where b = 2; delete from t3 where b = 2; # To verify if insert/update in an autoinc column causes statement to be logged in row format include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=5 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) master-bin.000001 # Annotate_rows # # update t1 set a = a + 5 where b = 1 master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Update_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Update_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Annotate_rows # # update t3 set a = a + 5 where b = 1 master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Update_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Update_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # update t1 set a = a + 5 where b = 2 master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Update_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # update t3 set a = a + 5 where b = 2 master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Update_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ commit; connection master; connection slave; #Test if the results are consistent on master and slave #for 'INVOKES A TRIGGER with after update action' include/diff_tables.inc [master:t2, slave:t2] include/diff_tables.inc [master:t4, slave:t4] include/diff_tables.inc [master:t6, slave:t6] connection master; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; DROP TABLE t5; DROP TABLE t6; DROP FUNCTION f1_insert_triggered; connection slave; # Test case4: INVOKES A TRIGGER with before update action connection master; create table t1(a int, b int) engine=innodb; create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create trigger tr1 before update on t1 for each row insert into t2(a) values(6); create table t3(a int, b int) engine=innodb; create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create table t5(a int) engine=innodb; create trigger tr2 before update on t3 for each row begin insert into t4(a) values(f1_insert_triggered()); insert into t4(a) values(f1_insert_triggered()); insert into t5(a) values(8); end | create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER BEGIN INSERT INTO t6(a) values(2),(3); RETURN 1; END// begin; insert into t1(a,b) values(1,1),(2,1); insert into t3(a,b) values(1,1),(2,1); update t1 set a = a + 5 where b = 1; update t3 set a = a + 5 where b = 1; delete from t1 where b = 1; delete from t3 where b = 1; connection master1; insert into t2(a) values(3); insert into t4(a) values(3); connection master; commit; insert into t1(a,b) values(4,2); insert into t3(a,b) values(4,2); update t1 set a = a + 5 where b = 2; update t3 set a = a + 5 where b = 2; delete from t1 where b = 2; delete from t3 where b = 2; # To verify if insert/update in an autoinc column causes statement to be logged in row format include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=5 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) master-bin.000001 # Annotate_rows # # insert into t2(a) values(6) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Update_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Update_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t2(a) values(6) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ commit; connection master; connection slave; #Test if the results are consistent on master and slave #for 'INVOKES A TRIGGER with before update action' include/diff_tables.inc [master:t2, slave:t2] include/diff_tables.inc [master:t4, slave:t4] include/diff_tables.inc [master:t6, slave:t6] connection master; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; DROP TABLE t5; DROP TABLE t6; DROP FUNCTION f1_insert_triggered; connection slave; # Test case5: INVOKES A TRIGGER with after delete action connection master; create table t1(a int, b int) engine=innodb; create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create trigger tr1 after delete on t1 for each row insert into t2(a) values(6); create table t3(a int, b int) engine=innodb; create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create table t5(a int) engine=innodb; create trigger tr2 after delete on t3 for each row begin insert into t4(a) values(f1_insert_triggered()); insert into t4(a) values(f1_insert_triggered()); insert into t5(a) values(8); end | create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER BEGIN INSERT INTO t6(a) values(2),(3); RETURN 1; END// begin; insert into t1(a,b) values(1,1),(2,1); insert into t3(a,b) values(1,1),(2,1); update t1 set a = a + 5 where b = 1; update t3 set a = a + 5 where b = 1; delete from t1 where b = 1; delete from t3 where b = 1; connection master1; insert into t2(a) values(3); insert into t4(a) values(3); connection master; commit; insert into t1(a,b) values(4,2); insert into t3(a,b) values(4,2); update t1 set a = a + 5 where b = 2; update t3 set a = a + 5 where b = 2; delete from t1 where b = 2; delete from t3 where b = 2; # To verify if insert/update in an autoinc column causes statement to be logged in row format include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=5 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 master-bin.000001 # Annotate_rows # # delete from t1 where b = 1 master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Delete_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Delete_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Annotate_rows # # delete from t3 where b = 1 master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Delete_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Delete_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # delete from t1 where b = 2 master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Delete_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # delete from t3 where b = 2 master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Delete_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ commit; connection master; connection slave; #Test if the results are consistent on master and slave #for 'INVOKES A TRIGGER with after delete action' include/diff_tables.inc [master:t2, slave:t2] include/diff_tables.inc [master:t4, slave:t4] include/diff_tables.inc [master:t6, slave:t6] connection master; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; DROP TABLE t5; DROP TABLE t6; DROP FUNCTION f1_insert_triggered; connection slave; # Test case6: INVOKES A TRIGGER with before delete action connection master; create table t1(a int, b int) engine=innodb; create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create trigger tr1 before delete on t1 for each row insert into t2(a) values(6); create table t3(a int, b int) engine=innodb; create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create table t5(a int) engine=innodb; create trigger tr2 before delete on t3 for each row begin insert into t4(a) values(f1_insert_triggered()); insert into t4(a) values(f1_insert_triggered()); insert into t5(a) values(8); end | create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER BEGIN INSERT INTO t6(a) values(2),(3); RETURN 1; END// begin; insert into t1(a,b) values(1,1),(2,1); insert into t3(a,b) values(1,1),(2,1); update t1 set a = a + 5 where b = 1; update t3 set a = a + 5 where b = 1; delete from t1 where b = 1; delete from t3 where b = 1; connection master1; insert into t2(a) values(3); insert into t4(a) values(3); connection master; commit; insert into t1(a,b) values(4,2); insert into t3(a,b) values(4,2); update t1 set a = a + 5 where b = 2; update t3 set a = a + 5 where b = 2; delete from t1 where b = 2; delete from t3 where b = 2; # To verify if insert/update in an autoinc column causes statement to be logged in row format include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=5 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 master-bin.000001 # Annotate_rows # # insert into t2(a) values(6) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Delete_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Delete_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t2(a) values(6) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Table_map # # table_id: # (test.t5) master-bin.000001 # Table_map # # table_id: # (test.t4) master-bin.000001 # Table_map # # table_id: # (test.t6) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ commit; connection master; connection slave; #Test if the results are consistent on master and slave #for 'INVOKES A TRIGGER with before delete action' include/diff_tables.inc [master:t2, slave:t2] include/diff_tables.inc [master:t4, slave:t4] include/diff_tables.inc [master:t6, slave:t6] connection master; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; DROP TABLE t5; DROP TABLE t6; DROP FUNCTION f1_insert_triggered; connection slave; # Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after insert action connection master; create table t1(a int) engine=innodb; create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER BEGIN INSERT INTO t2(a) values(2),(3); INSERT INTO t2(a) values(2),(3); RETURN 1; END | create trigger tr11 after insert on t2 for each row begin insert into t3(a) values(new.a); insert into t3(a) values(new.a); end | begin; insert into t1(a) values(f1_two_inserts_trigger()); connection master1; insert into t2(a) values(4),(5); connection master; commit; insert into t1(a) values(f1_two_inserts_trigger()); # To verify if insert/update in an autoinc column causes statement to be logged in row format include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t2(a) values(4),(5) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # INSERT INTO t2(a) values(2),(3) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # INSERT INTO t2(a) values(2),(3) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ commit; connection master; connection slave; #Test if the results are consistent on master and slave #for 'CALLS A FUNCTION which INVOKES A TRIGGER with after insert action' include/diff_tables.inc [master:t2, slave:t2] include/diff_tables.inc [master:t3, slave:t3] connection master; drop table t1; drop table t2; drop table t3; drop function f1_two_inserts_trigger; connection slave; # Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before insert action connection master; create table t1(a int) engine=innodb; create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER BEGIN INSERT INTO t2(a) values(2),(3); INSERT INTO t2(a) values(2),(3); RETURN 1; END | create trigger tr11 before insert on t2 for each row begin insert into t3(a) values(new.a); insert into t3(a) values(new.a); end | begin; insert into t1(a) values(f1_two_inserts_trigger()); connection master1; insert into t2(a) values(4),(5); connection master; commit; insert into t1(a) values(f1_two_inserts_trigger()); # To verify if insert/update in an autoinc column causes statement to be logged in row format include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t3(a) values(new.a) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t3(a) values(new.a) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Annotate_rows # # insert into t3(a) values(new.a) master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Table_map # # table_id: # (test.t3) master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ commit; connection master; connection slave; #Test if the results are consistent on master and slave #for 'CALLS A FUNCTION which INVOKES A TRIGGER with before insert action' include/diff_tables.inc [master:t2, slave:t2] include/diff_tables.inc [master:t3, slave:t3] connection master; drop table t1; drop table t2; drop table t3; drop function f1_two_inserts_trigger; connection slave; # Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with after insert action connection master; CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create trigger tr16 after insert on t1 for each row insert into t3(a) values(new.c1); create trigger tr17 after insert on t2 for each row insert into t3(a) values(new.c2); begin; INSERT INTO t1(c1) VALUES (11), (12); INSERT INTO t2(c2) VALUES (13), (14); CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2; INSERT INTO v16(c1) VALUES (15),(16); INSERT INTO v16(c2) VALUES (17),(18); connection master1; INSERT INTO v16(c1) VALUES (19),(20); INSERT INTO v16(c2) VALUES (21),(22); connection master; INSERT INTO v16(c1) VALUES (23), (24); INSERT INTO v16(c1) VALUES (25), (26); commit; connection slave; #Test if the results are consistent on master and slave #for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS' include/diff_tables.inc [master:t3, slave:t3] connection master; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP VIEW v16; connection slave; # Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with before insert action connection master; CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; create trigger tr16 before insert on t1 for each row insert into t3(a) values(new.c1); create trigger tr17 before insert on t2 for each row insert into t3(a) values(new.c2); begin; INSERT INTO t1(c1) VALUES (11), (12); INSERT INTO t2(c2) VALUES (13), (14); CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2; INSERT INTO v16(c1) VALUES (15),(16); INSERT INTO v16(c2) VALUES (17),(18); connection master1; INSERT INTO v16(c1) VALUES (19),(20); INSERT INTO v16(c2) VALUES (21),(22); connection master; INSERT INTO v16(c1) VALUES (23), (24); INSERT INTO v16(c1) VALUES (25), (26); commit; connection slave; #Test if the results are consistent on master and slave #for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS' include/diff_tables.inc [master:t3, slave:t3] connection master; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP VIEW v16; connection slave; # Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN connection master; create table t1(a int) engine=innodb; create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; CREATE FUNCTION f1_two_inserts() RETURNS INTEGER BEGIN INSERT INTO t2(a) values(2); INSERT INTO t2(a) values(2); RETURN 1; END// begin; insert into t1(a) values(f1_two_inserts()); connection master1; insert into t2(a) values(4),(5); connection master; commit; insert into t1(a) values(f1_two_inserts()); commit; connection master; #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master select * from t2 ORDER BY i1; i1 a 1 2 2 2 3 4 4 5 5 2 6 2 connection slave; connection slave; #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave select * from t2 ORDER BY i1; i1 a 1 2 2 2 3 4 4 5 5 2 6 2 connection master; drop table t1; drop table t2; drop function f1_two_inserts; connection slave; # Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN connection master; create table t1(a int) engine=innodb; create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; CREATE FUNCTION f1_two_updates() RETURNS INTEGER BEGIN update t2 set a = a + 5 where b = 1; update t2 set a = a + 5 where b = 2; update t2 set a = a + 5 where b = 3; update t2 set a = a + 5 where b = 4; RETURN 1; END// connection master1; insert into t2(a,b) values(1,1); insert into t2(a,b) values(2,2); insert into t2(a,b) values(3,3); insert into t2(a,b) values(4,4); insert into t1(a) values(f1_two_updates()); connection master; begin; insert into t1(a) values(f1_two_updates()); commit; connection master; #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master select * from t2 ORDER BY i1; i1 a b 1 11 1 2 12 2 3 13 3 4 14 4 connection slave; connection slave; #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave select * from t2 ORDER BY i1; i1 a b 1 11 1 2 12 2 3 13 3 4 14 4 connection master; drop table t1; drop table t2; drop function f1_two_updates; connection slave; # Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT connection master; create table t1(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; begin; insert into t1(a,b) values(1,1),(2,2); insert into t2(a,b) values(1,1),(2,2); update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b; insert into t1(a,b) values(3,3); insert into t2(a,b) values(3,3); commit; # To verify if it works fine when these statements are not be marked as unsafe include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=1 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,2) master-bin.000001 # Intvar # # INSERT_ID=1 master-bin.000001 # Query # # use `test`; insert into t2(a,b) values(1,1),(2,2) master-bin.000001 # Query # # use `test`; update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(3,3) master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; insert into t2(a,b) values(3,3) master-bin.000001 # Xid # # COMMIT /* XID */ connection slave; #Test if the results are consistent on master and slave #for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT' include/diff_tables.inc [master:t1, slave:t1] include/diff_tables.inc [master:t2, slave:t2] connection master; drop table t1; drop table t2; connection slave; # Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES connection master; CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; begin; INSERT INTO t1(c1) VALUES (11), (12); INSERT INTO t2(c2) VALUES (13), (14); CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2; INSERT INTO v15(c1) VALUES (15),(16); INSERT INTO v15(c2) VALUES (17),(18); connection master1; INSERT INTO v15(c1) VALUES (19),(20); INSERT INTO v15(c2) VALUES (21),(22); connection master; INSERT INTO v15(c1) VALUES (23), (24); INSERT INTO v15(c2) VALUES (25), (26); commit; # To verify if it works fine when these statements are not be marked as unsafe include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=1 master-bin.000001 # Query # # use `test`; INSERT INTO t1(c1) VALUES (11), (12) master-bin.000001 # Intvar # # INSERT_ID=1 master-bin.000001 # Query # # use `test`; INSERT INTO t2(c2) VALUES (13), (14) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v15` AS SELECT c1, c2 FROM t1, t2 master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (15),(16) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (17),(18) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=5 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (19),(20) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=5 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (21),(22) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=7 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (23), (24) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Intvar # # INSERT_ID=7 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (25), (26) master-bin.000001 # Xid # # COMMIT /* XID */ connection slave; #Test if the results are consistent on master and slave #for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES' include/diff_tables.inc [master:t1, slave:t1] include/diff_tables.inc [master:t2, slave:t2] connection master; drop table t1; drop table t2; drop view v15; connection slave; include/rpl_end.inc