Unknown collation: 'utf8mb4_unicode_ci'

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.

What? Installing plugins and themes writes data to the database. I may not have actual site content locally, but stuff does get written to the database that I want to move with me when I deploy to production.

I’ve worked at many dev agencies and never heard of a single time someone installed WordPress on the production server.

It’s just suuuuper frustrating when an update to a major platform like WordPress is deployed and it’s not backwards compatible.

At least make it an option to use utf8mb4, not some automagic thing that happens. If it’s going to do something that makes my database not work with older MySQL versions, let me decide on if I want it to do that or not, because maybe in the future I will have to move the database to a server that has a MySQL version < 5.5.3. Who knows? At least until MySQL < 5.5.3 is extinct.

@jtleathers that method worked for me. Although, does anyone know what that’s actually doing? Am I losing data? Is it a good long term solution?

+1 for jtleathers export mentioned above.

I had the exact same problem: localhost environment with mysql 5.5.38, needed to migrate to a live server with mysql 5.1.73.

My newly-migrated site seems to be working just fine thus far, but as mctenold had asked, I have no idea if I’m losing some kind of important data I may not be aware of.

But what a freaking bugger! I’ve spent nearly 2 hours on this issue. There are many of us who develop locally using mysql 5.5.X and need to deploy on web hosts using something older.

Currently it seems the only solution is to downgrade my local environment or export with the compatibility settings mentioned above and (maybe?) risk losing some data.

Otto – I realize that your dev sites are used primarily for developing themes/plugins, but a lot of us do actual content population within a localhost environment because it’s much faster than doing so on a remote server.

BackupBuddy developer here: It is absolutely INCORRECT to say that migrating sites to new server is uncommon. BackupBuddy helps users move several hundred thousand sites per year to new servers.

We are already seeing a flood of users encountering this problem attempting to move their site to new servers with older MySQL versions. They of course are puzzled why this is a problem since WordPress states it supports this older MySQL version.

Many users these days practice things such as local development, staging, etc which are good modern practices. WordPress has effectively put a huge thorn in the sides of developers and in my opinion has made an extremely big mistake.

@dustinbolton – Thanks for the comment. FWIW, I actually discovered this error while trying to perform an unsuccessful BackupBuddy migration.

Seems like a well intentioned feature but badly conceived implementation – a case of just because you can doesn’t mean you should.

How many users actually _need_ this and if anyone does need it then I’m sure they’d be happy to just have to tick a box to make it happen and also have that throw up big Warning about the implications as regards the future transportability of your site. Surely preferable to _forcing_ this onto the general WordPress user population whether they want it or not.

We know from the chatter above that this “conversion” can be done at _any_ time so there is absolutely no need to make it a “stealth” one-way upgrade is there?

I haven’t seen any good justification for why this has been forced upon the general WordPress user population and in fact I’ve seen some pretty wacky comments from a “Tech nerd what works on WordPress.oeg” (sic) about what users should and shouldn’t be doing as regards how they manage their site developments and workflows that would kinda suggest a certain lack of connectedness with real-world usage and may well be indicative of how this got slipped in without there being any true consideration of the potential impact.

So please WordPress devs, you are great guys and gals and WordPress is a great application but please consider your users and their real-world needs and seriously consider turning this into an _optional_ action selected through a tick-box for those that _need_ it (or want it just for the sake of it) and also provide a “back out” for those whose sites have already been affected through no fault of their own. It’s not too late to make this right.

Thanks.

Yea, I’ve just resorted to not using 4.2, simple as that, until they fix this. It’s too big of a headache to use it.

The WP Migrate DB plugin translates the database from one collation to the other when it moves 4.2 sites between hosts with pre- or post-5.5.3 MySQL.

@mcdonna – this isn’t really the issue, please refer to the information from earlier posts:

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.

and

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.

and

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.

which basically mans it’s a one way operation according to the www.ads-software.com Tech Guy.

@drprotocols, I understand what you’re saying. But if you have not used 4-byte characters (e.g., iOS emojis) in your editing, then reversing the process should work. I ran into the problem earlier this week when working locally, where I have up-to-date MySQL, on a site that is hosted on MediaTemple and is running 5.1.73.

When I used WP Migrate DB Pro to migrate the database to my server, it converted the database collation to utf8mb4. In this case, I just needed to edit code and test with their current database to ensure that all was working, but I asked their tech support if this would be a problem if I were to need to migrate it back. The response was:

WP Migrate DB Pro 1.4.7 takes care of switching back to utf8 when going from MySQL 5.5.3+ to a lesser version.

@mcdonna – I appreciate that you may have found a workaround that happens to work in your particular case and I am happy for you. But this cannot be offered as a general, safe, guaranteed “solution” in all possible cases. The support response you got, whilst I am sure well-meaning (and basically they would say that) is wwaayy too vague to have any meaning at all.

It’s sad when you see something like WordPress go the way of “products” from from traditional monolithic corporations. Eventually everything has to end up conforming to their view of how the world should operate and they forget their real world users who made them popular in the first place. The thing is you shouldn’t have to have had to do what you did – there is absolutely no good reason for you having been put in that situation.

@mcdonna – hmm, seems like I posted before but maybe _someone_ is removing my posts. This in itself is not a solution in the general case but might happen to be something that coincidentally works (as far as you may know initially) in your particular case. Earlier in this topic it is stated by a “www.ads-software.com Tech Guy” that the upgrade is a one-way process and you cannot safely/reliably revert database tables once upgraded. So the WP Migrate DB support may certainly tell you that and perhaps they even believe it but what they state is rather too general to have any real meaning. But at least it appears to work for you ??

Regards

If you have no 4 byte characters, then you can probably do an export in a way that won’t lose data. But you can’t know that for sure, really. Converting back is a bad idea.

In the long run, you should update your MySQL versions to 5.5.3 or later. If your hosting can’t do it, find better hosting. Because they can do it. Because there’s no reason not to do it. And because MySQL’s “utf8” doesn’t support the entire UTF character set properly, only utf8mb4 does.

If you haven’t seen this video yet, take a half hour and watch it to understand more: https://www.youtube.com/watch?v=yQaRUEwEKxE

“Getting your host to update MySQL” or “finding a new host” is not a fix to the problem.

mctenold: Yes, it is. Because the limited utf8 character set is going away. Not using it anymore is the only long term solution you will find.

Update or get left behind. That’s the bottom line. The goal posts are moving.