Another reason why SQL_SLAVE_SKIP_COUNTER is bad in MySQL

This post comes from Jervin Real at the MySQL Performance Blog.

It is everywhere in the world of MySQL that if your replication is broken because an event caused a duplicate key or a row was not found and it cannot be updated or deleted, then you can use ‘ STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;START SLAVE; ’ and be done with it. In some cases this is fine and you can repair the offending row or statements later on. But what if the statement is part of a multi-statement transaction? Well, then it becomes more interesting, because skipping the offending statement will cause the whole transaction to be skipped. This is well documented in the manual by the way. So here’s a quick example.

3 rows on the master:

master> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

2 on the slave:

slave> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  1 |   1 |
|  3 |   3 |
+----+-----+
2 rows in set (0.00 sec)

Execute a transaction on the master to break replication:

master> BEGIN;
Query OK, 0 rows affected (0.00 sec)
master> DELETE FROM t WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
master> DELETE FROM t WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
master> DELETE FROM t WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
master> COMMIT;
Query OK, 0 rows affected (0.01 sec)

Broken slave:

slave> show slave status \G
*************************** 1. row ***************************
...
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Delete_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000002, end_log_pos 333
...
1 row in set (0.00 sec)

An attempt to fix replication only caused bigger inconsistencies on slave:

slave> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Query OK, 0 rows affected (0.00 sec)
slave> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  1 |   1 |
|  3 |   3 |
+----+-----+
2 rows in set (0.00 sec)

This happens because the replication honors transaction boundaries, and is definitely something you should consider the next time you try to use this workaround on a broken slave. Of course, there is pt-table-checksum and pt-table-sync to rescue you when inconsistencies occur, however, prevention is always better than cure. Make sure to put safeguards in place to prevent your slaves from drifting.

Lastly, the example above is for ROW-based replication as my colleague pointed out, but can similarly happen with STATEMENT for example with a duplicate key error.  You can optionally fix the error above by temporarily setting slave_exec_mode to IDEMPOTENT so errors because of missing rows are skipped, but then again, it does not apply in all cases like an UPDATE statement that cannot be applied because the row on the slave is missing.

Here is a demonstration of the problem with STATEMENT-based replication:

master> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  4 |   1 |
|  6 |   3 |
+----+-----+
2 rows in set (0.00 sec)
slave> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  4 |   1 |
|  5 |   2 |
|  6 |   3 |
+----+-----+
3 rows in set (0.00 sec)
master> BEGIN;
Query OK, 0 rows affected (0.00 sec)
master> delete from t where id = 4;
Query OK, 1 row affected (0.00 sec)
master> insert into t values (5,2);
Query OK, 1 row affected (0.00 sec)
master> delete from t where id = 6;
Query OK, 1 row affected (0.00 sec)
master> COMMIT;
Query OK, 0 rows affected (0.15 sec)
slave> show slave status \G
*************************** 1. row ***************************
...
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '5' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t values (5,2)'
...
1 row in set (0.00 sec)
slave> stop slave; set global sql_slave_skip_counter = 1; start slave;
Query OK, 0 rows affected (0.05 sec)
slave> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  4 |   1 |
|  5 |   2 |
|  6 |   3 |
+----+-----+
3 rows in set (0.00 sec)