MySQL主從複製中斷報error code=1217錯誤解決

MySQL主從複製中斷報error code=1217錯誤解決

MySQL主從複製中斷，報“Error on master: message (format)='Cannot delete or update a parent row: a foreign key constraint fails' error code=1217” 錯誤。

前幾天，發現從庫掛了，具體報錯資訊如下：

分析思路

1. 因為我採用的是選擇性複製，只針對以下幾個庫進行複製： card,upay,deal,monitor,collect。所以，不太可能出現對於sas_basic的操作能複製到該從庫上。

2. 整個架構是1主2從，且都是選擇性複製，上面這個從庫是直接複製card,upay,deal,monitor,collect這幾個資料庫的資料，而另外一個從庫則是忽略上述庫，如下所示：

    懷疑是在上述schema下，執行了DROP TABLE IF EXISTS `sas_basic.old_channel_code`操作。

3. 於是根據報錯資訊查看了主庫binlog日誌的內容，發現是在sas_basic schema下操作的。

    use `sas_basic`/*!*/;

困惑

針對sas_basic的操作為什麼會反映到不複製它操作的從庫上。

 

PS：根據上述報錯資訊，中途還懷疑主從庫的外鍵定義不一致導致上述問題的產生，後來查看，發現主從庫的外鍵定義是一致的。

原因

上次利用set global sql_slave_skip_counter=1跳過後，今天又碰到了這個問題，深入其中，才發現這是MySQL的一個bug：https://bugs.mysql.com/bug.php?id=77684

但是這個bug中涉及到的版本是5.6.25, 5.6.27。而我生產資料庫是5.6.26。於是，在測試機上搭建環境，看能否重現問題。

重現現場

還是一主兩從，其中一個從設定replicate-ignore-db=test，另外一個從設定replicate-do-db=test。

在主中執行以下語句：

CREATE DATABASE `db1`;

USE `db1`;

CREATE TABLE `table1` (`ID` bigint(20) primary key) ENGINE=InnoDB;

CREATE TABLE `table2` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `DIVISION_ID` bigint(20) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FK_TABLE1_DIVISION_1` (`DIVISION_ID`), CONSTRAINT `FK_TABLE1_DIVISION_1` FOREIGN KEY (`DIVISION_ID`) REFERENCES `table1` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB;

DROP TABLE IF EXISTS `table1`;

結果，replicate-ignore-db=test這個從庫中複製正常，但replicate-do-db=test這個從庫的複製卻出現問題。報如下錯誤：

 Last_SQL_Error: Query caused different errors on master and slave.    Error on master: message (format)='Cannot delete or update a parent row: a foreign key constraint fails' error code=1217 ; Error on slave: actual message='no error', error code=0. Default database: 'db1'. Query: 'DROP TABLE IF EXISTS `table1` /* generated by server */'
  Replicate_Ignore_Server_Ids:

完美重現現場。

提交這個Bug的哥們同時也給出了一種替代方案

Suggested fix:
The problem seems to be related to the "USE" above as the following works as expected:

CREATE DATABASE `db1`;
CREATE TABLE `db1`.`table1` (`ID` bigint(20) primary key) ENGINE=InnoDB;
CREATE TABLE `db1`.`table2` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `DIVISION_ID` bigint(20) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FK_TABLE1_DIVISION_1` (`DIVISION_ID`), CONSTRAINT `FK_TABLE1_DIVISION_1` FOREIGN KEY (`DIVISION_ID`) REFERENCES `db1`.`table1` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB;
DROP TABLE IF EXISTS `db1`.`table1`;

however if you add an USE `db1` after the CREATE DATABASE statement the replication error will follow.

即在其它schema中刪除該表。

但經過測試，無論是在其它schema中執行該操作還是不指定資料庫執行該操作，均會使得複製中斷。

總結：

1. 該Bug不僅僅在replicate-ignore-db會觸發，在replicate-do-db中也會觸發。

2. 官方承諾會在5.6.30和5.7.12修複，具體未測。

本文永久更新連結地址：