Testing and QA

How do you convert the database from MyISAM to InnoDB engin?

 
Picture of Clarence Prudhoe
How do you convert the database from MyISAM to InnoDB engin?
 

There is a message in Moodle Notifications that says:

"Database tables are using MyISAM database engine, it is recommended to use ACID compliant engine with full transaction support such as InnoDB."

I found detailed instructions at this site http://www.linux.com/archive/feed/46370 ,but it assumes you have SHEL access, which I don't have.

How do you convert the database from MyISAM to InnoDB engin using phpMYAdmin.  I have search for MyISAM in phpMyAdmin and I see many instances in the database.  Is there a way to do a search and replace using phpMyAdmin?

Thanks,

Clarence

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: How do you convert the database from MyISAM to InnoDB engin?
Core developersDocumentation writersParticularly helpful MoodlersPlugin developers

There is a script in Moodle /admin/innodb.php, I think, that will do it for you. Use with caution.

 
Average of ratings: Useful (1)
Picture of Clarence Prudhoe
Re: How do you convert the database from MyISAM to InnoDB engin?
 

Thank You Tim,

I made 2 backup copies of my database then I ran the script in /admin/innodb.php.  It seems to have worked perfectly.  I search my database in phpMyAdmin and only found one instance of MyISAM and that was in the Moodle notification message.

You just saved me a ton of work.  I thought I would have to edit the entries one at a time.  

And Emanuel, thanks for your input.   As you said this would been a tedius task.   I'm glad the Moodle developers included this script.

Thanks,

Clarence

 
Average of ratings: -
Picture of Satish Babu
Re: How do you convert the database from MyISAM to InnoDB engin?
 

Thanks for the tip.  It worked very well for me.

 
Average of ratings: -
A Picture of Me
Re: How do you convert the database from MyISAM to InnoDB engin?
 

I'm on Moodle 2.1 and I ended up with this error: error/moodle/ddlexecuteerror

http://docs.moodle.org/en/error/moodle/ddlexecuteerror

bit of a shame, I was hoping for an easy option.

 
Average of ratings: -
Colin Dixon BSc DipEd
Re: How do you convert the database from MyISAM to InnoDB engin?
 

I needed to add to /admin/innodb.php

define('CLI_SCRIPT', true);

to get it to execute, then I got 

!!! Unsupported redirect detected, script execution terminated !!!

still getting 

 

Database tables are using MyISAM database engine, it is recommended to use ACID compliant engine with full transaction support such as InnoDB.
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: How do you convert the database from MyISAM to InnoDB engin?
Core developersDocumentation writersParticularly helpful MoodlersPlugin developers

Which version of Moodle? I am guessing not 2.2 or 2.3, since the innodb scrip was moved from admin/innodb.php to admin/tool/innodb/.

That script is not designed to work from the command-line. You are supposed to go there in your web browser.

In recent versions of Moodle, the tool admin/cli/mysql_engine.php exists if you want a command-line tool. Again, since you don't say which Moodle version you are using, I can't tell if that option is available to you.

 
Average of ratings: -
Colin Dixon BSc DipEd
Re: How do you convert the database from MyISAM to InnoDB engin?
 

Yes I worked it our it ran fine through the web browser, SSH was not the way to go.

I used admin\innoDB.php, not admin\tools\innoDB\inxed.php

and made the

$DB->change_database_structure("ALTER TABLE $fulltable ENGINE=INNODB DEFAULT CHARSET=utf8");

change, I had to reset the password for admin, looks like all passwords were lost, but thats not a big issue for me, still need to get email working again.

Thanks for your help. Colin

 
Average of ratings: -
Picture of Andrew Steele
Re: How do you convert the database from MyISAM to InnoDB engin?
 

I've just run into the database conversion challenge.  Made more frustrating by the fact that my upgraded Moodle install (2.2.1+) doesn't have innodb.php in the admin directory.

Is this an indication that that process of converting the database was ineffective?

Otherwise, any suggestions of where the script has disappeared too?

Andrew

 
Average of ratings: -
Picture of Emanuel Delgado
Re: How do you convert the database from MyISAM to InnoDB engin?
TestersTranslators

You can use PHPMyAdmin entering Structure view of a table and clicking Operations tab. Look for Storage Engine parameter. This is a tedious task due to the number of tables.

You may want to try this solution to automate the process. It looks promissing but I didn't test it, use it carefully!

 
Average of ratings: -
Picture of Miguel Guhlin
Re: How do you convert the database from MyISAM to InnoDB engin?
 

