Knowledgebase

You’ve designed a database with MyISAM and suddenly realized that you need ACID (atomicity, consistency, isolation, durability) compliant features and transactions. Right, you should use InnoDB. But how to change storage engine for your previous tables ? As far as i know, changing the storage engine for tables is easy by alter command. But there’s one flaw. You should do it one by one. On the other hand, you could either write complex SQL queries, or you could write a php code for bulk changes. Today i’m gonna show you how to change your storage engine with a tiny code. Please notice that if you decide to use my code it is your responsibility.

    $db = ‘your_database_name_here’;

    $dsn = “mysql:host=localhost;dbname=$db”;

    $username = “your_MySQL_username”;

    $password = “your_password”;

    $current = ‘MyISAM’; // Current storage engine

    $target = ‘INNODB’; // Target storage engine

     

    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 = ‘$current’”);

    foreach($result as $row) {

    $success = $pdo->exec(“ALTER TABLE {$row['TABLE_NAME']} ENGINE = $target”);

    if($success) {

    echo “{$row['TABLE_NAME']} – success\n”;

    } else {

    $info = $pdo->errorInfo();

    echo “{$row['TABLE_NAME']} – failed: $info[2]\n”;

    }

    }

That’s all. If you see “failed” messages, there’s probably a permission issue with information_schema table. In that case you should enter MySQL command line, and repeat the procedure there. To list storage engines for tables, you should execute;

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘your_database_name’;

This will show you which tables are using which storage engine. After that you should execute;

USE your_database_name;

When you see “Database changed” message, you should execute;

ALTER TABLE your_table_name ENGINE = INNODB;

Have a nice life with your magnificent database storage engine InnoDB !

Please check out other articles about InnoDB and Transactions.