SQL_SLAVE_SKIP_COUNTER がまずいもう一つの理由

レプリケーションがおかしくなって、キーの重複や対象行が存在しないといった問題で行の更新や削除が出来なくなった時、 STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; を実行して解決するというのは、MySQLの世界ではよくあることだ。これがうまくいって、不正な行やSQL文を修正できることもあるだろう。しかし、その文が複数の文からなるトランザクションの一部だったらどうなるだろう？そうすると、面白いことに、不正な行をスキップすることで、トランザクション全体がスキップされてしまう。これは、 ドキュメント にはちゃんと書かれている。例を見てみよう。

マスタには3行存在している。

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

スレーブには2行ある。

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

マスタで、レプリケーションが壊れてしまうトランザクションを実行する。

 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)

スレーブを見るとレプリケーションが切れている。

 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)

レプリケーションを直そうとすると、マスタとスレーブの間で不整合がひどくなってしまう。

 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)

これは、レプリケーションはトランザクションの境界を尊重しているからで、今度スレーブのレプリケーションが切れた時にこの方法を使うなら、これをどうにかしなければならない。不整合が発生した時には、 pt-table-checksum や pt-table-sync がもちろん役に立つだろうが、あとで修復するより予防できた方がいいに決まっている。 スレーブが路頭に迷うことのないよう、 予防策 を忘れないようにしよう。

最後に、上で挙げたのは私の同僚が指摘したように行ベースのレプリケーションでの例だが、キーの重複エラーはステートメントベースのレプリケーションでも同じように起こり得る。上の例は、 slave_exec_mode を一時的に IDENMPOTENT に変更して、行が存在しないのが原因のエラーをスキップしてしまうことでも解決できる。しかし、スレーブに該当の行が存在しないことによる更新時にいつでもあてはまるわけではない。

以下は、ステートメンスベースのレプリケーションで起こる問題の例だ。

 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)