0

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?

  • Why move to MyISAM? I ask because there are numerous benefits in InnoDB, and few in MyISAM. If you are reaching for a benefit, let's discuss that; perhaps that is a workaround. – Rick James Feb 10 '15 at 4:52
  • Because we don't need transactional support. It's a huge database for mostly read, and MyISAM gives better performance in such cases. – kriegu Feb 10 '15 at 16:07
0

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
  • The first approach didn't work for me, displays the same error message, i also tried ALTER TABLE DISABLE KEYS but didn't work. So i'll try the second one. It seems will work fine but it will take so much time. I'll be giving feedback as soon as it finishes. – kriegu Feb 10 '15 at 0:49
  • I'm currently using MySQL 5.6.22. So if i try to use --skip-innodb when starting the server, throws an error. Here´s the reference to the mysql docs: Note As of MySQL 5.6.21, the --skip-innodb option still works but it is deprecated and will return a warning when used. It will be removed in a future MySQL release. This also applies to its synonyms (--innodb=OFF, --disable-innodb, and so forth). – kriegu Feb 12 '15 at 23:13

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.