Unknown collation: 'utf8mb4_unicode_ci'

I’m in the same boat and just made a thread asking the same thing.

This is what is causing the problem.

There are a few replies in that thread with the same problem and no responses.

Use a local dev version that matches your actual live version.

Alternatively, bug your web host to upgrade your live site to a newer version of MySQL. You want 5.5.3 or later. Preferably much later.

Use a local dev version that matches your actual live version.

Is there a way to do that when you are working on multiple client projects who all have different hosts and therefore different versions of PHP / MySQL?

Also, that does not solve the current dilemma of what do about a database that has already been automatically updated and needs to be rolled back to how it was pre-4.2.

Use a local dev version that matches your actual live version.

Alternatively, bug your web host to upgrade your live site to a newer version of MySQL. You want 5.5.3 or later. Preferably much later.

This doesn’t fix the problem. Like @jtleathers said, sometimes the client has a MySQL version that we can’t control.

So basically the latest WordPress is not compatible with MySQL < 5.5.3 at all? That seems like a pretty major change.

Is there an easy way to downgrade your WordPress version?

So basically the latest WordPress is not compatible with MySQL < 5.5.3 at all? That seems like a pretty major change.

No, it’s perfectly compatible. It just updates the database to use utf8mb4 mechanisms on 5.5.3 and later installations.

If you’re actually migrating databases around (which is a bad practice to begin with), then they need to be between similar versions. MySQL has a version breakage at that point, and 4 byte character encodings don’t work on the lower versions. So, WordPress only uses those on versions that actually support it.

WordPress does not have any built-in ways to move database data around between different sites. So, don’t do that, or do it in a way that isn’t just using mysql dump files. Do an export/import instead, or use a migration plugin that handles this sort of thing for you.

For various security reasons, it is best to use later versions of everything, MySQL included. Bug your host to upgrade it. WordPress works fine on older versions, but it works *better* on newer ones. WordPress works fine on PHP 5.2 as well, but I’d still recommend updating your PHP version to 5.5 or later. Same reason.

If you’re actually migrating databases around (which is a bad practice to begin with)

How is developing locally and migrating your database to a dev or production server bad practice?

So it seems people with this workflow are the ones that are going to get screwed.

I think they forgot this common scenario when applying this update.

I think it’s pretty common practice to install WordPress locally when you begin working on a new site, and then move everything to a dev or production server once the time comes.

There has to be a way I can convert or export the database to be compatible with versions pre < 5.5.3.

Even if I have to manually open the .sql export file and do a search and replace. Any possibilities there?

I think it’s pretty common practice to install WordPress locally when you begin working on a new site, and then move everything to a dev or production server once the time comes.

Haha, I think we all know that “pretty common” is an understatement.

There has to be a way I can convert or export the database to be compatible with versions pre < 5.5.3.

Hopefully someone who knows about such things will see this thread and help us out!

I’m not sure you’re getting it here. You cannot undo this. Older versions of MySQL don’t support utf8mb4 properly. You cannot do hand-wavy things to add that support. They simply cannot support those character sets. Trying to “fix” an export file will simply result in the data becoming corrupted if you use any of those unsupported characters.

If you’re going to be importing into an older version of MySQL, then you need to develop on that same older version of MySQL. Downgrade your local dev version if you need to do this sort of thing.

If you want to try to search and replace “utf8mb4_unicode_ci” with “utf8_unicode_ci” in your export file, then that might work. Probably won’t though. And it will definitely result in data loss most of the time.

Older versions of MySQL don’t support utf8mb4 properly.

Which is why we were asking if anyone knows how to export or alter the database to go from utf8mb4 back to utf8.

If you’re going to be importing into an older version of MySQL, then you need to develop on that same older version of MySQL. Downgrade your local dev version if you need to do this sort of thing.

That’s good to know going forward but doesn’t help with our current situation unfortunately.

Which is why we were asking if anyone knows how to export or alter the database to go from utf8mb4 back to utf8.

Right. You can’t actually do that. If you have 4 byte characters in the database, then going backwards to a character set that doesn’t support them properly will cause your text to be truncated at the unsupported characters. And if you’ve been developing with 4.2, then you probably do have 4 byte characters somewhere.

You could go through and manually remove those characters, if you like. But it would probably take less time to copy and paste your text into the live site instead. Or do a normal WordPress export/import operation. Something like that.

Like I said earlier, if you want to edit your export files and change the collation with a search/replace, you can try it. No idea if it will work on your data though.

So, I was able to get this to work by doing the following in PHPMyAdmin:

1) Click the “Export” tab for the database
2) Click the “Custom” radio button
3) Go the section titled “Format-specific options” and change the dropdown for “Database system or older MySQL server to maximize output compatibility with:” from NONE to MYSQL40.
4) Scroll to the bottom and click “GO”.

This worked for me and I was able to import that database to a server running MySQL 5.1. This thread about a plugin gave me the idea to use that setting, I actually don’t know what MYSQL40 even means.

Based on what Otto said, I guess this wouldn’t work if your database has 4 byte characters saved in it but hopefully this helps you mctenold and maybe a few others.

Huh. Never seen that option before. I suppose that would do the trick, although you might be missing the 4 byte characters, if any are there. I’d double check after you do your import.

Did WordPress not think this update through? I feel like this is a common practice.

Moving forward I’m going to have to downgrade my local MySQL or not use WordPress 4.2. How is this at all a good “upgrade”?

There should at least be an option before you install WordPress that says “Try to use utf8mb4”, so you can opt out if you want.

It’s pretty uncommon, actually. WordPress is a content management system. Most people write their content on the actual site where it will be published. Not on some other dev site first.

Sure, you use a dev site to develop themes and plugins, but those are files. They have nothing to do with the content in the database. My dev site contains test content, not actual live content.