# This is a BitKeeper generated diff -Nru style patch. # # ChangeSet # 2007/12/20 15:24:27+01:00 mats@capulet.kindahl.net # BUG#12691 (Exec_master_log_pos corrupted with SQL_SLAVE_SKIP_COUNTER): # # Complementary patch since LOAD DATA INFILE was not covered in # the previous patch. # # This patch adds a check so that the slave skip counter is not # decreased to zero if seeing a BEGIN_LOAD_QUERY_EVENT, # APPEND_BLOCK_EVENT, or CREATE_FILE_EVENT since these cannot # end a group. The group is terminated by an EXECUTE_LOAD_QUERY_ # EVENT or DELETE_FILE_EVENT. # # mysql-test/r/rpl_slave_skip.result # 2007/12/20 15:24:24+01:00 mats@capulet.kindahl.net +158 -3 # Result change. # # mysql-test/t/rpl_slave_skip.test # 2007/12/20 15:24:25+01:00 mats@capulet.kindahl.net +134 -3 # Adding tests to test that the first event of a LOAD DATA INFILE # can be skipped safely for both transactional and non-transactional # tables. # # sql/slave.cc # 2007/12/20 15:24:25+01:00 mats@capulet.kindahl.net +4 -1 # Not decrementing slave skip counter to zero when seeing a # BEGIN_LOAD_QUERY_EVENT, APPEND_BLOCK_EVENT, or CREATE_FILE_EVENT # since these cannot end a group. # diff -Nru a/mysql-test/r/rpl_slave_skip.result b/mysql-test/r/rpl_slave_skip.result --- a/mysql-test/r/rpl_slave_skip.result 2007-12-20 15:26:05 +01:00 +++ b/mysql-test/r/rpl_slave_skip.result 2007-12-20 15:26:05 +01:00 @@ -5,8 +5,10 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; **** 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 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; ==== Skipping normal transactions ==== **** On Slave **** STOP SLAVE; @@ -139,6 +141,159 @@ SELECT * FROM t2 ORDER BY a; a b 5 master,slave +==== Skipping first event of a LOAD DATA for a transactional table ==== +**** On Slave **** +STOP SLAVE; +**** On 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'); +SELECT COUNT(*) FROM t3; +COUNT(*) +71 +**** On Slave **** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +-- Should only contain records marked 'master,slave' +SELECT * FROM t3 ORDER BY a; +a b +Go Rin No Sho master,slave +**** On Master **** +DELETE FROM t3; +==== Skipping first event of a LOAD DATA for a non-transactional table ==== +**** On Slave **** +STOP SLAVE; +**** On 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'); +SELECT COUNT(*) FROM t4; +COUNT(*) +71 +**** On Slave **** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +-- Should only contain records marked 'master,slave' +SELECT * FROM t4 ORDER BY a; +a b +Go Rin No Sho master,slave +**** On Master **** +DELETE FROM t4; +==== Try with a big file so that we get an append_block event as well +**** On Slave **** +STOP SLAVE; +**** On Master **** +SET AUTOCOMMIT=1; +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; +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; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 98 Server ver: 5.0.52-debug-log, Binlog ver: 4 +master-bin.000001 98 Query 1 223 use `test`; CREATE TABLE t1 (a INT, b SET('master','slave')) ENGINE=InnoDB +master-bin.000001 223 Query 1 348 use `test`; CREATE TABLE t2 (a INT, b SET('master','slave')) ENGINE=MyISAM +master-bin.000001 348 Query 1 478 use `test`; CREATE TABLE t3 (a CHAR(20), b SET('master','slave')) ENGINE=InnoDB +master-bin.000001 478 Query 1 608 use `test`; CREATE TABLE t4 (a CHAR(20), b SET('master','slave')) ENGINE=MyISAM +master-bin.000001 608 Query 1 676 use `test`; BEGIN +master-bin.000001 676 Query 1 774 use `test`; INSERT INTO t1 VALUES (1, 'master') +master-bin.000001 774 Query 1 872 use `test`; INSERT INTO t1 VALUES (2, 'master') +master-bin.000001 872 Query 1 970 use `test`; INSERT INTO t1 VALUES (3, 'master') +master-bin.000001 970 Xid 1 997 COMMIT /* xid=16 */ +master-bin.000001 997 Query 1 1065 use `test`; BEGIN +master-bin.000001 1065 Query 1 1169 use `test`; INSERT INTO t1 VALUES (4, 'master,slave') +master-bin.000001 1169 Query 1 1273 use `test`; INSERT INTO t1 VALUES (5, 'master,slave') +master-bin.000001 1273 Query 1 1377 use `test`; INSERT INTO t1 VALUES (6, 'master,slave') +master-bin.000001 1377 Xid 1 1404 COMMIT /* xid=21 */ +master-bin.000001 1404 Query 1 1481 use `test`; DELETE FROM t1 +master-bin.000001 1481 Xid 1 1508 COMMIT /* xid=31 */ +master-bin.000001 1508 Query 1 1576 use `test`; BEGIN +master-bin.000001 1576 Query 1 1674 use `test`; INSERT INTO t1 VALUES (1, 'master') +master-bin.000001 1674 Query 1 1772 use `test`; INSERT INTO t1 VALUES (2, 'master') +master-bin.000001 1772 Query 1 1870 use `test`; INSERT INTO t1 VALUES (3, 'master') +master-bin.000001 1870 Xid 1 1897 COMMIT /* xid=34 */ +master-bin.000001 1897 Query 1 1965 use `test`; BEGIN +master-bin.000001 1965 Query 1 2063 use `test`; INSERT INTO t1 VALUES (4, 'master') +master-bin.000001 2063 Query 1 2161 use `test`; INSERT INTO t1 VALUES (5, 'master') +master-bin.000001 2161 Query 1 2259 use `test`; INSERT INTO t1 VALUES (6, 'master') +master-bin.000001 2259 Xid 1 2286 COMMIT /* xid=39 */ +master-bin.000001 2286 Query 1 2354 use `test`; BEGIN +master-bin.000001 2354 Query 1 2458 use `test`; INSERT INTO t1 VALUES (7, 'master,slave') +master-bin.000001 2458 Query 1 2562 use `test`; INSERT INTO t1 VALUES (8, 'master,slave') +master-bin.000001 2562 Query 1 2666 use `test`; INSERT INTO t1 VALUES (9, 'master,slave') +master-bin.000001 2666 Xid 1 2693 COMMIT /* xid=44 */ +master-bin.000001 2693 Query 1 2770 use `test`; DELETE FROM t1 +master-bin.000001 2770 Xid 1 2797 COMMIT /* xid=54 */ +master-bin.000001 2797 Query 1 2865 use `test`; BEGIN +master-bin.000001 2865 Query 1 2963 use `test`; INSERT INTO t1 VALUES (1, 'master') +master-bin.000001 2963 Query 1 3061 use `test`; INSERT INTO t1 VALUES (2, 'master') +master-bin.000001 3061 Query 1 3159 use `test`; INSERT INTO t1 VALUES (3, 'master') +master-bin.000001 3159 Xid 1 3186 COMMIT /* xid=57 */ +master-bin.000001 3186 Query 1 3254 use `test`; BEGIN +master-bin.000001 3254 Query 1 3358 use `test`; INSERT INTO t1 VALUES (4, 'master,slave') +master-bin.000001 3358 Query 1 3462 use `test`; INSERT INTO t1 VALUES (5, 'master,slave') +master-bin.000001 3462 Query 1 3566 use `test`; INSERT INTO t1 VALUES (6, 'master,slave') +master-bin.000001 3566 Xid 1 3593 COMMIT /* xid=61 */ +master-bin.000001 3593 Query 1 3670 use `test`; DELETE FROM t1 +master-bin.000001 3670 Xid 1 3697 COMMIT /* xid=66 */ +master-bin.000001 3697 Query 1 3765 use `test`; BEGIN +master-bin.000001 3765 Query 1 3857 use `test`; INSERT INTO t1 VALUES (1, '') +master-bin.000001 3857 Query 1 3955 use `test`; INSERT INTO t2 VALUES (2, 'master') +master-bin.000001 3955 Query 1 4047 use `test`; INSERT INTO t1 VALUES (3, '') +master-bin.000001 4047 Query 1 4118 use `test`; ROLLBACK +master-bin.000001 4118 Query 1 4186 use `test`; BEGIN +master-bin.000001 4186 Query 1 4278 use `test`; INSERT INTO t1 VALUES (4, '') +master-bin.000001 4278 Query 1 4382 use `test`; INSERT INTO t2 VALUES (5, 'master,slave') +master-bin.000001 4382 Query 1 4474 use `test`; INSERT INTO t1 VALUES (6, '') +master-bin.000001 4474 Query 1 4545 use `test`; ROLLBACK +master-bin.000001 4545 Begin_load_query 1 5149 ;file_id=1;block_len=581 +master-bin.000001 5149 Execute_load_query 1 5302 use `test`; LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t3(a) SET b = 'master' ;file_id=1 +master-bin.000001 5302 Xid 1 5329 COMMIT /* xid=92 */ +master-bin.000001 5329 Query 1 5447 use `test`; INSERT INTO t3 VALUES ('Go Rin No Sho', 'master,slave') +master-bin.000001 5447 Xid 1 5474 COMMIT /* xid=93 */ +master-bin.000001 5474 Query 1 5551 use `test`; DELETE FROM t3 +master-bin.000001 5551 Xid 1 5578 COMMIT /* xid=100 */ +master-bin.000001 5578 Begin_load_query 1 6182 ;file_id=2;block_len=581 +master-bin.000001 6182 Execute_load_query 1 6335 use `test`; LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t4(a) SET b = 'master' ;file_id=2 +master-bin.000001 6335 Query 1 6453 use `test`; INSERT INTO t4 VALUES ('Go Rin No Sho', 'master,slave') +master-bin.000001 6453 Query 1 6530 use `test`; DELETE FROM t4 +master-bin.000001 6530 Begin_load_query 1 137625 ;file_id=4;block_len=131072 +master-bin.000001 137625 Append_block 1 268720 ;file_id=4;block_len=131072 +master-bin.000001 268720 Append_block 1 399815 ;file_id=4;block_len=131072 +master-bin.000001 399815 Append_block 1 530910 ;file_id=4;block_len=131072 +master-bin.000001 530910 Append_block 1 662005 ;file_id=4;block_len=131072 +master-bin.000001 662005 Append_block 1 793100 ;file_id=4;block_len=131072 +master-bin.000001 793100 Append_block 1 924195 ;file_id=4;block_len=131072 +master-bin.000001 924195 Append_block 1 1055290 ;file_id=4;block_len=131072 +master-bin.000001 1055290 Append_block 1 1186385 ;file_id=4;block_len=131072 +master-bin.000001 1186385 Append_block 1 1196648 ;file_id=4;block_len=10240 +master-bin.000001 1196648 Execute_load_query 1 1196801 use `test`; LOAD DATA INFILE 'rpl_slave_skip_words.dat' INTO TABLE t4(a) SET b = 'master' ;file_id=4 +master-bin.000001 1196801 Query 1 1196919 use `test`; INSERT INTO t4 VALUES ('Go Rin No Sho', 'master,slave') +SELECT COUNT(*) FROM t4; +COUNT(*) +286721 +**** On Slave **** +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +-- Should only contain records marked 'master,slave' +SELECT * FROM t4 ORDER BY a; +a b +Go Rin No Sho master,slave +**** On Master **** +DELETE FROM t4; ==== Cleanup ==== **** On Master **** -DROP TABLE t1, t2; +DROP TABLE t1, t2, t3, t4; diff -Nru a/mysql-test/t/rpl_slave_skip.test b/mysql-test/t/rpl_slave_skip.test --- a/mysql-test/t/rpl_slave_skip.test 2007-12-20 15:26:05 +01:00 +++ b/mysql-test/t/rpl_slave_skip.test 2007-12-20 15:26:05 +01:00 @@ -1,4 +1,5 @@ source include/have_innodb.inc; +source include/have_debug.inc; source include/master-slave.inc; # This test is for checking that the use of SQL_SLAVE_SKIP_COUNTER @@ -13,8 +14,10 @@ # 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 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 ==== @@ -195,9 +198,137 @@ 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; + --echo ==== Cleanup ==== --echo **** On Master **** connection master; -DROP TABLE t1, t2; +DROP TABLE t1, t2, t3, t4; sync_slave_with_master; diff -Nru a/sql/slave.cc b/sql/slave.cc --- a/sql/slave.cc 2007-12-20 15:26:05 +01:00 +++ b/sql/slave.cc 2007-12-20 15:26:05 +01:00 @@ -3348,7 +3348,10 @@ if (rli->slave_skip_counter && !((type_code == INTVAR_EVENT || type_code == RAND_EVENT || - type_code == USER_VAR_EVENT) && + type_code == USER_VAR_EVENT || + type_code == BEGIN_LOAD_QUERY_EVENT || + type_code == APPEND_BLOCK_EVENT || + type_code == CREATE_FILE_EVENT) && rli->slave_skip_counter == 1) && #if MYSQL_VERSION_ID < 50100 /*