Drupal 6 database has mixed InnoDB & MyISAM. Need to fix before migration

Question

I have a Drupal 6 database with tables which are a mixture of InnoDB and MyISAM. I'm using the Acquia Dev Desktop stack. The core module and other tables created at installation are MyISAM, and the tables for modules I've since installed are InnoDB. I didn't think at this issue when I started this project, and need to move my Drupal site to a new dev. environment I created on WebEnabled. So I have a couple of questions:

1. Since my Acquia MySQL default settings are set to InnoDB, can I safely change this to MyISAM without causing problems within my site?
2. I tried using the DB Tuner module to change all my tables to MyISAM. It worked but then I checked the status report and it said that all those tables that were converted were now "mis-matches". I restored from a backup at that point. Am I going to have to uninstall all the contributed modules and re-install them as MyISAM to avoid getting mis-matched errors.

Thanks a lot.

Jason

Answer 1

You could use the backup & migrate module to backup your DB. Then take this text file (UTF-8) and change all MyISAM strings to InnoDB. Save it and restore this backup. Done.

Answer 2

You may want mysql to write the script for you

Let's start with the base query

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') SQLCommand
FROM information_schema.tables WHERE engine = 'InnoDB'; 

Let's run it at the command line and save it to a SQL text file and display it like this:

mysql -uroot -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') SQLCommand FROM information_schema.tables WHERE engine = 'InnoDB'" > /root/MassConvertInnoDBToMyISAM.sql
cat /root/MassConvertInnoDBToMyISAM.sql

Next, login to mysql. From the mysql prompt, run the script:

mysql> source /root/MassConvertInnoDBToMyISAM.sql

Give it a Try !!!