MySQL Bugs: #5574: ALTER TABLE of InnoDB table causes data dictionary problem

Bug #5574	ALTER TABLE of InnoDB table causes data dictionary problem
Submitted:	14 Sep 2004 16:58	Modified:	8 Apr 2005 21:00
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.21/4.1.5 (at least)	OS:	Any (all)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
I am seeing an InnoDB problem under these conditions:

- Create a table that contains foreign key constraints.

- Use ALTER TABLE to change the table to a MyISAM table.
(Why am I doing this? you ask.  I was trying to see if MySQL
would allow this operation or deny it.)  This succeeds,
although of course the foreign key information is lost from
the table definition.

- Drop the table.

- Try to recreate the table using its original definition as
an InnoDB table.  This fails with an error that the constraint
name already exists:

ERROR 1005 at line 19: Can't create table './test/child.frm' (errno: 121)

SHOW INNODB STATUS indicates this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
040914 11:13:16 Error in foreign key constraint creation for table `test/child`.
A foreign key constraint of name `test/child_ibfk_1`
already exists.  (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).

This occurs in 4.0.21 and 4.1.5.  (Haven't tried 5.0.x)

A script that demonstrates the problem is included below.  I find that I can make
the error go away in two ways:

- Drop and recreate the InnoDB tablespace.  (The drastic solution.)
- Add "CONSTRAINT some_name" to the front of the foreign key definition.
This doesn't really "solve" the problem, though, because the constraint
is still recorded in the data dictionary.

How to repeat:
DROP TABLE IF EXISTS parent;
CREATE TABLE parent
(
    parent_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (parent_id)
) ENGINE = InnoDB;

DROP TABLE IF EXISTS child;
CREATE TABLE child
(
    parent_id   INT UNSIGNED NOT NULL,
    PRIMARY KEY (parent_id),
    FOREIGN KEY (parent_id) REFERENCES parent (parent_id)
) ENGINE = InnoDB;

ALTER TABLE child ENGINE = MyISAM;

DROP TABLE IF EXISTS child;
CREATE TABLE child
(
    parent_id   INT UNSIGNED NOT NULL,
    PRIMARY KEY (parent_id),
    FOREIGN KEY (parent_id) REFERENCES parent (parent_id)
) ENGINE = InnoDB;

Verified on the latest Bk 4.0/4.1/5.0 trees.

Verified also on Windows XP & FreeBSD, 4.1.11  -- Timothy

Marko,

please prevent ALTER to MyISAM if the table is involved in FOREIGN KEY constraints. The ALTER confuses InnoDB's internal data dictionary.

Regards,

Heikki

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23747

I've committed a fix that prevents ALTER TABLE from changing the storage engine from InnoDB if there are any foreign key constraints attached to the table.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The bug will be fixed in 4.1.12 and 5.0.5 or 5.0.4.

Hmm ... it seems that there is a change between 4.1.8-nt and 4.1.12-nt. It seems that a foreign key name must be (at least) unique across all tables of a single database. This restriction didn't exist in 4.1.8.

Regards,
Mark

Mark,
To my knowledge, InnoDB has always enforced <database_name>+<key_name> to be unique. If you can prove this false, please submit the test case. Please note that MySQL silently ignores foreign keys and TYPE=InnoDB or ENGINE=InnoDB if the InnoDB storage engine is not available. It's best to check the SHOW CREATE TABLE output.