Thursday, April 12, 2012

Moodle: PHP script to convert Mysql database from MYISAM to InnoDB

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:
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 *****
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.

// 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:
// 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

//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;";

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

$time = explode(" ", microtime());
$time = $time[1] + $time[0]; 
$endtime = $time; //define end time
$totaltime = ($endtime - $begintime);
echo "Script Execution Time: $totaltime" . " seconds." . "\n\n";



  1. Thanks for sharing such a nice information. I hope it will help many more. Teachers can schedule online classes, give high impact presentations and watch class recordings with Moodle! Know more about Moodle -

  2. better use this sql


  3. Obrigada! Funcionou muito bem, travou um pouco na tabela license, mas depois de uns minutos prosseguiu... Thanks very much!!!

  4. Frequent auditing of the safety measures and updating the tools can also be of immense help in protecting the database of a firm.create mysql dashboard

  5. Really cool post, highly informative and professionally written and I am glad to be a visitor of this perfect blog, thank you for this rare info!
    Php course in chennai

  6. You have the alternative to experience a specialist who knows the business and, for a charge, will speak to you to the makers. You could attempt chilly calling - giving over or messaging the content to makers yourself. coverage service

  7. Hi Thanks for the nice information its very useful to read your blog.
    Advanced SEO Training

  8. While writing PHP code, programmers have option to use either single quotes (') or double quotes ("). But the developers can easily enhance the performance of the PHP application by using single quotes instead of double quotes. The single code will increase the execution speed of loops drastically.plakatų spausdinimas

  9. If you think that running a website ends by simple creating the website and regularly updating it, then you are forgetting one important factor which is Random Password Generator Tool. You need to understand that the security of your blogs and websites is your top priority.

  10. Memang agar tidak mengundang kecurigaan yang lebih, disarankan untuk bermain pindah pindah meja. Hal ini dikarenakan dengan menggunakan Akun Pro maka kesempatan anda untuk menang semakin besar
    bandar ceme terbaik
    paito warna terlengkap
    syair sgp