Bug #16223 FOREIGN_KEY_CHECKS=1 doesn't allow converting to MyISAM, whil allows dropping

• Submitted: 5 Jan 2006 13:23
• Modified: 5 Jan 2006 15:28
• Reporter: Gleb Paharenko
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.18
• OS: Linux (Linux)
• CPU Architecture: Any

[5 Jan 2006 13:23] Gleb Paharenko

Description:
with FOREIGN_KEY_CHECKS I was unable to convert the table to MyISAM, while it is possible to drop the table, and then create it again, seems weird for. If I'm able to do the convertion in two steps (drop and create) why I can't use ALTER for this task? See: how to repeat

How to repeat:
create table par1(a int, key (a)) engine = innodb;
create table chil1(b int, foreign key (b) references par1(`a`))
engine=innodb;

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table par1 engine=myisam;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop table par1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table par1(a int, key (a)) engine = myisam;
Query OK, 0 rows affected (0.01 sec)

Wyala - the table is MyISAM

Suggested fix:
Fix that with FOREIGN_KEY_CHECKS alter engine=myisam can work. The bug in my opinion in that we're able to drop the table, but can't alter it. Seems weird for me

[5 Jan 2006 15:28] Heikki Tuuri

Gleb,

this is intentional. Converting to MyISAM could leave foreign key constraints dangling, and make the InnoDB internal data dictionary inconsistent.

Regards,

Heikki