MySQL Bugs: #79610: Failed DROP DATABASE due FK constraint on master breaks slave

Bug #79610	Failed DROP DATABASE due FK constraint on master breaks slave
Submitted:	11 Dec 2015 21:18	Modified:	7 May 2019 9:54
Reporter:	Sveta Smirnova (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.46, 5.6.27, 5.7.9, 5.5.48, 5.6.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

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;

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.

Option file: --replicate-ignore-db=db1 --replicate-wild-ignore-table=db1.%

Hello Sveta,

Thank you for the report and test case.
Verified as described with 5.5.48, 5.6.28 builds.

Thanks,
Umesh

test results

Attachment: 79610.results (application/octet-stream, text), 30.76 KiB.

Can you please test this with a drop table and delete a row with parent child relation on master. I doubt if it will work in that case too.

If just replace in my test "DROP DATABASE db2;" with "DROP TABLE db2.a1, db2.a2, db2.table0, db2.table1;" slave would not fail, because DROP TABLE statement will be correctly prefixed with USE db1:

show binlog events;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       120     Server ver: 5.6.28-76.1-debug-log, Binlog ver: 4
...
master-bin.000001       1138    Query   1       1298    use `db1`; DROP TABLE `db2`.`a1`,`db2`.`a2`,`db2`.`table0`,`db2`.`table1` /* generated by server */
show slave status;

In case of DROP DATABASE it not only creates dangerous statement, it also calls this command from wrong database:

show binlog events;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       120     Server ver: 5.6.28-76.1-debug-log, Binlog ver: 4
...
master-bin.000001       1138    Query   1       1249    use `db2`; DROP TABLE IF EXISTS `a1`,`a2`,`table0`

Another interesting finding: if I just replace "DROP DATABASE db2;" with "USE db2; DROP TABLE db2.a1, db2.a2, db2.table0, db2.table1;" or "USE db2; DROP TABLE a1, a2, table0, table1;" slave, again, would not fail.

But if I remove from the list last table, table1, which caused error on master (" DROP TABLE IF EXISTS a1, a2, table0"), slave will fail again.

Bug 79610 fix for 8.0.2 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug79610-8.0.2.patch (application/octet-stream, text), 18.09 KiB.

The latest contributed fix applies cleanly and works on 8.0.4 too.

Bug 79610 fix for 8.0.11 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug79610-8.0.11.patch (application/octet-stream, text), 19.67 KiB.

Thank you for the contributions!

Regards,
Umesh

Fixed in 8.0.12?

commit d408eb3145e19ed95e01804adccd27b0b1bb612c
Author: Dmitry Lenev <dmitry.lenev@oracle.com>
Date:   Tue Apr 10 13:07:52 2018 +0300

    Bug#27821060 "NEWDD FK: DROP TABLES/DATABASE SHOULD CHECK FOR FKS".
    
    After we have started to store information about foreign keys in the
    New Data Dictionary it became possible to move checks of foreign key
    definition validity from the storage engine to SQL-layer and thus
    reduce code duplication and simplify SE implementation.
    
    This patch moves check that disallows dropping of parent table in
    a foreign key without prior dropping of child table from InnoDB
    SE to SQL-layer code implementing DROP TABLES/DROP DATABASE.
    
    Such check now happens before trying to delete any tables mentioned
    in DROP TABLES statement/belonging to schema to be dropped. So DROP
    TABLES/DROP DATABASE no longer have any side-effect even on tables
    in SEs which don't support atomic DDL when they fail due to this check.
    
    The check was also relaxed to allow dropping of parent and child table
    in arbitrary order as long as they are dropped by the same DROP TABLES
    statement. This is possible thanks to the fact that both our engines
    supporting foreign keys also support atomic DDL.

    Finally, error message which is emitted when user attempts to drop
    parent table without dropping child was changed to more verbose one.
    
    New test coverage for this check was added and existing tests were
    adjusted accordingly. Also, some test cases which previously used
    foreign key dependency as a way to fail DROP TABLES/DATABASE in the
    middle of their execution had to be adjusted to rely on error injection
    instead.

Posted by developer:
 
Hello!

The problem is no longer repeatable in recent 8.0 versions.
Here is what slightly modified test case gives me on 8.0.18-git:

===

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

# The below DROP DATABASE fails with a new error in 8.0
# ERROR HY000: Cannot drop table 'table1' referenced by a foreign key constraint 'FK_TABLE1_DIVISION_1' on table 'table2'.
--error 3730
DROP DATABASE db2;

SHOW BINLOG EVENTS;
# Returns.
# Log_name       Pos     Event_type      Server_id       End_log_pos     Info
# master-bin.000001      4       Format_desc     1       124     Server ver: 8.0.18-tr-cluster, Binlog ver: 4
# master-bin.000001      124     Previous_gtids  1       155
# master-bin.000001      155     Anonymous_Gtid  1       232     SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
# master-bin.000001      232     Query   1       339     CREATE DATABASE `db2` /* xid=196 */
# master-bin.000001      339     Anonymous_Gtid  1       416     SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
# master-bin.000001      416     Query   1       525     use `db2`; CREATE TABLE a1(f1 INT) /* xid=198 */
# master-bin.000001      525     Anonymous_Gtid  1       602     SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
# master-bin.000001      602     Query   1       711     use `db2`; CREATE TABLE a2(f1 INT) /* xid=199 */
# master-bin.000001      711     Anonymous_Gtid  1       788     SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
# master-bin.000001      788     Query   1       939     use `db2`; CREATE TABLE `table0` (`ID` bigint(20) primary key) ENGINE=InnoDB /* xid=200 */
# master-bin.000001      939     Anonymous_Gtid  1       1016    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
# master-bin.000001      1016    Query   1       1167    use `db2`; CREATE TABLE `table1` (`ID` bigint(20) primary key) ENGINE=InnoDB /* xid=201 */
# master-bin.000001      1167    Anonymous_Gtid  1       1244    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
# master-bin.000001      1244    Query   1       1349    CREATE DATABASE db1 /* xid=202 */
# master-bin.000001      1349    Anonymous_Gtid  1       1428    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
# master-bin.000001      1428    Query   1       1812    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 /* xid=204 */

====

Notice that there is no trace of failed DROP DATABASE in binary log at all.
This is because after fix for bug#27821060 "NEWDD FK: DROP TABLES/DATABASE
SHOULD CHECK FOR FKS" in 8.0.12 both DROP DATABASE now checks first if
any tables to be deleted is referenced by foreign key outside of database
and fails early, without side-effects if it is.

Moreover, the problem should not be repeatable in 8.0 even before this fix
as thanks to introduction of atomic DDL support there, partial DROP DATABASE
statements on InnoDB-only databases are impossible, as error in the middle
of such statement causes its full rollback (and the problem is not relevant
for MyISAM tables as they don't support foreign keys anyway).

Taking into account the above I am closing this bug as fixed in 8.0.12.
Moving it to Documenting state to let Documentation team to decide if
they want to emphasize the fact that DROP DATABASE/TABLES now check for
referenced FKs before deleting anything in release notes.

Fixed in 8.0.12.

Posted by doc responsible: Margaret Fisher

Added bug number and extra info to changelog entry for Bug #27821060 / 90320,
which now reads:

Checking for foreign key relationships by DROP TABLE and DROP
DATABASE was improved. The check now takes place before any tables are
deleted,
so that in the event of a problem the statement fails before making any
changes. Parent and child tables now can be dropped in arbitrary order, as
long as they are dropped by the same DROP TABLE statement. In addition, error
reporting was improved for attempts to drop a parent table without dropping a
child table.