Category: phpMyAdmin

Convert MySQL Database from MyISAM to INNODB

On By Aryan NavaIn Moodle, phpMyAdmin3 Comments

Installed Moodle 2.0 using shared hosting. When I logon to my Moodle site,  this is the message I am getting

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

In my previous post I mention that you can change the MyISAM database engine to InnoDB using phpMyAdmin : Convert MyISAM to INNODB using phpMyAdmin for Moodle but it will take some time to change each table one by one.

I was looking for a good solutions to convert the MYISAM engine to InnoDB very fast and I found a blog which gives very good instructions.How to Migrate Your MySQL Database from MyISAM to INNODB

Two problem I found in the above post:

1. I was not able to see the video files from work to see the full instruction

2. It shows how to import the database but not showing what are the limitations involved in when you host Moodle in shared hosting environment.

Here are the instructions how you can resolve "Database tables are using MyISAM database engine, it is recommended to use ACID compliant engine with full transaction support such as InnoDB."  message in your Moodle 2.x

 

My current Moodle site is hosted at http://www.bluehost.com and I installed Moodle using Simple Scripts.  When you install Moodle using Simple Scripts, it will create MySQL Database and User name by default.

1. Login to your control Panel. Go where it says Database and you can see MySQL Database and phpMyAdmin. 

image

2. Click on MySQL Database to your current database

MySQL Database to your current database

As you can see Moodle installation created a database and user name.  We are going to export the current database and change MyISAM to INNODB and import back to MySQL using myPHPAdmin

3. Go to phpMyAdmin and export your database

Export Database using phpMyAdmin

 

For the database name you can leave it as since it’s know the current database name and click GO

image

 

Save the file

 

Exporting Database from myPHPAdmin

 

 

4. Open your database file in any tools which you have. In my case, I am using Dreamweaver.

Click on CTRL + F  to  find and replace of "myISAM" with "INNODB" (without quotes, of course) in the SQL dump text file

 

Find-myISAM-Replace-Innodb

5. Go to the top of the sql file which you opened and enter SET AUTOCOMMIT = 0;

set-autocommit=0

 

6. Now go to the end of the file and enter  COMMIT; and save your file.

image

 

Now  are going to import this file into phpMyAdmin but before we do that we have to drop the current database.

7. Go back to phpMyAdmin, Since I am hosting my Moodle in shared hosting I don’t have any options to drop a database.  

Drop Database from myPHPAdmin

8. When I run the SQL query Drop Database “database_name” I got following error message.  Drop Database statements are disabled

 

drop-database-statement-are-disabled 

9. Only way I can drop the database is by going to Cpanel and delete the database from MySQL Admin.  Login to your control panel

Click on MySQL Databases

mysql-database-cpanel

Delete your current database

delete-database-file

10. Confirmation to delete database

 

delete-database-mysql

 

11. Now create a new database but the name should be same as the one which you deleted.  Add user to Database.

 

Create new database in MySQL

 

12. When you add user to Database and give all privileges.

Manger User Privileges

 

13. Open your myPHPAdmin and you will see your empty database and now import your modified database

Import Database to change to INNODB

14. You can see the Database from MyISAM to INNODB are changed. 

from MyISAM to INNODB are changed

If you open your Moodle right now it should work and you wont’ see the error message any more.  This is how you can change your MyISAM to INNODB. 
It took me while to figure it and good luck

Advertisements

Convert MyISAM to INNODB using phpMyAdmin for Moodle

On By Aryan NavaIn Moodle, phpMyAdmin4 Comments

How to convert from MyISAM to InnoDB using phpMyAdmin?

When Installed Moodle 2.x, following message are showing up in the font page of Moodle.

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

Moodle Error : Database tables are using MyISAM database engine

When searched for “convert MyISAM to InnoDB using phpMyAdmin” in Google, I got many responses and I also found a link from Moodle which didn’t help me at all.

I finally found this article from http://richardcummings.info/convert-myisam-to-innodb-using-phpmyadmin/comment-page-1 and the instructions says

You can convert from MyISAM to InnoDB in phpMyAdmin with just a few clicks. To convert your data tables from MyISAM to InnoDB in phpMyAdmin, you will select the table, click the “Operations” tab, and then choose InnoDB from the drop down box next to the “Storage Engine” type. Click “Go” and you are done!

I couldn’t find the Drop Down box next to “ Storage Engine” After playing around, I came to know where to find drop down box

 
1. Go to phpMyAdmin

2. Select your database and click on Structure icon

convert MyISAM to InnoDB using phpMyAdmin

 

3. This is what you will see when you click on structure icon

convert MyISAM to InnoDB for Moodle

4.  Now click on Operations tab and now you will see Storage Engine and if you click on the drop down, you can see InnoDB.  Select InnoDB and click GO

image

5. Now if you go back to your database table, you can see type is changed to InnoDB

converted MyISAM to InnoDB using phpMyAdmin Storage Engine

 

Using this method you can easily  convert your database from MyISAM to InnoDB using phpMyAdmin. 

Many Thanks to Richard Cummings