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

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

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

 

分析思路

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修復，具體未測。