enterprise-support team mailing list archive

[Sveta Smirnova <sveta@xxxxxxxxxxxxx>]

Public bug reported:

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.

** Affects: mysql-server
     Importance: Unknown
         Status: Unknown

** Affects: percona-server
     Importance: Undecided
         Status: Confirmed

** Affects: percona-server/5.5
     Importance: Undecided
         Status: Confirmed

** Affects: percona-server/5.6
     Importance: Undecided
         Status: Confirmed

** Affects: percona-server/5.7
     Importance: Undecided
         Status: Confirmed


** Tags: i62857

** Also affects: percona-server/5.5
   Importance: Undecided
       Status: New

** Also affects: percona-server/5.7
   Importance: Undecided
       Status: New

** Also affects: percona-server/5.6
   Importance: Undecided
       Status: Confirmed

** Bug watch added: MySQL Bug System #79610
   http://bugs.mysql.com/bug.php?id=79610

** Also affects: mysql-server via
   http://bugs.mysql.com/bug.php?id=79610
   Importance: Unknown
       Status: Unknown

** Changed in: percona-server/5.5
       Status: New => Confirmed

** Changed in: percona-server/5.7
       Status: New => Confirmed

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1525407

Title:
  Failed DROP DATABASE due FK constraint on master breaks slave

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1525407/+subscriptions