MySQL Bugs: #12555: ALTER TABLE ENGINE fails on foreign key constraint w/ foreign_key

Bug #12555	ALTER TABLE ENGINE fails on foreign key constraint w/ foreign_key_checks=0
Submitted:	12 Aug 2005 17:50	Modified:	15 Aug 2005 15:42
Reporter:	Jay Lundy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1 5.0	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I am trying to convert a database of InnoDB tables to MyISAM with the syntax "ALTER TABLE <table> ENGINE=MyISAM". There are a large number of foreign key constraints between the tables, and the ALTER TABLE produces a foreign key constraint error (ERROR 1217), even after executing "SET FOREIGN_KEY_CHECKS=0".

Further testing shows it doesn't matter what is in the tables. As long as the table is a part of any foreign key relationship it does not allow you to change the table type.

How to repeat:
CREATE TABLE parent (
    id INT KEY
) ENGINE=InnoDB;

CREATE TABLE child (
    id INT,
    CONSTRAINT id FOREIGN KEY (id) REFERENCES parent (id)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE parent ENGINE=MyISAM; -- ERROR 1217
ALTER TABLE child ENGINE=MyISAM; -- ERROR 1217

ALTER TABLE child DROP FOREIGN KEY id;

ALTER TABLE parent ENGINE=MyISAM; -- succeeds
ALTER TABLE child ENGINE=MyISAM; -- succeeds

Suggested fix:
Either:
1. Use the suggestions from http://dev.mysql.com/doc/mysql/en/converting-tables-to-innodb.html (Create an identical MyISAM table and do "INSERT INTO ... SELECT * FROM")
2. Drop all foreign key constraints before changing table type

Sorry, meant to put this in InnoDB category

I think I disagree with this.

Depending on the tables' definitions/structure some data might become worthless if foreign keys are lost.  So I think a FK should be explicitly dropped!

Hi!

Yes, this is intentional. If you convert a table involved in a foreign key constraint to MyISAM, InnoDB's foreign key schema will get inconsistent. For example, the table may be referenced by a foreign key constraint, and that constraint will then be left 'dangling'. MyISAM does not support foreign key constraints.

Workaround: drop foreign key constraints first, after that convert to MyISAM.

Regards,

Heikki

I see where you're coming from, but I don't think the way it is right now is the expected behavior. If I set foreign_key_checks to 0, I expect the database to function as if those constraints never existed. It's my responsibility to make sure the data is left consistent if I want it to be. I don't think I should have to drop the constraint when it should be ignored in the first place. Plus you are free to drop the tables with foreign_key_checks set to 0. It doesn't seem consistent to allow you to drop the table but not to convert its type (which I'm assuming is basically just a drop and recreate).

Thank you for your time

Jay,

the deep reason is that the internal implementation of the foreign key schema tables is such that converting to MyISAM will make them badly inconsistent. There were several bug reports, and we decided to fix it this way.

Dropping a referenced InnoDB table will not cause such inconsistency.

Regards,

Heikki