MySQL – Converting to MyISAM with foreign_key_checks=0 Not Working

innodbmyisamMySQL

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 the 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.

Best Answer

Here's a method you can use without dumping all your InnoDB tables.

You will have to drop the foreign key constraints before you can alter the tables. You can generate the drop statements using the INFORMATION_SCHEMA this way:

mysql> SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, 
    '` DROP FOREIGN KEY `', constraint_name, '`;') AS _sql 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
  WHERE referenced_table_schema = 'mydb' -- replace with your database name
    AND referenced_table_name = 'catalog_category_entity' 
    AND ordinal_position = 1;

The output should be one or more a fully-formed DDL statements. Run the statements, and then you can change the storage engine for your parent table.

Related Solutions

MySQL Storage Engine – Which One to Choose?

Both works with b-tree index, but MyISAM can work with FULLTEXT index also.

MySQL 5.6 is supposed to support FULLTEXT indexing as well. Look for it when it goes GA.

InnoDB is more strict in data integrity, while MyISAM is loose

InnoDB has transactions while MyISAM does not.

InnoDB has foreign keys and relationship contraints while MyISAM does not.

This is true in light of the fact that InnoDB supports transaction isolation via MVCC. That, in part, makes InnoDB fully ACID compliant.

MyISAM are faster to read but slower to write (I'm not sure about this one)

This depends entirely on the row format of the MyISAM table (Fixed or Dynamic). Slow is relative. After all, each DML statement against a MyISAM performs a full table lock each and every time.

Given a large enough cache, InnoDB can be faster for both reads and writes because COMMITs can be delayed. This leaves data already cached in the InnoDB Buffer Pool available for reads right out of memory.

InnoDB caches both Data and Indexes while MyISAM only caches Indexes. Thus, disk reads for data from MyISAM is always required.

Mysql – “Cannot add or update a child row” when deleting a record

On the face of it, that does seem impossible.

The thing is, your error suggests it's not that you're trying to delete at all.

The message you're getting suggests you're trying to insert or update a row in the child table, not delete a row from the parent table. If the foreign key you posted was causing the problem relative to a delete, you should see this message, instead:

Cannot delete or update a parent row: a foreign key constraint fails (`test`.`bar`, CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`))

It's also possible you have some BEFORE DELETE trigger magic on survey_main that's doing something unexpected.

Right after this error occurs, try this:

SHOW ENGINE INNODB STATUS;

The LATEST FOREIGN KEY ERROR section should give you something more to go on. Failing that, you could enable the general log, which will show queries executed by triggers and other stored programs, as well as the queries you're directly executing, to shed light on what might be going on behind the scenes.

Update (#1) Things are definitely not as they seem and the full table definitions are going to be pretty critical, here.

Also, the version of MySQL you're using may also be relevant, so please mention it.

With nothing more to go on at the moment, I'm speculating that you have invalid data in the survey_id column of the survey_answers table. To test that theory:

SELECT *
  FROM survey_answers sa
  LEFT JOIN survey_main sm ON sm.id = sa.survey_id
 WHERE sm.id IS NULL;

If I understand your schema correctly, then this query will return zero rows if I am wrong. :) If you get rows returned, then those rows have survey_answers records that contain an survey_id value that doesn't exist in the id column of survey_main.

Best Answer

Related Solutions

MySQL Storage Engine – Which One to Choose?

Mysql – “Cannot add or update a child row” when deleting a record

Related Question