1

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

2 Answers 2

2

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.

1
  • Thanks, BetaRide. I'll try that approach and see if the Schema module still identifies the tables I change as mis-matches.
    – jkneip
    Feb 15, 2012 at 20:23
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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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