Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-1683

LP #1525407: Failed DROP DATABASE due FK constraint on master breaks slave

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Sveta Smirnova last update 19-07-2016 14:28:43

      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.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist