MySQL | @seanpinegar

Today I took on the task of converting our SugarCRM MySQL MyISAM tables to InnoDB. Currently we have roughly 200 tables in the SugarCRM DB. Its a time consuming processes to convert each table one at a time. Luckily, using some SQL scripting you can make this much less painful.

Start by running the following query to build a list of SQL statements that will handle converting each table.

SET @DB_NAME = 'db_name'; SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tbl WHERE table_schema = @DB_NAME AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name DESC;

The output generates a query for each table to be converted to InnoDB.

ALTER TABLE accounts ENGINE=InnoDB; ALTER TABLE accounts_audit ENGINE=InnoDB; ALTER TABLE accounts_bugs ENGINE=InnoDB; ALTER TABLE accounts_cases ENGINE=InnoDB; ALTER TABLE accounts_contacts ENGINE=InnoDB; ALTER TABLE accounts_cstm ENGINE=InnoDB; ALTER TABLE accounts_opportunities ENGINE=InnoDB; ALTER TABLE acl_actions ENGINE=InnoDB; ALTER TABLE acl_fields ENGINE=InnoDB; ...

Copy and paste these results and run the queries. After some time (in my case about 20 minutes) all of the tables in the DB will be converted to InnoDB.

@seanpinegar

Tag Archives: MySQL

Convert All MyISAM Tables To InnoDB