source include/have_innodb.inc; source include/master-slave.inc; # This test is for checking that the use of SQL_SLAVE_SKIP_COUNTER # behaves as expected, i.e., that it is guaranteed to skip an entire # group and not start executing in the middle of a transaction. # We are checking the correct behaviour when using both a # transactional and non-transactional table. The non-transactional # table comes into play when rolling back a transaction containing a # write to this table. In that case, the transaction should still be # written to the binary log, and the slave will apply it and then roll # it back to get the non-transactional change into the table. --echo **** On Master **** CREATE TABLE t1 (a INT, b SET('master','slave')) ENGINE=InnoDB; CREATE TABLE t2 (a INT, b SET('master','slave')) ENGINE=MyISAM; CREATE TABLE t3 (a CHAR(20), b SET('master','slave')) ENGINE=InnoDB; CREATE TABLE t4 (a CHAR(20), b SET('master','slave')) ENGINE=MyISAM; --echo ==== Skipping normal transactions ==== --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 t1 VALUES (1, 'master'); INSERT INTO t1 VALUES (2, 'master'); INSERT INTO t1 VALUES (3, 'master'); COMMIT; BEGIN; INSERT INTO t1 VALUES (4, 'master,slave'); INSERT INTO t1 VALUES (5, 'master,slave'); INSERT INTO t1 VALUES (6, 'master,slave'); COMMIT; save_master_pos; SELECT * FROM t1 ORDER BY a; # This will skip a begin event and the first INSERT of the # transaction, and it should keep skipping until it has reached the # transaction terminator. --echo **** On Slave **** connection slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; source include/wait_for_slave_to_start.inc; sync_with_master; SELECT * FROM t1 ORDER BY a; --echo **** On Master **** connection master; DELETE FROM t1; sync_slave_with_master; --echo ==== Skipping two normal transactions ==== --echo **** On Slave **** connection slave; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo **** On Master **** connection master; BEGIN; INSERT INTO t1 VALUES (1, 'master'); INSERT INTO t1 VALUES (2, 'master'); INSERT INTO t1 VALUES (3, 'master'); COMMIT; BEGIN; INSERT INTO t1 VALUES (4, 'master'); INSERT INTO t1 VALUES (5, 'master'); INSERT INTO t1 VALUES (6, 'master'); COMMIT; BEGIN; INSERT INTO t1 VALUES (7, 'master,slave'); INSERT INTO t1 VALUES (8, 'master,slave'); INSERT INTO t1 VALUES (9, 'master,slave'); COMMIT; save_master_pos; SELECT * FROM t1 ORDER BY a; # This will skip a begin event and the first INSERT of the # transaction, and it should keep skipping until it has reached the # transaction terminator. --echo **** On Slave **** connection slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=8; START SLAVE; source include/wait_for_slave_to_start.inc; sync_with_master; SELECT * FROM t1 ORDER BY a; --echo **** On Master **** connection master; DELETE FROM t1; sync_slave_with_master; --echo ==== Skipping without autocommit ==== # Testing without using autocommit instead. It should still write a # BEGIN event, so the behaviour should be the same --echo **** On Slave **** connection slave; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo **** On Master **** connection master; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (1, 'master'); INSERT INTO t1 VALUES (2, 'master'); INSERT INTO t1 VALUES (3, 'master'); COMMIT; INSERT INTO t1 VALUES (4, 'master,slave'); INSERT INTO t1 VALUES (5, 'master,slave'); INSERT INTO t1 VALUES (6, 'master,slave'); COMMIT; save_master_pos; SELECT * FROM t1 ORDER BY a; # This will skip a begin event and the first INSERT of the # transaction, and it should keep skipping until it has reached the # transaction terminator. --echo **** On Slave **** connection slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; source include/wait_for_slave_to_start.inc; sync_with_master; SELECT * FROM t1 ORDER BY a; # Testing with a non-transactional table in the transaction. This will # log a ROLLBACK as a transaction terminator, which is a normal Query # log event. --echo ==== Rollback of transaction with non-transactional change ==== --echo **** On Master **** connection master; DELETE FROM t1; SET AUTOCOMMIT=1; --echo **** On Slave **** sync_slave_with_master; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo **** On Master **** connection master; disable_warnings; BEGIN; INSERT INTO t1 VALUES (1, ''); INSERT INTO t2 VALUES (2, 'master'); INSERT INTO t1 VALUES (3, ''); ROLLBACK; BEGIN; INSERT INTO t1 VALUES (4, ''); INSERT INTO t2 VALUES (5, 'master,slave'); INSERT INTO t1 VALUES (6, ''); ROLLBACK; enable_warnings; save_master_pos; SELECT * FROM t1 ORDER BY a; SELECT * FROM t2 ORDER BY a; --echo **** On Slave **** connection slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; 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; --echo ==== Skipping first event of a LOAD DATA for a transactional table ==== --echo **** On Slave **** connection slave; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo **** On Master **** connection master; SET AUTOCOMMIT=1; LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t3(a) SET b = 'master'; INSERT INTO t3 VALUES ('Go Rin No Sho', 'master,slave'); save_master_pos; SELECT COUNT(*) FROM t3; # This will skip a begin event and the first INSERT of the # transaction, and it should keep skipping until it has reached the # transaction terminator. --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; --echo -- Should only contain records marked 'master,slave' SELECT * FROM t3 ORDER BY a; --echo **** On Master **** connection master; DELETE FROM t3; sync_slave_with_master; --echo ==== Skipping first event of a LOAD DATA for a non-transactional table ==== --echo **** On Slave **** connection slave; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo **** On Master **** connection master; SET AUTOCOMMIT=1; LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t4(a) SET b = 'master'; INSERT INTO t4 VALUES ('Go Rin No Sho', 'master,slave'); save_master_pos; SELECT COUNT(*) FROM t4; # This will skip a begin event and the first INSERT of the # transaction, and it should keep skipping until it has reached the # transaction terminator. --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; --echo -- Should only contain records marked 'master,slave' SELECT * FROM t4 ORDER BY a; --echo **** On Master **** connection master; DELETE FROM t4; sync_slave_with_master; --echo ==== Try with a big file so that we get an append_block event as well --echo **** On Slave **** connection slave; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo **** On Master **** connection master; SET AUTOCOMMIT=1; # This contain about 70 words, so we double it a few times to get more than 128 KiB SET SQL_LOG_BIN=0; LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t4(a) SET b = 'master'; INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; SELECT a FROM t4 INTO OUTFILE 'rpl_slave_skip_words.dat'; SET SQL_LOG_BIN=1; # Start the real job LOAD DATA INFILE 'rpl_slave_skip_words.dat' INTO TABLE t4(a) SET b = 'master'; INSERT INTO t4 VALUES ('Go Rin No Sho', 'master,slave'); #SHOW BINLOG EVENTS; save_master_pos; SELECT COUNT(*) FROM t4; # This will skip a begin event and the first INSERT of the # transaction, and it should keep skipping until it has reached the # transaction terminator. --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; --echo -- Should only contain records marked 'master,slave' SELECT * FROM t4 ORDER BY a; --echo **** On Master **** connection master; DELETE FROM t4; sync_slave_with_master; # Test to generate a Delete_file log event, and see that it works as well. --echo **** On Master **** connection master; CREATE TABLE t5 (a int, b int, c SET('master','slave'), PRIMARY KEY (a,b)) ENGINE=MyISAM; LOAD DATA INFILE '../std_data_ln/loaddata5.dat' INTO TABLE t5 FIELDS TERMINATED BY '' ENCLOSED BY '' (a,b) SET c='master,slave'; --echo **** On Slave **** sync_slave_with_master; STOP SLAVE; source include/wait_for_slave_to_stop.inc; --echo **** On Master **** connection master; error ER_DUP_ENTRY; LOAD DATA INFILE '../std_data_ln/loaddata5.dat' INTO TABLE t5 FIELDS TERMINATED BY '' ENCLOSED BY '' (a,b) SET c=''; INSERT INTO t5 VALUES (42, 42, 'master,slave'); save_master_pos; #SHOW BINLOG EVENTS; SELECT * FROM t5; --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 t5; connection slave; --echo ==== Cleanup ==== --echo **** On Master **** connection master; DROP TABLE t1, t2, t3, t4, t5; sync_slave_with_master;