MySQL master-slave replication interrupts, reported "Error on master:message (format) = ' cannot delete or update a parent ROW:A FOREIGN KEY constraint fails ' err or code=1217 "error

A few days ago, found hanging from the library, the specific error message is as follows:

Analysis Ideas

1. Because I am using selective replication, it is only replicated for the following libraries: Card,upay,deal,monitor,collect. Therefore, it is unlikely that the operation for Sas_basic can be copied to the slave library.

2. The entire architecture is 1 Master 2 from, and is selective replication, the above from the library is directly copied card,upay,deal,monitor,collect the data of these databases, and the other from the library is to ignore the above library, as follows:

It is suspected that under the above schema, the drop TABLE IF EXISTS ' Sas_basic.old_channel_code ' operation is performed.

3. Then according to the error message to view the main library Binlog log content, found to be operating under the Sas_basic schema.

Use ' Sas_basic '/*!*/; confusedWhy the operation against Sas_basic is reflected in the slave library that does not replicate its operations. PS: According to the above error information, midway also suspected that the foreign key definition of the master-slave library caused the above problems, and later viewed, found that the foreign key definition of the master-slave library is consistent. causeThe last time after using set global Sql_slave_skip_counter=1 jump, today again encountered this problem, in-depth, only to find this is a MySQL bug:https://bugs.mysql.com/bug.php?id= 77684 but the version involved in this bug is 5.6.25, 5.6.27. And my production database is 5.6.26. Then, build the environment on the test machine to see if the problem can be reproduced. Reproduce the sceneOr a master two from, one from the set replicate-ignore-db=test, the other one from the set replicate-do-db=test. Execute the following statement in the master:

CREATE DATABASE' DB1 '; Use' DB1 ';CREATE TABLE' Table1 ' (' ID ')bigint( -)Primary Key) ENGINE=InnoDB;CREATE TABLE' Table2 ' (' ID ')bigint( -) not NULLAuto_increment, ' division_id 'bigint( -)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 ';

As a result, replicate-ignore-db=test this copy from the library is normal, but replicate-do-db=test this copy from the library is problematic. The following error is reported:

 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  Span style= "color: #800000;" > ' , error code=0 . Default database:   ' . Query:   "  Replicate_ignore_server_ Ids:  

Recreate the scene perfectly.

The guy who submitted the bug also gave an alternative.

suggested fix:the problem seems to being related to the" Use"above as the following works as Expected:create DATABASE ' db1 '; CREATE TABLE ' db1 '. ' Table1 ' (' ID ' bigint ( -) primary key) engine=InnoDB; CREATE TABLE ' db1 '. ' Table2 ' (' ID ' bigint ( -) Not NULL auto_increment, ' division_id ' bigint ( -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 '; howeverifYou add a use ' db1 ' after the CREATE DATABASE statement the replication error would follow.

That is, delete the table in the other schema.

However, it has been tested to cause replication to break, either by executing the operation in another schema or by not specifying the database to perform the operation.

Summarize:

1. This bug is triggered not only in replicate-ignore-db, but also in replicate-do-db.

2. Official commitments are to be repaired in 5.6.30 and 5.7.12, not specifically tested.

MySQL master-slave replication interrupts, reported "Error on master:message (format) = ' cannot delete or update a parent ROW:A FOREIGN KEY constraint fails ' err or code=1217 "error