Re: Ingo Str..g > Best Solution

[JLynch]

I had to cook this up today because my CMS installed InnoDB but my system only supports MyISAM. Also, my MySQL clients either don't allow changing or on a one-by-one basis (could take half an hour!).

Hope this helps someone. Requires PHP by the way!

<?php
#This script will change all the table engine types for a given database!
#All the DB tools I have (GNU/freeware) will not change a list of database
# types, so this script saves time when a CMS or other populates a database
# with tables we cannot use! This can be migrated to InnoDB by changing line
# 23, col 46 from MyISAM to InnoDB (double check the capitals there!).
# Change these variables relative: serverName, userName, password, databaseName

# 20051410 JLynch
# myisamFixer.php

ini_set('display_errors', 'On');
error_reporting(E_ALL);

$link = mysql_connect("serverName","userName","password")
or die("unable to connect to msql server: " . msql_error());

mysql_select_db("databaseName", $link)
or die("unable to select database 'db': " . msql_error());

$result = mysql_query("show tables");
if (!$result) {
die('query failed: ');
}

while ($row = mysql_fetch_array($result)){
mysql_query("ALTER TABLE ".$row[0]." ENGINE=MyISAM; ");
#Command Reference: ALTER TABLE tableName ENGINE=MyISAM
}

?>


Audit: I ran into this problem using eZpublish 3.7[1] and recieved this error,

"Fatal error: eZ publish did not finish its request

The execution of eZ publish was abruptly ended, the debug output is present below."

This error was also recieved by my PHP scripts before fixing the problem,

"Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in"