Replication of foreign key constraint names (InnoDB) inconsistent

[Chris Tucker]

 >Description:
   It is possible to get into a situation where foreign key constraints 
on a replication slave have different labels to the same foreign key 
constraint on the master.  This normally causes replication to fail when 
a drop of a foreign key on the master is attempted (although presumably 
it could, in a particularly unfortunate circumstance, drop the wrong 
constraint for a given table).

 >How-To-Repeat:
   Run the following on the master:
CREATE TABLE t1 (
    a INT NOT NULL PRIMARY KEY
);

   Then, on the slave run:
CREATE TABLE t2 (
    b INT NOT NULL PRIMARY KEY,
    FOREIGN KEY (b) REFERENCES t1 (a)
);

   And finally, on the master, run:
CREATE TABLE t3 (
    c INT NOT NULL PRIMARY KEY,
    FOREIGN KEY (c) REFERENCES t1 (a)
);

If you now go into the master and look at the SHOW CREATE TABLE output 
for table t3 you should see something like:

*************************** 1. row ***************************
        Table: t3
Create Table: CREATE TABLE `t3` (
   `c` int(11) NOT NULL default '0',
   PRIMARY KEY  (`c`),
   CONSTRAINT `0_15` FOREIGN KEY (`c`) REFERENCES `t1` (`a`)
) TYPE=InnoDB


while in the slave you will see:

*************************** 1. row ***************************
        Table: t3
Create Table: CREATE TABLE `t3` (
   `c` int(11) NOT NULL default '0',
   PRIMARY KEY  (`c`),
   CONSTRAINT `0_17` FOREIGN KEY (`c`) REFERENCES `t1` (`a`)
) TYPE=InnoDB

Note the constraint name on the master is `0_15` while on the slave it 
is `0_17`.


 >Fix:

There are two workarounds at present:

1) Don't ever create a foreign key anywhere on the slave (unless it 
comes through the binlog from the master).  This works fine until 
someone makes a mistake...

2) Accept that replication will halt.  Drop the key from the master, 
then manually drop the key from the slave; set the slave skip counter to 
1 and restart the slave.  This is something of a problem if 100% uptime 
is expected/required.

I would imagine this problem would also manifest if restoring a slave 
from a mysqldump of a master, or using any other mechanism that is not 
guaranteed to create the foreign keys in exactly the order they were 
created on the master.

Making the parser and InnoDB foreign key management systems aware of 
constraint names being indicated in the foreign key definition would 
eliminate this problem: if I could reliably use a SHOW CREATE TABLE on 
the master and be guaranteed that either the table will create with the 
same foreign key labels, or will fail with an error if the labels are 
already in use elsewhere, then the problem will go away.  If the 
constraint names also held the name of the table to which they are tied, 
the problem of collisions would also disappear: for example, CONSTRAINT 
`test_t3_0_15` would be a suitable label for the key on table t3 in 
database test.

 >Submitter-Id:	<submitter ID>
 >Originator:	chris.tucker@stripped
 >Organization:
  <organization of PR author (multiple lines)>
 >MySQL support: none
 >Synopsis:	Foreign key label replication inconsistent
 >Severity:	serious
 >Priority:	<[ low | medium | high ] (one line)>
 >Category:	mysql
 >Class:		<[ sw-bug | doc-bug | change-request | support ] (one line)>
 >Release:	mysql-4.0.15-standard (Official MySQL-standard binary)
 >Server: /usr/local/bin/mysqladmin  Ver 8.40 Distrib 4.0.15, for 
pc-linux on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version		4.0.15-standard-log
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/tmp/mysql.sock
Uptime:			10 min 59 sec

Threads: 3  Questions: 30  Slow queries: 0  Opens: 17  Flush tables: 1 
Open tables: 1  Queries per second avg: 0.046
 >C compiler:    2.95.3
 >C++ compiler:  2.95.3
 >Environment:
	<machine, os, target, libraries (multiple lines)>
System: Linux localhost.localdomain 2.4.18-3smp #1 SMP Thu Apr 18 
07:27:31 EDT 2002 i686 unknown
Architecture: i686

Some paths:  /usr/local/bin/perl /usr/bin/make /usr/bin/gmake 
/usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-113)
Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=pentiumpro'  CXX='gcc' 
CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors'  LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx    1 root     root           13 Oct 13 16:32 /lib/libc.so.6 
-> libc-2.2.5.so
-rwxr-xr-x    1 root     root      1260480 Mar  5  2003 /lib/libc-2.2.5.so
-rw-r--r--    1 root     root      2312442 Mar  5  2003 /usr/lib/libc.a
-rw-r--r--    1 root     root          178 Mar  5  2003 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql' 
'--localstatedir=/usr/local/mysql/data' 
'--libexecdir=/usr/local/mysql/bin' '--with-comment=Official 
MySQL-standard binary' '--with-extra-charsets=complex' 
'--with-server-suffix=-standard' '--enable-thread-safe-client' 
'--enable-local-infile' '--enable-assembler' '--disable-shared' 
'--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' 
'--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 
'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'