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

LP #1475107: DROP TABLE IF EXISTS may brake replication if slave has replication filters

    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 Jericho Rivera last update 27-08-2015 09:04:22

      Copied from original bug description in mysql bug #77684 reported by FernandoL

      Description:
      If a replica has replication filters on a given database and DROP TABLE IF EXISTS is issued on a table that holds a key being used as Foreign Key by another table the statement will fail as expected with error:

      ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

      however this will break replication on the replica with:

      Last_SQL_Error: Query caused different errors on master and slave. Error on master: message (format)='Cannot delete or update a parent row: a foreign key constraint fails' error code=1217 ; Error on slave: actual message='no error', error code=0. Default database: 'db1'. Query: 'DROP TABLE IF EXISTS `table1` /* generated by server */'

      How to repeat:
      Setup master with:

      binlog_format=STATEMENT
      or
      binlog_format=ROW

      Setup replica with:

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

      Then run on the master the following statements:

      CREATE DATABASE `db1`;

      USE `db1`;

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

      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 `table1` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB;

      DROP TABLE IF EXISTS `db1`.`table1`;

      Suggested fix:
      The problem seems to be related to the "USE" above as the following works as expected:

      CREATE DATABASE `db1`;
      CREATE TABLE `db1`.`table1` (`ID` bigint(20) primary key) ENGINE=InnoDB;
      CREATE TABLE `db1`.`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 `db1`.`table1` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB;
      DROP TABLE IF EXISTS `db1`.`table1`;

      however if you add an USE `db1` after the CREATE DATABASE statement the replication error will follow.

        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