MySQL master-slave replication interrupt error code = 1217 error resolved

MySQL master-slave replication interrupt error code = 1217 error resolved

MySQL master-slave replication is interrupted and the Error "error on master: message (format) = 'could not delete or update a parent row: a foreign key constraint fails 'error code = 1217" is reported.

A few days ago, the slave database was found to have crashed. The specific error message is as follows:

Analysis ideas

1. Because I use selective replication, I only copy the following databases: card, upay, deal, monitor, and collect. Therefore, it is unlikely that sas_basic operations can be copied to the slave database.

2. the entire architecture is one master node and two slave nodes, and the slave database above directly copies the data of the databases card, upay, deal, monitor, and collect, the other slave database ignores the above database, as shown below:

It is suspected that the drop table if exists 'sas _ basic. old_channel_code 'operation is executed in the schema above.

3. view the binlog content of the master database based on the error message and find that the operation is performed in sas_basic schema.

Use 'sas _ basic '/*! */;

Confusions

Why does the sas_basic operation reflect the slave database that does not copy its operation.

 

PS: according to the above error message, the foreign key definitions of the master database and slave database are suspected to be inconsistent in the middle, resulting in the above problem. Later I checked and found that the foreign key definitions of the master database and slave database are consistent.

Cause

The last time I used set global SQL _slave_skip_counter = 1 to skip, today I encountered this problem, deep in it, I found this is a MySQL bug: https://bugs.mysql.com/bug.php? Id = 77684

However, the versions involved in this bug are 5.6.25 and 5.6.27. My production database is 5.6.26. Therefore, you can build an environment on the testing machine to see if the problem can be reproduced.

Reproduction site

Or one master and two slaves, one of which sets replicate-ignore-db = test, and the other sets replicate-do-db = test.

Execute the following statement in the master:

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 ';

As a result, the slave database replicate-ignore-db = test is copied normally, but the slave database replicate-do-db = test has a problem. The following error is reported:

Last_ SQL _Error: Query caused different errors on master and slave. error on master: message (format) = 'could not 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:

Perfectly reproduce the scene.

The buddy who submitted this Bug also provided an alternative solution.

Suggested fix:
The problem seems to be related to the "USE" abve 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.

Delete the table from other schemas.

However, after testing, whether the operation is performed in other schemas or the database is not specified to execute the operation, the replication is interrupted.

Summary:

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

2. The official commitment will be fixed in 5.6.30 and 5.7.12, but not tested.

This article permanently updates the link address: