Converting WordPress database tables from MyIASM to InnoDB

      Comments Off on Converting WordPress database tables from MyIASM to InnoDB

There are numerous web pages discussing the advantages on InnoDB over MyISAM in different scenarios, and naturally there are conflicting views on the subject.  My view however is that all MySQL database tables should now be InnoDB unless there is a compelling reason otherwise.

However WordPress does not specify the database table type when being installed or when a plugin creates a new table. It therefore follows the server default which is often MyISAM.

This simple PHP script checks all the database tables and converts them to InnoDB if they are currently MyIASM. It does not execute any WordPress code; it just reads the wp-config.php file to get the required config values.

Copy and paste the code into a new file named myiasm2innodb.php or whatever you prefer, upload it to the WordPress site root and access it from a browser.

The tables will be converted immediately, with no prompting. The status of each table will be listed.

It attempts to clear the output buffer so the ongoing progress will be displayed but many web servers ignore that.  With a large database it may appear to be stuck doing nothing but be patient. If it times out just reload the page.

A single configuration option in the file allows you to just convert WordPress tables (based on the table name prefix) or convert all tables in the database.

<?php
// CONVERT ALL MYIASM TABLES IN WORDPRESS DATABASE TO INNODB
// Put this file in your WordPress root directory and run it from your browser.
// Delete it when you're done.

// OPTIONS
// true to only update WordPress tables (based on table name prefix), false to update all tables
$wordpressOnly = true;

// Read wp-config file for database settings (without loading the file)
$config = file('wp-config.php');
foreach ($config as $line) {
	if (strpos($line,'DB_HOST')) {
		$dbHost = substr($line,strpos($line,'DB_HOST')+7);
		$dbHost = trim(str_replace(array(' ',"'",'"',',',')',';'),'',$dbHost));
	} elseif (strpos($line,'DB_USER')) {
		$dbUser = substr($line,strpos($line,'DB_USER')+7);
		$dbUser = trim(str_replace(array(' ',"'",'"',',',')',';'),'',$dbUser));
	} elseif (strpos($line,'DB_PASSWORD')) {
		$dbPass = substr($line,strpos($line,'DB_PASSWORD')+11);
		$dbPass = trim(str_replace(array(' ',"'",'"',',',')',';'),'',$dbPass));
	} elseif (strpos($line,'DB_NAME')) {
		$dbName = substr($line,strpos($line,'DB_NAME')+7);
		$dbName = trim(str_replace(array(' ',"'",'"',',',')',';'),'',$dbName));
	} elseif (strpos($line,'table_prefix')) {
		$prefix = substr($line,strpos($line,'table_prefix')+12);
		$prefix = trim(str_replace(array(' ',"'",'"',',','=',';'),'',$prefix));
	}
}

echo "<pre>Checking database tables and converting to InnoDB if necessary...\n\n";

// Connect to database
$db = mysqli_connect($dbHost,$dbUser,$dbPass,$dbName);
if (!$db) {
	die('ERROR: Could not connect to database: '.mysqli_connect_error());
}

// Get info on all tables
$tables = mysqli_query($db,'SHOW TABLE STATUS');

// Loop through tables and convert if necessary
while ($table = mysqli_fetch_array($tables)) {

	$tableName = $table['Name'];
	echo $tableName;

	if (!$wordpressOnly || strpos($tableName,$prefix) === 0) {

		$tableEngine = $table['Engine'];

		if ($tableEngine == 'MyISAM') {
			echo ' - converting to InnoDB';

			// attempt to flush output buffer
			@ob_flush();
			@flush();

			// repair table first in case there are any problems
			$db->query("REPAIR TABLE `$tableName`");

			// do the database conversion
			$db->query("ALTER TABLE `$tableName` ENGINE = InnoDB");
			echo ' - finished';

		} elseif ($tableEngine == 'InnoDB') {
			echo ' - already InnoDB';

		} else {
			echo " - unrecognised engine $tableEngine";
		}

	} else {
		echo ' - not a WordPress table';
	}
	echo "\n";
}

echo "\nCompleted, now delete this file.";

IMPORTANT NOTES:

  • Back up the database before running this script!
  • Delete the file after it has been run to avoid exposing your database table names.

Disclaimer and acknowledgement

This PHP script is offered on an ‘as-is’ basis with no guarantee that it will work correctly etc. Please carry out your own tests before using it, and make a backup before using it on a live site. No liability is accepted for any errors or damage however caused. I would appreciate a link to this website if you find it useful.