Clarence:
Howdy! I've successfully converted several of our Organization's Moodle databases to INNODB from MyISAM following the process here:

moodlemayhem.org/tcea2011-moodle-server-setup-basics/moodleacidtrip

It's an adaptation from the information you shared in the link in your original post (and referenced in the steps summarized at my link above).

All I'm doing is exporting a database via phpMyAdmin, making edits, dropping the MyISAM version of the dbase, then importing the INNODB version.

I haven't tried the script yet, but will do so.

Thanks,

Miguel Guhlin
http://mguhlin.org

Moodle Mayhem - http://moodlemayhem.org

 
Average of ratings: Useful (1)
C'est moi :-)
Re: How do you convert the database from MyISAM to InnoDB engin?
Documentation writersParticularly helpful MoodlersTestersTranslators

Thanks Michel for sharing it, and adding in the dedicated page in the documentation smile

I hope more people will add to it, with comments based on experience wink

 
Average of ratings: -
Picture of Aryan Nava
Convert database from MyISAM to InnoDB engin in Moodle
 

If your using myPHPAdmin, you can convert MyISAM to InnoDB using the phpMyAdmin interface

For more information please see:
Convert MyISAM to InnoDB using PHPMyAdmin Interface

 

Other way to conver database from MyISAm to InnoDB involved serverl steps.  This involved export/import and drop database and Find and Replace MyISAM to InnoDB.

Convert MySQL Database from MyISAM to INNODB by export/import database

 
Average of ratings: -
Debbie Mahler
Re: Convert database from MyISAM to InnoDB engin in Moodle
 

What happens if you don't have InnoDB option available in the phpMyAdmin interface? I just went to convert mine, and it's not there. Should I contact support from my webhost or is this just a recommendation and not a necessity?

 
Average of ratings: -
Picture of Greg Taylor
Re: Convert database from MyISAM to InnoDB engin in Moodle
 

You can get the script working again by simply updating the ./admin/innodb.php file with the following:

Find: 
$DB->change_database_structure("ALTER TABLE $fulltable TYPE=INNODB");

Replace with:
$DB->change_database_structure("ALTER TABLE $fulltable ENGINE=INNODB DEFAULT CHARSET=utf8"); 

 

... it appears Mysql has removed "TYPE" from ALTER in the recent versions.  Explicitly spelling it all out seems to do the trick thoughsmile  Enjoy!

 
Average of ratings: Useful (1)
Picture of brian avery
Re: Convert database from MyISAM to InnoDB engin in Moodle
 

I'm using moodle 2.1 now but I have, or had, a 1.9 site which I kept as a sort of storage for material I'd previously created and was slowly copying (manually) from site to site. Today though, I decided to upgrade this site to 2.1 to see what happens. Curiosity, mostly. Also, I'd been looking at various open-learn courses from the OU which come in 1.9 format and which (at least my) moodle 2.1 refuses to import. So, I upgraded. No problems there.

I got the 'wrong format' database message too and after baffling myself by looking at myphp, I used the script method mentioned in the second post, by Tim. Worked perfectly.

For any non-expert like me who doesn't know how to start the script, just add it to the url of your site in your browser (so, wwww.mysite.com/admin/innodb.php) and hit the go button. Takes a minute or two, but that's it. Moodle seems to be all smiles afterwards.

Brian

 
Average of ratings: -
Colin Dixon BSc DipEd
Re: Convert database from MyISAM to InnoDB engin in Moodle
 

I tried this fix and I'm still getting

!!! Unsupported redirect detected, script execution terminated !!!

??

Silly me I was running it from ssh,

from the web browser it worked find ;)

 
Average of ratings: -
Picture of andy emerine
Re: How do you convert the database from MyISAM to InnoDB engin?
 

I just used this script with my Moodle 2.1 install.  It seemed to have worked okay.  I looked at the database tables in phpMyAdmin after the script completed. All tables are using InnoDB engine now except for adodb_logsql table.  This table is still using MyISAM engine.  Should I manually convert it in phpMyAdmin?  ...Or leave it alone? 

Just curious, what would be the effect of keeping the engine for the database set to MyISAM?  MySQL has been causing the CPU on our server to spike, the site runs slow, and eventually locks up when there are more than 15 concurrent users.  I'm hoping converting the engine to InnoDB will help. 

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: How do you convert the database from MyISAM to InnoDB engin?
Core developersDocumentation writersParticularly helpful MoodlersPlugin developers

