Mysql – Change Storage Engine on Referenced Tables

innodbmyisamMySQLstorage-engine

i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases.
So i made a script file to do so:

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
for D in $DATABASES
do
   TABLES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select table_name from information_schema.tables where table_schema="'$D'" and engine="InnoDB"')
   for T in $TABLES
   do
      echo "ALTERING TABLE $T"
      mysql -u XXXXX -pXXXXX -e "USE $D; ALTER TABLE $T ENGINE=MYISAM"
   done
done

It works on some tables, but on those with foreign keys, displays the following error message:

ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails

Is there a way to change storage engine on those tables?

Best Answer

You need to disable foreign key checks and unique checks

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
for D in $DATABASES
do
   TABLES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select table_name from information_schema.tables where table_schema="'$D'" and engine="InnoDB"')
   for T in $TABLES
   do
      echo "ALTERING TABLE $T"
      SQL="SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0;"
      SQL="${SQL} ALTER TABLE ${T} ENGINE=MYISAM"
      mysql -u XXXXX -pXXXXX -D${D} -e "${SQL}"
   done
done

Here is a more concise way: Reload data with InnoDB Disabled

#!/bin/bash
DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")')
mysqldump -u XXXXX -pXXXXX --single-transaction --add-drop-database -B ${DATABASES} > AllMyData.sql
mysql -u XXXXX -pXXXXX -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql restart --skip-innodb
mysql -u XXXXX -pXXXXX < AllMyData.sql
Related Question