UpdraftPlus is arguably the most popular WordPress backup plugin available. With over 2.6 million downloads, it's installed on 500,000+ live sites. For such a popular plugin, you would think it would pretty much work in every situation, at least that's what I thought. UpdraftPlus has been my go to tool of choice to move sites to new servers, refresh development sites with production data, or update production sites from development or staging environments. However, as I just learned the hard way, UpdraftPlus may not be as reliable a tool as I once thought. Not only did UpdraftPlus fail to restore a newly remodeled website, it trashed the target production WordPress database in the process. Contacting support in this situation was no help at all. All they did is echo back what I had already diagnosed, reported to them, and repaired.
The Problem
This issue reared its ugly head after I had remodeled the YellowWood Group's website and was attempting to move the new site back to production, currently hosted in a shared environment on GoDaddy. While I was given FTP access to the GoDaddy site, I was not given access to the GoDaddy site management tools. So in a way I was working blindly for some aspects of this engagement.
When I setup the development environment on one of my servers, the process went very smoothly. I created the database and user in cPanel as I generally do, backed up the production site with UpdraftPlus, and restored the production data to the development instance. Within minutes, I had a fully functional development version of the production site.
Development required about a week. We conducted user acceptance testing over the weekend and after a round of final tweaks, my client approved the migration back to her production site. This is where things went wrong—horribly wrong!
Almost as soon as I started the UpdraftPlus restore process on the production server, I received a message that the target database didn't support utf8mb4_unicode_ci collation and the restore failed. This message is bad enough, but what's worse is the message that came immediately afterward. The WordPress admin area refused to load and was replaced by a WordPress error that the database was damaged and needed to be repaired. I started the repair process only to see another message reporting that the wp_options table didn't exist. So now the questions are “What is utf8mb4_unicode_ci collation?" and “Where did wp_options go?". I also had to figure out how to fix this issue fast because without wp_options, WordPress doesn't run. UpdraftPlus had just killed this site.
The Diagnosis
Without access to PHPMyAdmin or the GoDaddy site management tools, I knew I couldn't recreate wp_options from a backup. What I did was change the table prefix in wp_config.php. Doing this will allow you to reinstall WordPress to the same pre-existing database. The install process creates a fresh set of database tables with the new table prefix. Of course, all of the production data will be gone, but I do have an UpdraftPlus backup copy, don't I?
Unicode Encoding
Next, I needed to find out more about the utf8mb4. Before we can fully understand utf8mb4, we need to learn a little about its predecessor, utf8. The utf8 encoding can represent every symbol in the Unicode character set. Utf8 is a variable-width encoding that uses a maximum of three bytes per character and contains only Basic Multilingual Plane characters. For a long time, the MySQL utf8 charset for databases, tables, and columns, was the default Unicode encoding. As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character and supports supplemental characters such as emoticons.
With the release of WordPress 4.2, the WordPress developers upgraded the application's tables to utf8mb4. A site will only upgrade when the following conditions are met:
- Your current WordPress installation uses the utf8 character set.
- Your MySQL server is version 5.5.3 or higher (including all 10.x versions of MariaDB).
- Your MySQL client libraries are version 5.5.3 or higher. If you’re using mysqlnd, 5.0.9 or higher.
This means that all new sites created with WordPress 4.2 or higher, when coupled with MySQL 5.5.3 or higher, will automatically default to utf8mb4. Keep this in mind when you are putting up a new site. The new WordPress tables will be utf8mb4. You will encounter the same problems described in this article if you try to restore a database created with WordPress 4.2 or higher to a WordPress instance running a version of MySQL earlier than 5.5.3. In this circumstance, my development server is running MySQL Version 5.5.42, which supports utf8mb4. I couldn't discover what MySQL version is running on the production server, but whatever version it is, since it doesn't support utf8mb4, it must be earlier than 5.5.3. The UpdraftPlus log captures the error.
1 2 3 4 5 6 7 | 0000.256 () An error (1) occurred: Unknown character set: 'utf8mb4' - SQL query was (type=2): CREATE TABLE `wp_options` ( `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `option_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `option_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `autoload` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes', PRIMARY KEY (`option_id`), UNIQUE KEY `option_name` (`option_name`)) ENGINE=MyISAM AUTO_INCREMENT=15473 DEFAULT CHARSET=utf8 ; 0000.256 () Leaving maintenance mode 0000.256 () Disabling Maintenance mode… 0000.257 () Error message: An error occurred on the first CREATE TABLE command - aborting run 0000.257 () Restore failed... 0000.258 () Error message: An error occurred on the first CREATE TABLE command - aborting run 0000.258 () Restore failed |
Where Did wp_options Go?
This was pretty easy to figure out. The first thing UpdraftPlus does before restoring a table is to drop it if it exists. UpdraftPlus deleted the wp_options table and then failed to recreate it because of the character set. This unveils a glaring oversight on behalf of the UpdraftPlus development team for not including a database compatibility check before attempting to do a restore, especially in light of the database change in WordPress 4.2.
The Remedy
To fix this problem, I had to convert the development database and tables to plain old utf8 to make them compatible with the production server. The following MySQL commands will take care of this. Each table needs to be individually converted.
1 2 | ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; |
Once the conversion to utf8 is complete, create a database backup with UpdraftPlus and restore it to the production environment.
UpdraftPlus Closing Thoughts
UpdraftPlus has always worked reliably for me up until this one incident. How rare is it that we would ever have to restore to a version of MySQL earlier than 5.5.3 again? I have no idea, but now that WordPress 4.2 and higher defaults to utf8mb4, you can bet this isn't the last time we'll hear of this problem.
One suggestion that the UpdraftPlus support team made is to update the target database to a newer version. This idea made me laugh for two reasons. First, it's not possible for an end user to update a MySQL instance in a shared server environment. Second, MySQL upgrades do not migrate your databases and data. This is the primary reasons most hosts won't upgrade MySQL. There is no data migration upgrade path. Recovery of data from a MySQL upgrade is a manual process. Please visit my article Recover MySQL Tables after Upgrade/OSX for more details.
Leave a Reply