Convert MySQL MyISam Tables to Innodb

From Bonus Bits
Jump to: navigation, search

Purpose

This article gives the steps to convert tables using MyISam Engine to Innodb.

Important Note about a Mediawiki Database (1.28 or Below)

Warning.png DO NOT convert searchindex table to InnoDB!

As of Mediawiki version 1.28 this is the only table that needs to remain using the MyISam engine. The maintenance scripts do full text indexing which is not supported by Innodb prior to MySQL 5.6. When Mediawiki pushes the minimum supported version of MySQL to 5.6+ I'm sure they will change the scripts to work properly.

If you do convert to InnoDB and run the php ../maintenance/rebuildall.php you will get an error like the following:

** Rebuilding fulltext search index (if you abort this will break searching; run this script again to fix):
Clearing searchindex table...Done
Rebuilding index fields for 890 pages...
500

Rebuild the index...
[b1c80f566ffab6f0563deb8a] [no req]   DBQueryError from line 1054 of /var/www/html/mediawiki/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: ALTER TABLE `searchindex` ADD FULLTEXT si_title (si_title), ADD FULLTEXT si_text (si_text)
Function: RebuildTextIndex::createMysqlTextIndex
Error: 1795 InnoDB presently supports one FULLTEXT index creation at a time (shareddb.bonusbits-prd-us-west-2.com)

Backtrace:
#0 /var/www/html/mediawiki/includes/libs/rdbms/database/Database.php(912): Database->reportQueryError(string, integer, string, string, boolean)
#1 /var/www/html/mediawiki/maintenance/rebuildtextindex.php(148): Database->query(string, string)
#2 /var/www/html/mediawiki/maintenance/rebuildtextindex.php(74): RebuildTextIndex->createMysqlTextIndex()
#3 /var/www/html/mediawiki/maintenance/rebuildall.php(48): RebuildTextIndex->execute()
#4 /var/www/html/mediawiki/maintenance/doMaintenance.php(111): RebuildAll->execute()
#5 /var/www/html/mediawiki/maintenance/rebuildall.php(67): require_once(string)
#6 {main}

https://www.mediawiki.org/wiki/Manual_talk:Searchindex_table

Switch searchindex Table Back to MyISam

  1. Login to MySQL Command Line Utility or use Query Utility
    ALTER TABLE mediawiki.searchindex engine=MyISam;
    
  2. Try php ../maintenance/rebuildall.php again.


View All Tables in all Databases That are MyISam (Option 1)

List all database tables that are MyISAM and need to be converted to InnoDB. Output the commands to run to convert.

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

Example Output

This output is all the tables that ARE MyISam. The output is providing the SQL command to run to switch each table to InnoDB.

+----------------------------------------------------------------------+
| CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;') |
+----------------------------------------------------------------------+
| ALTER TABLE mediawiki.searchindex engine=InnoDB;                     |
| ALTER TABLE wordpress.wp_loginizer_logs engine=InnoDB;               |
| ALTER TABLE mysql.columns_priv engine=InnoDB;                        |
| ALTER TABLE mysql.db engine=InnoDB;                                  |
| ALTER TABLE mysql.event engine=InnoDB;                               |
| ALTER TABLE mysql.func engine=InnoDB;                                |
| ALTER TABLE mysql.host engine=InnoDB;                                |
| ALTER TABLE mysql.ndb_binlog_index engine=InnoDB;                    |
| ALTER TABLE mysql.proc engine=InnoDB;                                |
| ALTER TABLE mysql.procs_priv engine=InnoDB;                          |
| ALTER TABLE mysql.proxies_priv engine=InnoDB;                        |
| ALTER TABLE mysql.tables_priv engine=InnoDB;                         |
| ALTER TABLE mysql.user engine=InnoDB;                                |
+----------------------------------------------------------------------+


View Tables in a Specific Database that are MyISam (Option 2)

This is a way to only look at a specific databases tables for MyISam engine instead of all the databases on the system like above.

SET @DATABASE_NAME = 'wordpress';

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

Example Output

This output is all the tables in the wordpress database that ARE MyISam. The output is providing the SQL command to run to switch each table to InnoDB.

+----------------------------------------------------------------------+
| sql_statements                                                       |
+----------------------------------------------------------------------+
| ALTER TABLE wordpress.wp_loginizer_logs engine=InnoDB;               |
+----------------------------------------------------------------------+


Convert

Copy / Paste the Output commands in short lists/chunks without the graphics. I found you have to break up how many you paste in. It bombs out if you feed. IF you are doing this on a MySQL RDS Instance you'll skip the mysql database because we don't have permissions to it.

Example Commands

ALTER TABLE wordpress.wp_loginizer_logs engine=InnoDB;