The Problem

Recently I was migrating a new WordPress site from a staging server to the production server. Normally, I would have set the development site on a sub-domain of the site, however their hosting account had been suspended at the time, and needing to get started immediately, I set up the staging site on a different server.

With the site developed, content uploaded and QAed, it was time to migrate the site. Moving the files didn’t take too long, but when I tried to import the database, I was met with an error:

Unknown character set: 'utf8mb4'

Since WordPress 4.2, core tables use utf8mb4, changed from utf8. As the production server was only running MySQL 5.1, it didn’t recognise utf8mb4 as a valid character set, which was introduced in MySQL 5.5.3.

The Solution

When exporting your database from phpMyAdmin, choose Custom from Export Method.

Under Format-specific options, set the  Database system or older MySQL server to maximize output compatibility with: option to MYSQL40. This will change the character set of the tables from utf8mb4 to utf8 and your database will now import correctly.

There was one more issue that I encountered now that the database had migrated. A number of pages now contained some � characters where hyphens or apostrophes had been. What made it more difficult was that the TinyMCE WYSIWYG editors wouldn’t load the content that included these characters. Being not too many pages on the site, I was able to simply copy and paste content from the development site to the production site. However this isn’t ideal if you have a considerably larger site.

Moral of the story, always set up your development environments with the same server configuration as production as this makes life a lot easier when migrating, especially if your site is running WordPress version 4.2 or later. However if you don’t have a choice it’s not the end of the world and there are ways around it.