MySQL Bugs: #28618: Skipping into the middle of a group with SQL_SLAVE_SKIP

Bug #28618	Skipping into the middle of a group with SQL_SLAVE_SKIP_COUNTER is possible
Submitted:	23 May 2007 12:30	Modified:	28 Nov 2007 18:12
Reporter:	Mats Kindahl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:	Mats Kindahl	CPU Architecture:	Any
Tags:	bfsm_2007_10_18

Description:
It is possible to use SQL_SLAVE_SKIP_COUNTER to skip events so that you end up in the middle of a group. This have the potential for crashing the server, since it might skip either table map events (for row-based) or context events (for statement-based).

How to repeat:
connection slave;
STOP SLAVE;
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;
SHOW BINLOG EVENTS;

# These tables should be changed
SELECT * FROM t1;
SELECT * FROM t2;
save_master_pos;

connection slave;
# Stop when reaching the the 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 #
query_vertical SHOW SLAVE STATUS;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;
sync_with_master;

# These values should be what was inserted, not what was updated, i.e.,
# the entire group should be skipped.
SELECT * FROM t1;
SELECT * FROM t2;

connection master;
DROP TABLE t1, t2;
sync_slave_with_master;

Suggested fix:
If the result of skipping an events ends up in the middle of a group, print a warning and continue skipping until the end of the group.

Correcting priority to match that of BUG#12691

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27332

ChangeSet@1.2581, 2007-05-25 12:56:42+02:00, mats@kindahl-laptop.dnsalias.net +2 -0
  BUG#28618 (Skipping into the middle of a group with
  SQL_SLAVE_SKIP_COUNTER is possible):
  
  By setting the SQL_SLAVE_SKIP_COUNTER it was possible to start the
  from the middle of a group. This patch adds code so that events that
  do not end a statement are ignored instead of skip counted when the
  slave skip counter is 1.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27837

ChangeSet@1.2581, 2007-05-31 20:01:06+02:00, mats@kindahl-laptop.dnsalias.net +2 -0
  BUG#28618 (Skipping into the middle of a group with
  SQL_SLAVE_SKIP_COUNTER is possible):
  
  By setting the SQL_SLAVE_SKIP_COUNTER it was possible to start the
  from the middle of a group. This patch adds code so that events that
  do not end a statement are ignored instead of skip counted when the
  slave skip counter is 1.

Pushed into 5.1.20-beta

Noted in 5.1.20 changelog.

It was possible to set SQL_SLAVE_SKIP_COUNTER such that the slave 
would jump into the middle of an event group.

Reopened since there are new failing test cases.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35750

ChangeSet@1.2578, 2007-10-17 15:56:08+02:00, mats@kindahl-laptop.dnsalias.net +6 -0
  BUG#28618 (Skipping into the middle of a group with SQL_SLAVE_SKIP_COUNTER
  is possible):
  
  When skipping the beginning of a transaction starting with BEGIN, the OPTION_BEGIN
  flag was not set correctly, which caused the slave to not recognize that it was
  inside a group. This patch sets the OPTION_BEGIN flag for BEGIN, COMMIT, ROLLBACK,
  and XID events. It also adds checks if inside a group before decreasing the
  slave skip counter to zero.
  
  Begin_query_log_event was not marked that it could not end a group, which is now
  corrected.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35786

ChangeSet@1.2578, 2007-10-17 20:19:47+02:00, mats@kindahl-laptop.dnsalias.net +8 -0
  BUG#28618 (Skipping into the middle of a group with SQL_SLAVE_SKIP_COUNTER
  is possible):
  
  When skipping the beginning of a transaction starting with BEGIN, the OPTION_BEGIN
  flag was not set correctly, which caused the slave to not recognize that it was
  inside a group. This patch sets the OPTION_BEGIN flag for BEGIN, COMMIT, ROLLBACK,
  and XID events. It also adds checks if inside a group before decreasing the
  slave skip counter to zero.
  
  Begin_query_log_event was not marked that it could not end a group, which is now
  corrected.

The code looks good and works as expected. There was one minor issue with the rpl_skip_slave test. Please change the line:

eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|';

to:

eval LOAD DATA INFILE '../tmp/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|';

This is necessary to ensure the results of the test are platform neutral. Patch approved pending this minor change.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35914

ChangeSet@1.2578, 2007-10-19 14:18:41+02:00, mats@kindahl-laptop.dnsalias.net +10 -0
  BUG#28618 (Skipping into the middle of a group with SQL_SLAVE_SKIP_COUNTER
  is possible):
  
  When skipping the beginning of a transaction starting with BEGIN, the OPTION_BEGIN
  flag was not set correctly, which caused the slave to not recognize that it was
  inside a group. This patch sets the OPTION_BEGIN flag for BEGIN, COMMIT, ROLLBACK,
  and XID events. It also adds checks if inside a group before decreasing the
  slave skip counter to zero.
  
  Begin_query_log_event was not marked that it could not end a group, which is now
  corrected.

approved after discussions on #rep. Suggested to extend tests to include more cases with skipping more than one binlog group.

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.23 changelog as:

        It was possible to set SQL_SLAVE_SKIP_COUNTER
        such that the slave would jump into the middle of a transaction.
        This fix improves on one made for this bug in MySQL 5.1.20; the
        previous fix insured that the slave could not be made to jump
        into the middle of an event group, but the slave failed to
        recognize that BEGIN, COMMIT, and ROLLBACK
        statements could begin or end an event group.

Documented bugfix in 6.0.4 changelog as:

        It was possible to set SQL_SLAVE_SKIP_COUNTER
        such that the slave would jump into the middle of an event
        group, including possibly a transaction.