Collation error UTF8-MB4

That’s not exactly a WordPress problems, and it would be easier to upgrade MySQL on the server, to be honest.

You need MySQL 5.5.3 or higher to support the utf8mb4_unicode_ci collation.

Have you contacted your hosting provider?

Alternatively, you can try changing your collation to utf8_general_ci but that might cause character encoding problems with the existing data.

Here are some more threads about this in case you want to follow them on the off chance someone actually comes up with a solution for those of us in this situation:

https://www.ads-software.com/support/topic/sql-error-when-importing-database-1?replies=3

https://www.ads-software.com/support/topic/unknown-collation-utf8mb4_unicode_ci?replies=11

The utf8mb4 Upgrade

https://stackoverflow.com/questions/29909290/collation-issue-is-it-possible-to-downgrade-convert-from-utf8mb4-unicode-ci-to

https://stackoverflow.com/questions/29916610/1273-unknown-collation-utf8mb4-unicode-ci-cpanel

Thanks for the collection!

Hi everyone, thanks for all the answers.
I decided to change the collation to utf8-general_ci, it worked on the importation.
And no problem with character encoding.
You are faster than the customer service from OVH, and it’s free.
You are amazing, guys, ahah.

You’re welcome!

That is not a “solution” but just a hack to try and workaround a bad design decision to force the use of utf8mbr on users who neither need nor want it.

That’s not exactly a WordPress problems, and it would be easier to upgrade MySQL on the server, to be honest.

Well intentioned I’m sure but it’s rather a “slippy-shoulder” response that doesn’t address the realities of life for the average workaday WordPress developer who often get no choice concerning their clients hosting – perhaps you would like to personally contact all hosts and tell them to update their servers?

There was absolutely _no_ need to _force_ this upon users, it _should_ have been an “opt-in” by ticking a box that could be done anytime along with a big warning of the implications for site transportability.

WordPress devs will be doing their users a great service by changing this to an “opt-in” and providing a non-hacky “reversion” for those already affected – not everyone wants to delve into the guts of their database to fix what shouldn’t have been broken in the first place.

Thanks.

WordPress doesn’t require utf8mb4, nor does switching to utf8mb4 cause any problems, so I’m not sure what you’re so upset about.

If you’re moving to a hosting provider who is offering a version of MySQL lower than 5.5.3 (which was released in March of 2010 by the way), perhaps you should consider not moving to that host.

Thanks for your reply, but:

WordPress doesn’t require utf8mb4, nor does switching to utf8mb4 cause any problems, so I’m not sure what you’re so upset about.

I didn’t say that WordPress “requires” utf8mb4 and it is simply your opinion that WordPress forcing an update to utf8mb4 doesn’t cause any problems, which seems to fly in the face of real-world experience by real-world users.

The issue is that once your WordPress site has been forcefully “upgraded” to utf8mbr on a server where MySQL happens to support that you can then no longer migrate that site to another server where the MySQL version does not support that using standard procedures that people have been using for years. You don’t call that a problem, what do you call it then?

And by the way, a “www.ads-software.com Tech Guy” states that you can’t go back from utf8mb4 to utf8 (or do you know otherwise in which case care to share how to do it safely, reliably and 100% guaranteed not to cause any problem under any conditions) and you certainly can’t do it just by changing the table definitions, you already stated that yourself.

If you’re moving to a hosting provider who is offering a version of MySQL lower than 5.5.3 (which was released in March of 2010 by the way), perhaps you should consider not moving to that host.

With all due respect did you actually read what I said? For your average workaday WordPress developer trying to scrape a living they often do not get a choice about their clients hosting. I don’t disagree with you that hosts _should_ have up to date MySQL versions but you know what, they don’t and trolling out the trite “solution” of moving to another host just isn’t a “solution”, it’s just an excuse to try and avoid a issue that has been caused.

So, as I said, do you want to go head and contact all the hosts out there and tell then to get a move on and update their MySQL versions. Or maybe you want to explain this to all those clients of workaday WordPress developers who have what you consider to be “outdated” hosting that that they need to change their hosting because WordPress has forced it upon them for no god reason they will understand.

Oh, and if you are so sure about this why not have www.ads-software.com “force” users into using PHP5.4+ rather than still passively (or even actively) encouraging them to not bother with having to use an up to date and supported PHP version by having WordPress still support PHP5.2? Consistency don’t you think?

So I’m not “upset” about anything (haven’y you ever encountered anyone just putting across an argument/point in a forthright manner) but I don’t know why you are so lackadaisical about something that is clearly a problem that you seem to fail to grasp based on your “misunderstanding” of my post.

