Mass MySQL storage engine updater

engineswap.php

<?php
// Update tables from one storage engine to another
//
// Written by Matt Stith for Nexcess.net
// May 3, 2011
//


// CLI only
if (!defined('STDIN')) {
	die();
}

$argc = intval($_SERVER['argc']);
$argv = $_SERVER['argv'];

$usage = 'Usage: '.$argv[0]." mysqlUser mysqlDatabase [oldEngine] [newEngine]\nmysqlUser - Username to connect with\nmysqlDatabase - Database to alter tables in\noldEngine - If specified, will only alter tables with this type, defaults to all\nnewEngine - Engine to set tables to, defaults to InnoDB for Magento\n\nNote: If only one of [oldEngine] or [newEngine] are specified, the supplied option is assumed to be newEngine\n";

if ($argv[1] == 'h' || $argv[1] == 'help') {
	die($usage);
}

if ($argc < 3 || $argc > 5) {
	die($usage);
}


$mysqlUser = $argv[1];
$mysqlDatabase = $argv[2];

$oldEngine = 'unset';
$newEngine = 'unset';

if ($argc == 3) {
	$oldEngine = 'all';
	$newEngine = 'InnoDB';
} else if ($argc == 4) {
	$oldEngine = 'all';
	$newEngine = $argv[3];
} else if ($argc == 5) {
	$oldEngine = $argv[3];
	$newEngine = $argv[4];
} else {
	die('Arg error: '.$argc.' arguments. 3, 4, or 5 expected.\n');
}

// Trust noone
$oldEngine = mysql_escape_string($oldEngine);
$newEngine = mysql_escape_string($newEngine);

// Password prompt (No output while typing)
function prompt_silent($prompt = 'Enter Password:') {
    $command = "/usr/bin/env bash -c 'echo OK'";
    if (rtrim(shell_exec($command)) !== 'OK') {
      trigger_error("Can't invoke bash for password prompt");
      return;
    }
    $command = "/usr/bin/env bash -c 'read -s -p \""
      . addslashes($prompt)
      . "\" mypassword && echo \$mypassword'";
    $password = rtrim(shell_exec($command));
    echo "\n";
    return $password;
}


$mysqlPass = prompt_silent("MySQL password for $mysqlUser: ");

$connection = mysql_connect('localhost', $mysqlUser, $mysqlPass);
if (!$connection) {
	die(mysql_error()."\n");
}
mysql_select_db($mysqlDatabase);

$query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$mysqlDatabase'";

if ($oldEngine != 'all') $query .= " AND ENGINE LIKE '$oldEngine'";

$tables = mysql_query($query, $connection);

$count = mysql_num_rows($tables);

if ($count == 0) {
	die("No tables found with $oldEngine. Aborting.\n");
}

echo "About to change $count tables to $newEngine. Continue? [Y/n] ";

$handle = fopen('php://stdin','r');
$answer = fgets($handle);
fclose($handle);
echo "\n";

if (stripos($answer,'y') !== 0) {
	die("Aborting\n");
}

echo "Running queries...\n";
while ($table = mysql_fetch_array($tables)) {
	$tableName = mysql_escape_string($table['TABLE_NAME']);
	$result = mysql_query('ALTER TABLE '.$tableName.' ENGINE = '.$newEngine,$connection);
	if (!$result) {
		echo "Engine change failed on $mysqlDatabase.$tableName: ".mysql_error($connection)."\n";
	}
}

mysql_close($connetion);
echo "Done!\n";
?>