Failed DROP DATABASE due FK constraint on master breaks slave

Bug #1525407 reported by Sveta Smirnova
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Fix Released
Medium
Laurynas Biveinis
5.6
Fix Released
Medium
Laurynas Biveinis
5.7
Fix Released
Medium
Laurynas Biveinis

Bug Description

Description:
If DROP DATABASE on master failed it is converted into DROP table1, table2, ... statement. If master and slave have completely same structure this is OK. But if slave has tables which reference tables, dropped on master, this statement will fail on slave and break replication. If DROP DATABASE completes on master successfully it will be replicated as DROP DATABASE to slave and completes too.

Workaround:

SET GLOBAL foreign_key_checks=0;
STOP SLAVE;
START SLAVE;

How to repeat:
--source include/master-slave.inc

CREATE DATABASE `db2`;

USE `db2`;

create table a1(f1 int);
create table a2(f1 int);

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

create database db1;
use db1;

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 `db2`.`table1` (`ID`) ON DELETE no action ) ENGINE=InnoDB;

--sync_slave_with_master
show databases;
use db2;
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 `db2`.`table0` (`ID`) ON DELETE no action ) ENGINE=InnoDB;

--connection master
--error 1217
DROP DATABASE db2;

--sync_slave_with_master
--vertical_results
show slave status;

Option file:

--replicate-ignore-db=db1 --replicate-wild-ignore-table=db1.%

Suggested fix:
Ideally: make DROP DATABASE transactional, drop either all tables or nothing.

Alternatively: check all constraints before dropping tables on master, so there would not be situation when some tables dropped and others not.

Or: replicate DROP TABLE separately. This way replication users will hit same issue, but at least it will be easier and safer to fix.

tags: added: upstream
Revision history for this message
Sveta Smirnova (svetasmirnova) wrote :

Maybe related (but not same) upstream bug fixed: https://bugs.mysql.com/bug.php?id=77684

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1683

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.