Sure enough WordPress is a popular application and that is due in no small part to the dedication of many and it’s popularity gives those in “control” much power _but_ with power comes responsibility and in this case the responsibility is to consider the users and their day to day requirements for the use of WordPress in the real-world – unfortunately they have taken their eye off the ball here and they will be doing a great many people a great service by doing the right thing and fixing the problem as caused and making this “upgrade” optional and providing a “reversion” path for anyone adversely affected thus far.

Thanks.

My WordPress was so old that everything was still latin1. None of the updates converted it. I decided to manually change the charsets to utf8 at least. Described it here: https://blog.markheadrick.com/2015/04/27/blog-updated-to-wordpress-v4-2-1-and-other-changes/

Server does have MySQL 5.5.42-cll though.

The issue is that once your WordPress site has been forcefully “upgraded” to utf8mbr on a server where MySQL happens to support that you can then no longer migrate that site to another server where the MySQL version does not support that using standard procedures that people have been using for years. You don’t call that a problem, what do you call it then?

I consider it more of a problem that the host you’re moving to hasn’t updated MySQL in at least 5 years. MySQL 5.5.3 was released March 24, 2010.

And by the way, a “www.ads-software.com Tech Guy” states that you can’t go back from utf8mb4 to utf8 (or do you know otherwise in which case care to share how to do it safely, reliably and 100% guaranteed not to cause any problem under any conditions) and you certainly can’t do it just by changing the table definitions, you already stated that yourself.

Yes, and as I also already stated, “but that might cause character encoding problems with the existing data.” So, yeah, I know.

With all due respect did you actually read what I said? For your average workaday WordPress developer trying to scrape a living they often do not get a choice about their clients hosting.

I did read. Have you been following along in development? This wasn’t a decision made overnight, and there have been many public discussions in the Make P2s at https://make.www.ads-software.com/ and on Trac.

it’s just an excuse to try and avoid a issue that has been caused.

Not making an excuse, just stating like it is. I have little patience for hosts that let critical software sit un-updated for 5 years.

So, as I said, do you want to go head and contact all the hosts out there and tell then to get a move on and update their MySQL versions.

Nope, I’m sure they’ve been following the news and are either updating MySQL or don’t care.

Or maybe you want to explain this to all those clients of workaday WordPress developers who have what you consider to be “outdated” hosting that that they need to change their hosting because WordPress has forced it upon them for no god reason they will understand.

And, as I keep saying, WordPress didn’t force you to use a higher version of MySQL. If you’re on a version of MySQL below 5.5.3, your collation wasn’t changed.

Oh, and if you are so sure about this why not have www.ads-software.com “force” users into using PHP5.4+ rather than still passively (or even actively) encouraging them to not bother with having to use an up to date and supported PHP version by having WordPress still support PHP5.2?

WordPress will drop support for PHP 5.2 when WordPress needs to. Right now, there is no reason to, and the developers aren’t even incurring the slightest hardship by supporting it.

but I don’t know why you are so lackadaisical about something that is clearly a problem that you seem to fail to grasp based on your “misunderstanding” of my post.

Probably because I’m just volunteering support in my free time, I’m not one of the developers, and I personally don’t see this as that big of an issue. Remember, just because you have a problem, or even a handful other people do, doesn’t mean that everyone else does.

unfortunately they have taken their eye off the ball here and they will be doing a great many people a great service by doing the right thing and fixing the problem as caused and making this “upgrade” optional and providing a “reversion” path for anyone adversely affected thus far.

I assure you, their eyes were quite on the ball for many months. WordPress still requires only MySQL 5.0, that hasn’t changed. If you were running 5.5.3 or higher, you got the new collation, and special characters are inserted as encoded text. If you weren’t, you didn’t, and special characters are inserted as images.

If your hosts insists on using a version of MySQL over 5 years old, I fail to see how that’s a WordPress problem.

So I’m not “upset” about anything (haven’y you ever encountered anyone just putting across an argument/point in a forthright manner)

I agree, “upset” was the wrong word. You seem very passionate about this. Have you considered getting involved in the development of WordPress? https://make.www.ads-software.com/

@james – you are still missing the point which is that WordPress users have had a change forced upon them that is (apparently, as neither you or anyone else seem to have indicated otherwise) not safely reversible.

That change means that the workflow used by people making their living by developing WordPress sites for clients is broken when the development site (with no warning whatsoever) has the database changed (because WordPress considers the conditions to be right on _that_ server) and then the site developer tries to migrate the site to their clients hosting that _does not_ support the database changes in charset/collation and they cannot deliver the site to the client.

Now you can argue until you are blue in the face that all hosts should be on up to date versions of X and Y and Z but, you know what, as I previously stated above, the real world simply doesn’t match such a cosy idealisation as hosts don’t; all have up to date X and Y and Z and not all clients could give a fig, they just want their site delivered.

