path: root/mysql-test/t/rpl_slave_skip.test

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;