1

I'm working on some scripts to programmatically automate a database migration, and at the end of a long series of compromises working in a system I didn't design, own, or maintain, I need to convert a number of MySQL InnoDB tables to MyISAM tables. However, when I attempt to alter a table

mysql> ALTER TABLE catalog_category_entity ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>

MySQL complains. This is expected. However, if I disable the foreign_key_checks, I get the same results

mysql> SET foreign_key_checks = 0;
mysql> ALTER TABLE catalog_category_entity ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

This happens regardless of my setting the key check at the global or session level. I assume the problem here is the table in questions has other InnoDB table which reference it, and MySQL refuses to end up in a state where an InnoDB table references a table that's not InnoDB. (I may be incorrect on this, and I'm more than happy to be corrected)

Is there a quick way to handle this situation? I basically want all tables in the database to be MyISAM, and tracing out all the relationships myself and/or manually removing the contraints seems very time consuming, and the sort of thing that a computer would be better at.

If the answer is "suck it up and do the work" I'm happy to hear that from an expert — I just don't want to waste the time if I don't need to.

If it matters the language I'm programming in in PHP, but I'm happy for solutions that require other languages.

Alana Storm
  • 164,128
  • 91
  • 395
  • 599
  • possible duplicate of [Mysql: Programmatically remove all foreign keys](http://stackoverflow.com/questions/1252868/mysql-programmatically-remove-all-foreign-keys) – RandomSeed Jul 14 '14 at 20:56

1 Answers1

1

MyISAM doesn't support foreign keys. Delete the keys before changing the engine:

> ALTER TABLE catalog_category_entity DROP FOREIGN KEY fk_name;
> ALTER TABLE catalog_category_entity ENGINE=MyISAM;

You can find the foreign key name by calling:

> SHOW CREATE TABLE catalog_category_entity;

This may be helpful: Delete all foreign keys in database(MySql)

Community
  • 1
  • 1
user4035
  • 22,508
  • 11
  • 59
  • 94
  • 1
    +1 for good information — but there's a large number of tables involved here — I was hoping for a command that does all that for me. – Alana Storm Jul 14 '14 at 19:22
  • @AlanStorm Ahh not sure, whether such command exists. Why do you want to convert to MyISAM? You will lose some protection from anomalies, that is guaranteed by the foreign keys. – user4035 Jul 14 '14 at 19:24
  • Agreed, but see "end of a long series of compromises working in a system I didn't design, own, or maintain" – Alana Storm Jul 14 '14 at 19:26
  • @AlanStorm Here it was discussed: http://stackoverflow.com/questions/14546477/delete-all-foreign-keys-in-databasemysql - you can use system tables – user4035 Jul 14 '14 at 19:30