Mysql – What’s the safest way to convert table with InnoDB to MyISAM

innodbmyisamMySQL

My database is currently using the InnoDB engine. Now I want to add the fulltext search feature, which is why I want to convert my tables to MyISAM. But doing so breaks all foreign keys. How can I change my table engines to MyISAM safely?

How can I use SELECT…JOIN after I change my tables to the MyISAM engine?

ALTER TABLE jobs ENGINE = MyISAM;
Cannot delete or update a parent row: a foreign key constraint fails

Best Answer

I'd recommend you to do a dump of the db, change all the text from that file from InnoDB to MyISAM, then load the modified file

Related Solutions

Mysql – Setting up foreign keys in phpMyAdmin

If you want to use phpMyAdmin to set up relations, you have to do 2 things. First of all, you have to define an index on the foreign key column in the referring table (so foo_bar.foo_id, in your case). Then, go to relation view (in the referring table) and select the referred column (so in your case foo.id) and the on update and on delete actions.

I think foreign keys are useful if you have multiple tables linked to one another, in particular, your delete scripts will become very short if you set the referencing options correctly.

EDIT: Make sure both of the tables have the InnoDB engine selected.

Mysql – Bogus foreign key constraint fail

On demand, now as an answer...

When using MySQL Query Browser or phpMyAdmin, it appears that a new connection is opened for each query (bugs.mysql.com/bug.php?id=8280), making it neccessary to write all the drop statements in one query, eg.

SET FOREIGN_KEY_CHECKS=0; 
DROP TABLE my_first_table_to_drop; 
DROP TABLE my_second_table_to_drop; 
SET FOREIGN_KEY_CHECKS=1;

Where the SET FOREIGN_KEY_CHECKS=1 serves as an extra security measure...

Best Answer

Related Solutions

Mysql – Setting up foreign keys in phpMyAdmin

Mysql – Bogus foreign key constraint fail

Related Topic