PHP script to convert MySQL tables to a new storage engine

Posted June 27th, 2011 in MySql and PHP

MySQL supports multiple storage engines which each have their pros and cons; the two most commonly used are MyISAM and INNODB. It's easy to convert a table from one storage engine to another, and this post has a PHP script which convert all tables in a MySQL database from one engine to another.

Storage engine incompatibilities

Note that there are often some incompatibilities between storage engines which prevent a table from being converted from one to another. For example, MyISAM tables can have full text indexing but INNODB tables cannot; attempting to convert a MyISAM table to INNODB when it has a full text index will fail.

Conversion can take time

Converting from one storage engine to another can take a lot of time depending on how large the database tables are; they have to be recreated to change the engine. It is not recommended this be done on busy production websites without first taking the website offline.

The PHP script

Substitute the variables at the top of the script with those appropriate for your database, and set the $from and $to variables to the storage engine you want to change from and to. The example script attempts to convert all MyISAM tables to INNODB.

$db = 'myDatabaseName';
$dsn = "mysql:host=localhost;dbname=$db";
$username = "myUsername";
$password = "myPasssword";

$from = 'MyISAM';
$to = 'INNODB';

try {
    $pdo = new PDO($dsn, $username, $password);
}
catch(PDOException $e) {
    die("Could not connect to the database\n");
}

$result = $pdo->query("
	SELECT TABLE_NAME
	FROM information_schema.TABLES
	WHERE TABLE_SCHEMA = '$db'
	AND ENGINE = '$from'
");

foreach($result as $row) {
	$success = $pdo->exec("ALTER TABLE {$row['TABLE_NAME']} ENGINE = $to");
	if($success) {
		echo "{$row['TABLE_NAME']} - success\n";
	}
	else {
		$info = $pdo->errorInfo();
		echo "{$row['TABLE_NAME']} - failed: $info[2]\n";
	}
}

The script connects to the database server, then queries the information_schema for all the tables in the database that have a storage engine of the type $from.

It then loops through the result set and attempts to convert the table. If there was an error in converting the table this is echoed out.

Example output

Here's some example output from the script, showing a table that was successfully converted from MyISAM to INNODB and one that failed because it contains a full text index:

test1 - success
test1 - failed: The used table type doesn't support FULLTEXT indexes