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
Tags: bfsm_2007_10_18

[23 May 2007 12:30] Mats Kindahl
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.
[24 May 2007 7:39] Mats Kindahl
Correcting priority to match that of BUG#12691
[25 May 2007 11:08] Bugs System
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.
[31 May 2007 18:01] Bugs System
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.
[21 Jun 2007 20:15] Bugs System
Pushed into 5.1.20-beta
[23 Jul 2007 20:07] Paul Dubois
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.
[10 Sep 2007 8:44] Lars Thalmann
Reopened since there are new failing test cases.
[17 Oct 2007 13:56] Bugs System
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.
[17 Oct 2007 18:20] Bugs System
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.
[17 Oct 2007 19:11] Chuck Bell
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.
[19 Oct 2007 12:19] Bugs System
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.
[21 Oct 2007 15:52] Andrei Elkin
approved after discussions on #rep. Suggested to extend tests to include more cases with skipping more than one binlog group.
[27 Nov 2007 10:52] Bugs System
Pushed into 5.1.23-rc
[27 Nov 2007 10:54] Bugs System
Pushed into 6.0.4-alpha
[28 Nov 2007 18:12] Jon Stephens
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.