WordPress Unknown Collation Error: How to Avoid This Ticking Time Bomb When Migrating Your Website

WordPress version 4.2+, silently and permanently modifies your database if certain conditions are met on your web server. This can lead to a downed website, hours of lost time, and unanticipated expenses— particularly when migrating your website from one hosting environment to another. Here’s what you need to know to avoid this nightmare.

Unknown collations

WordPress Uknown Collation Error

The differences between MySQL database versions never really mattered much— until WordPress 4.2.

WordPress 4.2+ forces an irrevocable database table change to utf8mb4_ci unicode collation if your web server is running MySQL 5.3+— without your knowledge.

Perhaps there are good reasons the WordPress team chose such an approach (e.g., security), but next time you migrate your website, you may be in for an unpleasant surprise with an unknown collation error, which results in a failed migration attempt and an ugly white page for your website.

DesktopServer Lights the Fuse

In hindsight, everything started unraveling shortly after I upgraded DesktopServer from version 3.65 to version 3.8— that’s the web server software I use to quickly build, modify, and test websites, before pushing them out to a staging server for my clients to see.

Among other things, DesktopServer 3.65 installs MySQL 5.1, and DesktopServer 3.8 installs MySQL 5.6. And unfortunately, once your database gets converted to utf8mb4_unicode_ci collation, there’s no turning back.

Note: MAMP Pro 3, a popular alternative to DesktopServer for the Mac OS, runs MySQL 5.5.

Kaboom! Website Down!

After migrating my site (now with a MySQL 5.6 database and utf8mb4 collation) back to the live server (running MySQL 5.1 and no utf8mb4 collation), I was completely hosed, and my website went down for the count.

No Help from Hosting Companies

When this happened to me, I was told by my hosting company (MediaTemple) that they have no plan to update MySQL for their gridserver (gs) hosting service. But it’s 7 years old, which points to a bigger problem…

Many Hosting Companies Have Hosting Plans That are Stuck in 2005

In an ideal world, clients who hire me to improve their existing website would come to me with a hosting company that is already running the latest versions of MySQL and related software. But in the real world, they don’t. Here’s a sampling of MySQL versions running on some of my client’s websites:

  • GoDaddy*: MySQL 5.0 (released in 2005)
  • Hostgator: MySQL 5.0 (released in 2005)
  • Rackspace: MySQL 5.0 (released in 2005)
  • Mediatemple (gs): MySQL 5.1 (released in 2008)
  • Mediatemple (WordPress Hosting**): MySQL 5.5 (released in 2010)
  • WPEngine: MySQL 5.6 (released in 2013)

* On another client’s site, GoDaddy is running MySQL 5.6.24 for the same hosting plan.
** Mediatemple’s WordPress Hosting plan was unveiled in March 2014 (i.e., this plan wasn’t a viable option until recently).

Update (8/3/15): This afternoon, I discovered that the Hostgator site is now running MySQL 5.5, just days after I wrote this article. Always, always, always check the version of MySQL that you are migrating to/from!

No Help from ServerPress

ServerPress (developers of DesktopServer), offered no advice or guidance as to how to revert back to the old version of DesktopServer— they knew I had opened a Pandora’s box, and I suppose it really wasn’t their problem.

Update (8/3/15): Serverpress eventually replied days later with instructions for uninstalling/re-installing DesktopServer… which, by that time, were useless. Still, I highly recommend DesktopServer, and this issue really wasn’t their fault. However, prior to the upgrade they could have included a warning regarding the possible consequences.

No Help from WordPress

It would have been nice to know before upgrading to WordPress 4.2 what was about to happen. Better yet, it would have been nice to have the option to not switch to utf8mb4ci unicode collation. This was a controversial move by the WordPress team that many in the WordPress community are just beginning to recognize and talk about.

Picking Up the Pieces, for Now

Initially, I suspected that the issue had something to do with iTheme’s BackupBuddy— that’s the software I use (and highly recommend) to back up and migrate WordPress websites. After all, it was simply reporting the WordPress unknown collation error:

BackupBuddy Notification

Thankfully, iThemes’ support was kind enough to explain what was going on, and suggested I create a virtual machine within Parallels, so that I can run 2 versions of DesktopServer on the same computer, at the same time— version 3.65 for websites running on older versions of MySQL, and version 3.8 for those running on newer versions of MySQL.

Creating a Virtual Mac

With Parallels, I created a virtual Mac on my SSD drive (don’t do this unless you have an SSD drive)…

Mac Virtual Machine

Update (8/3/15): Scratch this idea– I put it into full practice this afternoon and it’s very erratic. I’m now considering the addition of a second SSD drive for my Mac Pro. Hopefully, I can switch to either startup drive and run 2 versions of DesktopServer as needed.

Key Take Away

In the future, I’ll make it a point to check the version of MySQL that my new client’s are running prior to migrating in either direction. If you’re using BackupBuddy, this is very easy to do via BackupBuddy -> Server Tools.

Is your WordPress website not converting like it should? Stop the madness and let me help. From new custom websites, to complete makeovers, to monthly maintenance. Learn more here.

Comments

  1. Joelle

    Thanks for posting this. I went through the exact same process as you, and have been pulling my hair out trying to find a solution. It’s good to know I’m not alone 🙂

Leave a Reply

Your email address will not be published. Required fields marked with an asterisk.


*