# Updating the bava database from MyISAM to InnoDB

Some of our initial explorations in the Reclaim Cloud have been around automagically scaling for larger WordPress Multisite instance. One of the things Tim discovered yesterday is that the Gallera Cluster that scales databases only works with InnoDB type MySQL databases not MyISAM. Turns out both this blog and ds106 run MyISAM, so I found this Stack Exchange post on upgrading from one to the other. Needless to say I got a backup of my database before trying anything, and then ran this code in the general SQL area of phpMyAdmin:

SET @DATABASE_NAME = 'bavatues_wp1';

SELECT  CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     ENGINE = 'MyISAM'
AND     TABLE_TYPE = 'BASE TABLE'
ORDER BY table_name DESC;

That command spit out the following that I then ran in the SQL area of phpMyAdmin for this blog’s database:

ALTER TABLE wp_wordtube_playlist ENGINE=InnoDB;
ALTER TABLE wp_wordtube_med2play ENGINE=InnoDB;
ALTER TABLE wp_wordtube ENGINE=InnoDB;
ALTER TABLE wp_users ENGINE=InnoDB;
ALTER TABLE wp_usermeta ENGINE=InnoDB;
ALTER TABLE wp_term_taxonomy ENGINE=InnoDB;
ALTER TABLE wp_term_relationships ENGINE=InnoDB;
ALTER TABLE wp_terms ENGINE=InnoDB;
ALTER TABLE wp_termmeta ENGINE=InnoDB;
ALTER TABLE wp_stp_tags ENGINE=InnoDB;
ALTER TABLE wp_spamlist ENGINE=InnoDB;
ALTER TABLE wp_scaptcha ENGINE=InnoDB;
ALTER TABLE wp_richcomments ENGINE=InnoDB;
ALTER TABLE wp_ratings ENGINE=InnoDB;
ALTER TABLE wp_quotescollection ENGINE=InnoDB;
ALTER TABLE wp_posts ENGINE=InnoDB;
ALTER TABLE wp_postmeta ENGINE=InnoDB;
ALTER TABLE wp_pollsq ENGINE=InnoDB;
ALTER TABLE wp_pollsip ENGINE=InnoDB;
ALTER TABLE wp_pollsa ENGINE=InnoDB;
ALTER TABLE wp_podpress_stats ENGINE=InnoDB;
ALTER TABLE wp_podpress_statcounts ENGINE=InnoDB;
ALTER TABLE wp_options ENGINE=InnoDB;
ALTER TABLE wp_ngg_pictures ENGINE=InnoDB;
ALTER TABLE wp_ngg_gallery ENGINE=InnoDB;
ALTER TABLE wp_ngg_album ENGINE=InnoDB;
ALTER TABLE wp_links ENGINE=InnoDB;
ALTER TABLE wp_flickr_post ENGINE=InnoDB;
ALTER TABLE wp_comments ENGINE=InnoDB;
ALTER TABLE wp_commentmeta ENGINE=InnoDB;
ALTER TABLE wp_blc_synch ENGINE=InnoDB;
ALTER TABLE wp_blc_links ENGINE=InnoDB;
ALTER TABLE wp_blc_instances ENGINE=InnoDB;
ALTER TABLE wp_blc_filters ENGINE=InnoDB;
ALTER TABLE wp_bibliography ENGINE=InnoDB;
ALTER TABLE wp_as3cf_items ENGINE=InnoDB;
ALTER TABLE wp_amber_queue ENGINE=InnoDB;
ALTER TABLE wp_amber_check ENGINE=InnoDB;
ALTER TABLE wp_amber_cache ENGINE=InnoDB;
ALTER TABLE wp_amber_activity ENGINE=InnoDB;
ALTER TABLE wp_ak_twitter ENGINE=InnoDB;
ALTER TABLE wp_ak_404_log ENGINE=InnoDB;

That was all it took, they were changed successfully. After that  I restarted the Gallera Cluster in Reclaim Cloud, and everything seems to be running smoothly. I’m currently working up the courage to try the same thing for ds106 🙂

This entry was posted in bavatuesdays, Reclaim Cloud, sysadmin, WordPress and tagged , , , , , . Bookmark the permalink.

### 3 Responses to Updating the bava database from MyISAM to InnoDB

1. Ed Beck says:

I’m following the Reclaim.cloud project as closely as can, with as many nuggets as the teams puts out in the open.

One of my main interests in SUNY is Open Educational Resources, and one of the main tools we use is Pressbooks. With a Pressbooks installation being essentially a WordPress Multisite with custom plugins that convert every child-site into a book, I’m really looking forward to seeing what Reclaim.Cloud can do in auto-scaling, and how you will integrate it in with existing services. I also wonder if the flexibility of a docker based solution will allow you to use all of Pressbooks features, like accessible math with MathJax that Reclaim can’t currently support.

I’m also excited by the idea that Reclaim.Cloud would extend capabilities beyond LAMP, to allow us to play with tools like Discourse.

• Reverend says:

Ed,

Yeah, it is pretty exciting, and we are still playing but we can definitely hook you up with an invitation. I think scaling is the key reason to explore Pressbooks and WPMS in the Reclaim Cloud given if you have major traffic and usage you would automatically be able to meet the demand, yet only pay for what people use. So, there is definitely an economies of scale, but it is also billing like a utility, if you turn off the lights or use less resources you don’t pay for them.

The Discourse and other custom applications is really interesting, and Tim is in the process of making installers for many of them. I can confirm Discourse runs cleanly via container, and we also have Ghost, Etherpad, Jitsi, ShinyApps, R Studio, Minecraft, and Manifold is coming (a nice alternative to Pressbooks) and many more. It is pretty exciting, and while we don’t want to over promise and under deliver, it really could be something special.

So, you excitement could be very well founded 🙂

This site uses Akismet to reduce spam. Learn how your comment data is processed.