This is a technical post on how to convert from MyISAM to InnoDB using phpMyAdmin.
Yesterday, I was using phpMyAdmin to set up database tables in MySQL. The solution that I was implementing called for me to use foreign keys to relate the tables to one another. Well, foreign keys require the use of the InnoDB search engine instead of the default MyISAM.
Often, within phyMyAdmin, I type my SQL syntax directly using the SQL tab within phpMyAdmin (which in this case would be “ALTER TABLE tablename ENGINE=InnoDB;”). However, a customer recently asked me how to convert the tables from MyISAM to INNODB using the phpMyAdmin graphical interface so I thought I would give that a go.
When I searched for “convert MyISAM to InnoDB using phpMyAdmin” in Google, I received a bunch of responses that were wrong…or maybe I should just say outdated. (I think Google should update their algorithm for technical queries to default to recent, rather than older, material.) These outdated posts talked about the need to modify an admin config file and suggested a process that seemed to take a while. Not so.
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!
NOTE: Initially, my conversion was unsuccessfully because I received a message that said The Used Table Type Doesn’t Support FULLTEXT Indexes. If you receive a similar message, follow that link to solve it!
Hope this helps. Enjoy the day,
Richard Cummings
9 Comments on “Convert MyISAM to INNODB using phpMyAdmin”
I just installed the latest phpmyadmin and logged in as ‘root’. I failed, however, to find ANY dropdown list that let me change a storage type. So I am really puzzled.
Finally, I found a SQL query that would do so. So I ran that from within phpmyadmin instead… and it worked:
ALTER TABLE `Events` ENGINE=InnoDB;
ALTER TABLE `Frames` ENGINE=InnoDB;
ALTER TABLE `Stats` ENGINE=InnoDB;
Btw, this is for a misbehaving ZoneMinder installation and it was suggested to convert these tables to InnoDB. Any insight on the web-gui way of doing it, as you suggested, would be appreciated. I’m puzzled why my experience differed from yours.
I am using the latest phpmyadmin as well, and the dropdown list that you speak of is non existent. I was logged in as root of course.
To those of you above who are not seeing the storage engine drop down box, I am not sure what version of phpMyAdmin that you are using. I just double-checked the instructions above and all is good. Anyway, if you cannot find the graphic choice, follow the instructions from Paul above 🙂
Yes, indeed your instructions are correct, however for some reason I missed the part about the tables, and I thought there’s a drop down list for the whole db. After individually selecting the tables, I was able to find it and easily convert them.
Thank you for this post.
Hi,
I tryed Paul’s code but received:
MySQL said:
#1289 – The ‘InnoDB’ feature is disabled; you need MySQL built with ‘InnoDB’ to have it working
Interesting…
Ricardo
I agree with everyone, I am using myphpadmin
* cpsrvd 11.28.64
* MySQL client version: 5.1.56
* PHP extension: mysql
and there is no Storage Engine drop down. Also the code Paul’s code is wrong and it’s not working.
Pingback: Convert MyISAM to INNODB using phpMyAdmin for Moodle | Virtualize SharePoint and SharePoint Virtualization
Hi Richard Cummings,
I finally found the drop down box next to the “Storage Engine” but it took me sometime. I added the instructions in my blog about how I found “Storage Engine” and I hope this will help others.
http://virtualizesharepoint.com/2011/05/11/convert-myisam-to-innodb-using-phpmyadmin-for-moodle/
thanks Paul.. it is working