Failed DROP DATABASE due FK constraint on master breaks slave
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_
STOP SLAVE;
START SLAVE;
How to repeat:
--source include/
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_
--sync_
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_
--connection master
--error 1217
DROP DATABASE db2;
--sync_
--vertical_results
show slave status;
Option file:
--replicate-
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 |
Maybe related (but not same) upstream bug fixed: https:/ /bugs.mysql. com/bug. php?id= 77684