I am pretty sure adodb_logsql is no longer used, which is why the innodb script ignored it. You can safely ignore that table. Or, if you are feeling brave, delete it. (Keep a backup!)

InnoDB is much better at handling concurrency, so hopefully you will seen an improvement.

 
Average of ratings: -
Rosario playing soccer
Re: How do you convert the database from MyISAM to InnoDB engin?
Plugin developers

I am struggling with this DECISION to change to InnoDB for about 2 years now. I didn't change because I would no more be able to use the mysql utility called mysqlhotcopy with which I do a rapid hotcopy of the tables at midday. I then add a database dump every night and everything gets saved to tapes during night.

Of course I could do a database dump twice a day and abandon mysqlhotcopy.

Are there other drawbacks using InnoDB?? Or should we all change to PostgreSQL?? This will be my next DECISION to take as soon as I will migrate to Moodle 2.x after 6 years of Moodle 1.9 with MyIsam.

Rosario

 
Average of ratings: -
Picture of Vijay N
Re: How do you convert the database from MyISAM to InnoDB engin?
 

This is the answer you are looking for in Moodle 2.1 or 2.2:

http://www.yoursite.com/admin/tool/innodb

 
Average of ratings: -
Picture of Greg Mitchell
Re: How do you convert the database from MyISAM to InnoDB engin?
 

Thank you so much Vijay, this worked great for me with v2.2.1 on Ubuntu 10.04 LTS.

 
Average of ratings: -
Colin Dixon BSc DipEd
Re: How do you convert the database from MyISAM to InnoDB engin?
 

$DB->change_database_structure("ALTER TABLE $fulltable ENGINE=INNODB");
$DB->change_database_structure("ALTER TABLE $fulltable ENGINE=INNODB DEFAULT CHARSET=utf8");

still getting

!!! Unsupported redirect detected, script execution terminated !!!

 
Average of ratings: -
Picture of Royce Hobbs
Re: How do you convert the database from MyISAM to InnoDB engin?
 

Works perfect! I run on a couple of localhost Moodle sites and both are up and running without a hitch.

 
Average of ratings: -
Picture of AL Rachels
Re: How do you convert the database from MyISAM to InnoDB engin?
Core developersParticularly helpful MoodlersPlugin developersTesters

Rosario,

Which version of MySQL are you using. Since version 5.1.6, MySQL it has a built in event scheduler. I have not tried to use it myself, yet, but from a quick glance through the documentation you should be able to set some sort of "hotcopy" and "database dump" to run every day automatically. The events can also be set up and managed in phpMyAdmin.

Hope this helps,

AL

 
Average of ratings: -
Rosario playing soccer
Re: How do you convert the database from MyISAM to InnoDB engin?
Plugin developers

On my production server, still Moodle 1.9, I use mySQL version 5.0.95 as offered by SUSE SLES 11 SP 1.

Unfortunately this version will be too old to be used with Moodle 2.3

I already installed SUSE SLES 11 SP 2 on a test server, but again the mySQL version 5.0.95 doesn't fit the needs of Moodle 2.3 just released whereas it worked fine with the latest beta release candidate. So I am stuck until Novell releases a  newer version or I would install a newer version out of the openSuse distribution.

Rosario

 
Average of ratings: -
Picture of Debbie K
Re: How do you convert the database from MyISAM to InnoDB engin?
 

As a non coder and just installed Moodle 2.2.4+ (Build: 20120816)( to test upgrading from 1.9 before takinn the plunge with my production site!) the path to run this seems to be:

mysite.com/admin/tool/innodb/index.php

Though having run the script successfully the Admin Notifications page still shows the same - "Database tables are using MyISAM database engine, it is recommended to use ACID compliant engine with full transaction support such as InnoDB."

Anyone else come across this?

Should I worry about it? I am not a coder so not sure of the importance! Strange as the script seemed to run OK.

Appreciate any input smile

 
Average of ratings: -
Picture of Jerry Mathews
Re: How do you convert the database from MyISAM to InnoDB engin?
 

I realize this was posted almost a year ago, but I thought if there were any out there like me who hasn't done this yet. They would like to know that this script runs perfectly.

Just point to the web address as specified:<mysite.com/admin/tool/innodb/index.php>

And it will do all the work. The notification to do this also disappeared from my update page.

 
Average of ratings: Useful (1)