2

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.

  • How many tables, and what is the total size of the DB? [Not related question, but why do you want to convert to MyISAM] – Jehad Keriaki Jul 14 '14 at 19:25
  • @JehadKeriaki About 30 tables to convert, total DB size in a 3GB - 4GB when I dump it out to export files. It's not an awful manual process, I'd just like to know if there's a tool. – Alan Storm Jul 14 '14 at 19:30
  • @JehadKeriaki Also, re: the why — someone converted an entire Magento database to MyISAM years before my involvement. I needed to selectively change some tables to InnoDB to get Magento's upgrade scripts to run, and now I want to change them back because I'm worried there's new, custom logic in the system that relies on there being no foreign key checks. – Alan Storm Jul 14 '14 at 19:32
4

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.

1

Alan Storm wrote:

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

Indeed and may I just say that you have my deepest sympathies.

This is a guess, so YMMV... but have you looked at your startup parameters for your mysqld daemon? In particular, many of the latest distros automatically put

mysql sql_mode=no_engine_substitution strict_trans_tables

into my.cnf. It's possible that removing it may relax some of the engine's requirements for RI &c. You can read about this further here.

1

If you want all tables to be MyISAM, here is a vicious way to make MySQL "suck it up and do the work"

STEP #1 : mysqldump everything

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--routine --triggers --all-databases"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql

STEP #2 : Restart MySQL with InnoDB Disabled

service mysql stop
service mysql start --skip-innodb

Afterwards, add this to your my.cnf

[mysqld]
skip-innodb

STEP #3 : Reload the mysqldump

mysql ${MYSQL_CONN} < MySQLData.sql

Everything should be MyISAM.

GIVE IT A TRY !!!

UPDATE 2014-07-14 15:40 EDT

Since you are using a shared service, I take it you want to work with your database. Suppose the database is mydb. Try the following:

STEP #1 : mysqldump everything; convert the phrase ENGINE=InnoDB to ENGINE=MyISAM

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTS="--routine --triggers --databases mydb"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTS}|sed 's/ENGINE=InnoDB/ENGINE=MyISAM'>MySQLData.sql

The script MySQLData.sql should be all MyISAM table creates.

STEP #2 : Reload the mysqldump

mysql ${MYSQL_CONN} < MySQLData.sql

GIVE IT A TRY !!!

  • +1 for useful information, thanks — but the server's a shared resource and other users need the InnoDB features. – Alan Storm Jul 14 '14 at 19:34
  • I updated my answer to change your database only – RolandoMySQLDBA Jul 14 '14 at 19:41
0

Not an expert, but what about this idea? https://rtcamp.com/tutorials/mysql/innodb-to-myisam/ Basically the data+SQLstructure is causing the problem. If you separate the data and the SQL maybe you can get around this issue. You can probably figure out how to filter out the tables you want if you don't want this done to the whole database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.