Professional javascript and node.js engineering for functional websites.

InnoDB on Ubuntu?

May 3, 2011

I'm currently working on a Drupal 6.x website that recently changed its database storage engine to InnoDB. I was not very aware at the time, but it does have some issues while running on Ubuntu.

Of course, I do all my development locally and I recently upgraded to the shiny new Ubuntu 11.04. For a while I blamed the ridiculously slow speed of this particular database to the OS upgrade, as, for some reason, I had to re-install my entire LAMP stack after the upgrade; the MyISAM to InnoDB switch happened around the same time.

Something is not right when Views takes minutes to save. Another Drupalista working on the website is using Ubuntu as well, so we decided to look into the matter. We could not find a proper solution right away, but decided that, for sanity's sake, we can just change our local databases to run MyISAM instead. There's no way to do a bulk change of database storage engine, so we came up with the following SQL query:

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=MyISAM;') FROM information_schema.tables WHERE engine = 'InnoDB' AND table_schema = 'DATABASE_NAME';

Change DATABASE_NAME to the database name, of course. Copy & paste the results into a text editor and remove whatever embellishing characters your MySQL client decides to add to the results table, then save the file with a .sql extension. The result of this is a nice list of queries that will bring back speed into your database life, for the time being.

I do not know for sure what the problem is, but this seems like a temporary fix. I wonder if anyone in the community has had similar experiences...

Let me know in the comments.