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?