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 18:58	Modified:	8 Apr 2005 23:00
Reporter:	Paul DuBois
Status:	Closed
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	4.0.21/4.1.5 (at least)	OS:	Any (all)
Assigned to:	Marko Mäkelä	Target Version:

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.