MySQL Bugs: #2167: Foreign key references are not dropped correctly on replication

Bug #2167	Foreign key references are not dropped correctly on replication
Submitted:	18 Dec 2003 19:08	Modified:	8 Feb 2004 15:49
Reporter:	Todd Buiten	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.15	OS:	Linux (Linux 2.4.22)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
If you use binlogs to replicate databases from one machine to another, DROP FOREIGN KEY fails to replicate correctly.

How to repeat:
Do the following on machine A:

use test;
create table foo (col1 int not null, index(col1)) type=innodb;
create table bar (col1 int not null, index (col1), foreign key (col1) references foo (col1)) type=innodb;
show create table bar;
alter table bar drop foreign key *internal_fk_id*;

Take the binary logs to a second machine and replay them.  Notice that mysql breaks with the following error message: "ERROR 1025 at line 17: Error on rename of './test/bar' to './test/#sql2-88b-3d3' (errno: 140)"

Suggested fix:
I'm hoping that I'm wrong, but it seems like there's a fundamental design flaw in the DROP FOREIGN KEY syntax with respect to replication.  By using an internal foreign key ID, the statement can't be replicated from one machine to another if the original table was either replicated using binlogs or created from a mysqldump.  I tried using the "CONSTRAINT symbol" syntax to assign some kind of ID to the foreign key that I could pass along in the DROP FOREIGN KEY command, but the identifier that I passed in appeared to be replaced by a new internally generated ID.  Am I missing something, or is this a real bug?  If it's a real bug then my suggested fix is to somehow add a mechanism to allow me to assign the foreign key identifier so that I can use it in both the CREATE TABLE and ALTER TABLE commands.

Thanks

- todd

Hi!

Yes, this problem was reported on the mailing list a couple of weeks ago. The fix will not happen too quickly, because at the same time I want to make InnoDB to remember the name which the user gave to the constraint.

A possible solution is that we generate new constraint names per table, and based on what constraint names there already are in the table. For example,

databasename_tablename_15
databasename_tablename_16
...

Then the names in the slave will agree with the master.

Regards,

Heikki

I have now documented this in the known bug section in MySQL.
This should be fixed in 4.1 or 5.0 when MySQL will be able to store the foreign key defintion independent of InnoDB and also generate repeatable constraint names for the foreign key definition.

Hi!

I have now changed the constraint name handling:

1) If the user gives a name to a constraint, InnoDB remembers that name, and you can use it to DROP the foreign key.
2) Otherwise, InnoDB generates an internal foreign key constraint name of the form
tablename_ibfk_<nth generated constraint name for this table>
and you can use this name to DROP the foreign key.

These changes should ensure that in replication the slave has the same constraint names as the master, and ALTER TABLE ... DROP FOREIGN KEY ... does not break replication.

Note that constraint names in the same database must differ. Note also that in RENAME table InnoDB renames also those constraints whose name was generated as in 2).

The bug fix will appear in 4.0.18 or 4.0.19, depending on which 4.0 source tree snapshot is used to build release 4.0.18.

Regards,

Heikki

The same bug is open again
in Version 4.1.1a-alpha-nt