Moving from a mySQL 5.5 back to mySQL 5.1 server

I ran into an issue today migrating a site from one host running MySQL 5.5 to another host running MySQL 5.1.x.

The problem was when exporting the database from the newer server using phpMyAdmin and then importing to the older server, I got an error ‘[ Unknown collation: ‘utf8mb4_unicode_ci’ ]

You can, when exporting from phpMyAdmin, look for the option ‘Database system or older MySQL server to maximize output compatibility with’ and pick MYSQL4 from the popup. This will probably solve the problem, but in my case didn’t, and it took a while to track the problem down to the WordFence security plugin that was running on the site. Whatever tables it creates won’t import into the older server, and the import fails.

I removed the plugin on the source server and all the database tables it creates (there’s a checkbox option you need to select to delete all data on plugin deletion – if you don’t check that you need to remove the tables manually). Then I was able to export the database again, selecting the MYSQL4 option and everything imported onto the older destination server without any trouble at all.

I’ll re-install the plugin now that the site is migrated.

If you’re having this issue but aren’t running the Wordfence plugin, check to see what plugins you are running that are creating tables in the database and see if you can export/import without them, at least to pin down the problem.

If you’ve got content on your site that makes use of characters not supported in the standard utf8 encoding, then you’re going to be stuck, but if not and you’re running into the issues I was, hopefully this will help.