I wrote (or rather modified an existing script) that batch converts the moodle database from MyISAM to InnoDB. This saves a lot of time, rather than writing a couple hundred ALTER TABLE statements, or even using the phpMyadmin interface.
Credit for the original script:
http://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-to-innodb
Please test on a copy of the database before using. Do not run on a production database!
I can't emphasize that enough.
It's been tested on both Moodle 1.9 and 2.0/2/2, see script preamble for further details.
It's a potentially hazardous script, so I won't recommend placing it in any web accessible folders (like /var/www/html/..). It's been made relatively simple to implement, and the instructions are below:
0. Login as root/su
1. Ensure that InnoDB is enabled on your server
mysql -u root -p *****
SHOW ENGINES;
Expected output
...
InnoDB | YES | Supports transactions...
2. Install script on your server
Copy/Paste the code at the bottom of the post into a new file named
innodb_convert.php on your Moodle server where the database is located.
Please ensure that it is not in a web accessible folder.
3. Give the file execute permissions to the script
chmod +x innodb_convert.php
4. Configuration
Open the file and navigate to the database configuration section (approx line 48). Set relevant variables.
$host = 'myhost'; //Specify host
$dbuser = 'myuser'; // Specify user with alter permissions
$dbpass = 'mypass'; //user password
$mydb = 'mydb'; //specify schema or db to be modified
5. Save and exit.
6. From the command line, call the script using php
cd /tmp
php innodb_convert.php
7. Normal output expected:
ALTER TABLE mytable1 engine=InnodDB;
ALTER TABLE mytable2 engine=InnodDB;
ALTER TABLE mytablen engine=InnodDB;
Operation Completed
Confirm Storage Engine conversion using phpyadmin
or from Mysql: show create tablename.
Script Execution time: 999.99 seconds
The script (see preamble for additional information):
Download text file here.
<?php
// WARNING: PLEASE TEST BEFORE RUNNING ON A PRODUCTION DATABASE
// Tested on Moodle 1.9 and Moodle 2.0/2.2
//
// Disclaimer:
//
// This script was written to be executed from the command line in
// Linux using a MYSQL database
// and MYISAM storage engine. It converts the storage engine to
// INNODB using the ALTER TABLE command.
//
// It assumes that the INNODB engine has been enabled.
// This can be confirmed by logging into the mysql prompt
// and running the command:
// show engines;
//
// If it is disabled, check your my.cnf and
// comment out the line: skip-innodb
//
// It does not check if disabled, so the script would execute
//and attempt to co
// It contains secure information, so DO NOT PLACE IN WEB DIRECTORY
//
// Execute from /temp and delete afterwards
//
// This is a modification of a script located here:
// http://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-to-innodb
//
// Version : 1.2
// Date : 11 April 2012
// Diff : check for recordset before executing alter table
//
// Version : 1.1
// Date : 31 January 2012
// Diff : Added Timer to script; Display user and DB being affected
//
//
// Version : 1.0
// Date : 25 January 2012
//
//
//
// Noveck Gowandan http://noveckg.blogspot.com
//Time Calculation - start count
$time = explode(' ',microtime());
$time = $time[1] + $time[0]; //return array
$begintime = $time; //define begin time
//Your database connection items here
$host = 'myhost'; //Specify host
$dbuser = 'myuser'; // Specify user with alter permissions
$dbpass = 'mypass'; //user password
$mydb = 'mydb'; //specify schema or db to be modified
//connect to database using variables above
$link = mysql_connect($host,$dbuser,$dbpass);
$db = mysql_select_db($mydb);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo "Connected Successfully to: $host." . "\n\n";
echo "Using database: $mydb." . "\n\n";
echo "Running script as $dbuser." . "\n\n";
//show tables in database
$sql = 'SHOW TABLES';
$rs = mysql_query($sql);
echo $sql;
echo "\n";
if (!$rs) {
die('SQL Recordset Error: ' . mysql_error());
}
else {
//loop through tables and convert to InnoDB
while($row = mysql_fetch_array($rs))
{
$tbl = $row[0];
$sql = "ALTER TABLE $tbl engine=InnoDB;";
mysql_query($sql);
echo $sql;
echo "\n";
}
echo 'Operation Completed.' . "\n\n";
echo 'Confirm Storage Engine conversion using phpmyadmin ' . "\n" . 'or from mysql: show create table tblname.' . "\n";
}
//close connection
mysql_close($link);
$time = explode(" ", microtime());
$time = $time[1] + $time[0];
$endtime = $time; //define end time
$totaltime = ($endtime - $begintime);
echo "Script Execution Time: $totaltime" . " seconds." . "\n\n";
?>