Your arguments are simply meaningless because they simply based on how you think the world should be but the world just isn’t like that, however much we might all wish it were.

So I say to you again, why don’t you get in contact with all the thousands of hosts out there and tell them they really ought to update their “stuff” because you say they should.

And, as I keep saying, WordPress didn’t force you to use a higher version of MySQL.

You just don’t get it do you – if the server you are on has the right conditions WordPress forces an update to the table charset/collation when the update to 4.2 is made doesn’t it, you cannot possibly deny that that update is forced, the user has _no_ choice, it happens automatically.

From that point on you are _forced_ to use the higher version of MySQL if you want to migrate that site to another server/hosting using the site migration processes that thousands of WordPress users would use and have used for ages. How is that not WordPress forcing you into using a higher version of MySQL? Any information I have seen from WordPress so far says the database “upgrade” is a one-way operation, it cannot be safely undone which means you cannot go to a server/hosting that has an earlier version of MySQL – but according to WordPress requirements all you need is MySQL version 5.0.

So again, all your “ideal world” arguments simply don’t hold water in the real world but it’s good to know that you are not really interested in the problem, in fact you refuse to even acknowledge there is a problem which is very sad.

Oh, and you strangely never responded to the point about this database upgrade being made optional through an “opt-in” check-box or similar. The information from WordPress make sit clear that it can be done at any time, it doesn’t have to be done as an automatic thing on a version update so a simple checkbox to select and save and have the upgrade done if you wanted it would have done the job with no fuss, no mess.

Perhaps you would care to answer how many users actually _need_ this database upgrade and how many might _want_ it and how many would really rather not because of the implications?

Remember, just because you have a problem, or even a handful other people do, doesn’t mean that everyone else does.

Hmm, did I say that “everyone” has a problem, no I didn’t. I have specifically outlined the scenario that will result in some unknown number of people having a problem – but given the huge user base of WordPress do you really think it will be just a “handful”, but at least I seem to care about their problem – you apparently do not because it may only be a “handful” of people. But of course you have no way of knowing that either do you?

If your hosts insists on using a version of MySQL over 5 years old, I fail to see how that’s a WordPress problem.

And again, if it is a version of MySQL that WordPress states is supported and yet a WordPress site cannot be migrated to that host because WordPress made the site incompatible with that host then it _is_ a WordPress problem.

Look at the WordPress requirements page – there is no warning that using a server with MySQL 5.5 or greater will then render your site unmigrateable to a host that supports only an earlier version of MySQL that is all that WordPress claims to require.

I appreciate you are just a volunteer and doing this in your free time but that doesn’t give you the right to be judge and jury over what is or isn’t a problem:

I’m not one of the developers, and I personally don’t see this as that big of an issue

As I say before, it isn’t about what you “personally” think and as you have stated you are not one of the developers so are you really qualified to judge what is and isn’t a problem for all those workaday site developers out there trying to make a living using WordPress? No, your role is to help people where you can and if there is something that you are not sure about or qualified to judge then you refer it on to someone who is – that is support. I’m used to this “blocking” from host supports on bad hosts whose sole job is to simply deny everything and blame someone else – I had hoped for something better here.

I honestly see no point in continuing this conversation as you have obviously decided that as you don’t think there is a problem then there isn’t one – I wonder what it is like to be so omniscient?

Good luck with your future endeavours.

you are still missing the point which is that WordPress users have had a change forced upon them

Here’s some more on that just revealed today: https://poststatus.com/the-trojan-emoji/

Short version: It was a massive security fix two years in the making masquerading as Emoji support to avoid public disclosure.

Oh, and you strangely never responded to the point about this database upgrade being made optional through an “opt-in” check-box or similar.

Ok, I’ll respond now. ??

Personally, I think that’s silly. WordPress doesn’t need *more* options, it needs less.

Your arguments are simply meaningless because they simply based on how you think the world should be but the world just isn’t like that, however much we might all wish it were.

I think we’re both saying the exact same thing. We both have passionate viewpoints that happen to be passionately opposed with one another.

and as you have stated you are not one of the developers so are you really qualified to judge what is and isn’t a problem for all those workaday site developers out there trying to make a living using WordPress?

I’m just about as qualified as you are, and I also make my living using WordPress.

I appreciate you are just a volunteer and doing this in your free time but that doesn’t give you the right to be judge and jury over what is or isn’t a problem.

I’m not being “judge and jury,” I’m just another person stating an opinion, one which happens to disagree with yours.

As I have continually stated, if you want to have a say in the matter, here’s where you go to get involved: https://make.www.ads-software.com/

If you just want to continue a rant, the *support* forums aren’t the place for it.

And on that note, the support value of this thread, which was resolved a week ago by the way, has been completely diminished, so I’ll be closing it down now.