MySQL Bugs: #7584: Constraints are preserved even if table engine is changed

Bug #7584	Constraints are preserved even if table engine is changed
Submitted:	29 Dec 2004 22:55	Modified:	30 Dec 2004 0:24
Reporter:	Luca Lucchetta	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1.8	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Hi
I don't know if this is a bug, but I know it is giving me many problems.

I've noticed that constraints are preserved even if table engine is changed.

Times ago I've built some InnoDB tables and some constrains betweem them.
Then I've changed the engine of those tables to InnoDB forgetting to remove the constrains.
After that I've divided the database into many databases, moving some of those tables into them.

Now I've decided to change again some table engines into InnoDB.

Here it is the problem.
There are two tables that don't accept this change, returning the following error:
#1025 - Error on rename of './contatti/#sql-247_389' to './contatti/relazioni' (errno: 150)
If I use another name for the table (instead of "relazioni") all works perfectly.
If I use the same name for the table but I use another name for the database (instead of "contatti") all works perfectly.

I try to remove any old constraints but nothing.
I try to dump database and to restore it but nothing:
#1005 - Can't create table './contatti/relazioni.frm' (errno: 150)
(restoring on another server all works properly)

I think that there are some other tables that have some constraints with those problematic tables, and that stop me changing the tables engine.

How to repeat:
- create some InnoDB tables
- create some constraints between them
- transform those tables to MyIsam tables
- retransform them to InnoDB tables

you fill find that constraints still exist

Suggested fix:
I don't know how to fix my problem but I think that MySQL should:
- destroy any constraints of a certain InnoDB table when its engine is changed to one that doesn't support constraints.
- don't let change table engine if some tables have constraints with that table.

Luca,

this is a duplicate of bug #5574.

We should block ALTER TABLE ... TYPE=MyISAM if the table takes part in FOREIGN KEY constraints.

Regards,

Heikki

Heikki,
is there a way I can follow to fix my problem, so I can create such InnoDB tables ?
Thank you
Luca

To repair my databases I followed the following steps:
1- dump all databases in a text file (about 4 gb of data)
2- uninstall MySQL, deleting also the data directory
3- reinstall MySQL 
4- import data from the text file built at step 1

Now all seems to work.

In my opinion you should fix this bug asap: it can corrupt data.

Regards,
Luca