enterprise-support team mailing list archive

[Jericho Rivera <jericho.rivera@xxxxxxxxxxx>]

Public bug reported:

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.

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

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


** Tags: i56487

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

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

-- 
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/1475107

Title:
  DROP TABLE IF EXISTS may brake replication if slave has replication
  filters

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