source include/master-slave.inc; source include/have_innodb.inc; --echo **** On Slave **** connection slave; source include/have_innodb.inc; STOP SLAVE; --echo **** On Master **** connection master; SET SESSION BINLOG_FORMAT=ROW; CREATE TABLE t1 (a INT, b INT); CREATE TABLE t2 (c INT, d INT); INSERT INTO t1 VALUES (1,1),(2,4),(3,9); INSERT INTO t2 VALUES (1,1),(2,8),(3,27); UPDATE t1,t2 SET b = d, d = b * 2 WHERE a = c; source include/show_binlog_events.inc; # These tables should be changed SELECT * FROM t1; SELECT * FROM t2; save_master_pos; --echo **** On Slave **** connection slave; # Stop when reaching the the first table map event. START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=484; wait_for_slave_to_stop; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # 35 # 36 # query_vertical SHOW SLAVE STATUS; # Now we skip *one* table map event. If the execution starts right # after that table map event, *one* of the involved tables will be # changed. SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; sync_with_master; # These values should be what was inserted, not what was # updated. Since we are skipping the first table map of the group # representing the UPDATE statement above, we should skip the entire # group and not start executing at the first table map. SELECT * FROM t1; SELECT * FROM t2; STOP SLAVE; RESET SLAVE; connection master; RESET MASTER; SET SESSION BINLOG_FORMAT=STATEMENT; SET @foo = 12; INSERT INTO t1 VALUES(@foo, 2*@foo); save_master_pos; source include/show_binlog_events.inc; connection slave; START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=106; wait_for_slave_to_stop; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; sync_with_master; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 8 # 9 # 23 # 33 # 35 # 36 # query_vertical SHOW SLAVE STATUS; --echo **** On Master **** connection master; DROP TABLE t1, t2; sync_slave_with_master; # # More tests for BUG#28618 # # Case 1. # ROW binlog format and non-transactional tables. # Create the group of events via triggers and try to skip # some items of that group. # connection master; SET SESSION BINLOG_FORMAT=ROW; SET AUTOCOMMIT=0; CREATE TABLE t1 (a INT, b VARCHAR(20)) ENGINE=myisam; CREATE TABLE t2 (a INT, b VARCHAR(20)) ENGINE=myisam; CREATE TABLE t3 (a INT, b VARCHAR(20)) ENGINE=myisam; INSERT INTO t1 VALUES (1,'master/slave'); INSERT INTO t2 VALUES (1,'master/slave'); INSERT INTO t3 VALUES (1,'master/slave'); DELIMITER |; CREATE TRIGGER tr1 AFTER UPDATE on t1 FOR EACH ROW BEGIN INSERT INTO t2 VALUES (NEW.a,NEW.b); DELETE FROM t2 WHERE a < NEW.a; END| CREATE TRIGGER tr2 AFTER INSERT on t2 FOR EACH ROW BEGIN UPDATE t3 SET a =2, b = 'master only'; END| DELIMITER ;| --echo **** On Slave **** sync_slave_with_master; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo **** On Master **** connection master; UPDATE t1 SET a = 2, b = 'master only' WHERE a = 1; DROP TRIGGER tr1; DROP TRIGGER tr2; INSERT INTO t1 VALUES (3,'master/slave'); INSERT INTO t2 VALUES (3,'master/slave'); INSERT INTO t3 VALUES (3,'master/slave'); SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; save_master_pos; --echo *** On Slave *** connection slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; source include/wait_for_slave_to_start.inc; sync_with_master; SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; SELECT * FROM t3 ORDER BY a; connection master; DROP TABLE t1, t2, t3; sync_slave_with_master; --echo **** Case 2: Row binlog format and transactional tables **** # Create the transaction and try to skip some # queries from one. --echo *** On Master *** connection master; CREATE TABLE t4 (a INT, b VARCHAR(20)) ENGINE=innodb; CREATE TABLE t5 (a INT, b VARCHAR(20)) ENGINE=innodb; CREATE TABLE t6 (a INT, b VARCHAR(20)) ENGINE=innodb; --echo **** On Slave **** sync_slave_with_master; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo *** On Master *** connection master; BEGIN; INSERT INTO t4 VALUES (2, 'master only'); INSERT INTO t5 VALUES (2, 'master only'); INSERT INTO t6 VALUES (2, 'master only'); COMMIT; BEGIN; INSERT INTO t4 VALUES (3, 'master/slave'); INSERT INTO t5 VALUES (3, 'master/slave'); INSERT INTO t6 VALUES (3, 'master/slave'); COMMIT; SELECT * FROM t4 ORDER BY a; SELECT * FROM t5 ORDER BY a; SELECT * FROM t6 ORDER BY a; save_master_pos; --echo *** On Slave *** connection slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; source include/wait_for_slave_to_start.inc; sync_with_master; SELECT * FROM t4 ORDER BY a; SELECT * FROM t5 ORDER BY a; SELECT * FROM t6 ORDER BY a; # Test skipping two groups --echo **** On Slave **** connection slave; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo *** On Master *** connection master; BEGIN; INSERT INTO t4 VALUES (6, 'master only'); INSERT INTO t5 VALUES (6, 'master only'); INSERT INTO t6 VALUES (6, 'master only'); COMMIT; BEGIN; INSERT INTO t4 VALUES (7, 'master only'); INSERT INTO t5 VALUES (7, 'master only'); INSERT INTO t6 VALUES (7, 'master only'); COMMIT; SELECT * FROM t4 ORDER BY a; SELECT * FROM t5 ORDER BY a; SELECT * FROM t6 ORDER BY a; save_master_pos; --echo *** On Slave *** connection slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=10; START SLAVE; source include/wait_for_slave_to_start.inc; sync_with_master; SELECT * FROM t4 ORDER BY a; SELECT * FROM t5 ORDER BY a; SELECT * FROM t6 ORDER BY a; # # And the same, but with autocommit = 0 # connection slave; STOP SLAVE; source include/wait_for_slave_to_stop.inc; connection master; SET AUTOCOMMIT=0; INSERT INTO t4 VALUES (4, 'master only'); INSERT INTO t5 VALUES (4, 'master only'); INSERT INTO t6 VALUES (4, 'master only'); COMMIT; INSERT INTO t4 VALUES (5, 'master/slave'); INSERT INTO t5 VALUES (5, 'master/slave'); INSERT INTO t6 VALUES (5, 'master/slave'); COMMIT; SELECT * FROM t4 ORDER BY a; SELECT * FROM t5 ORDER BY a; SELECT * FROM t6 ORDER BY a; save_master_pos; --echo *** On Slave *** connection slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; source include/wait_for_slave_to_start.inc; sync_with_master; SELECT * FROM t4 ORDER BY a; SELECT * FROM t5 ORDER BY a; SELECT * FROM t6 ORDER BY a; connection master; DROP TABLE t4, t5, t6; sync_slave_with_master; --echo **** Case 3: Statement logging format and LOAD DATA with non-transactional table **** # LOAD DATA creates two events in binary log for statement binlog format. # Try to skip the first. --echo *** On Master *** connection master; CREATE TABLE t10 (a INT, b VARCHAR(20)) ENGINE=myisam; --echo *** On Slave *** sync_slave_with_master; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo *** On Master *** connection master; SET SESSION BINLOG_FORMAT=STATEMENT; exec cp ./suite/rpl/data/rpl_bug28618.dat $MYSQLTEST_VARDIR/tmp/; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|'; remove_file $MYSQLTEST_VARDIR/tmp/rpl_bug28618.dat; SELECT * FROM t10 ORDER BY a; save_master_pos; --echo *** On Slave *** connection slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; source include/wait_for_slave_to_start.inc; sync_with_master; SELECT * FROM t10 ORDER BY a; connection master; DROP TABLE t10; sync_slave_with_master;