Support » Fixing WordPress » Unknown collation: 'utf8mb4_unicode_ci'

  • mctenold

    (@mctenold)


    I’m going through my typical workflow, building and editing a site locally. Once I’m in a good spot I move the site to a dev server on one of my hosting accounts.

    As far as database, I typically export using MySQL Workbench on a Mac. Then import to my dev server through MySQL Workbench as well.

    Today, I randomly get an error that says Unknown collation: ‘utf8mb4_unicode_ci’ and the import fails.

    My local MySQL version is: 5.6.22

    The version of MySQL on my dev server is: 5.1.56.

    I can’t control the version of MySQL on my dev server, it’s just a shared server at Dreamhost.

    I’ve been reading that this may have something to do with WordPress version 4.2?

    How do I fix this error, or export so that it will be compatible with my WordPress version on my dev server?

    Thanks!

Viewing 14 replies - 31 through 44 (of 44 total)
  • Thread Starter mctenold

    (@mctenold)

    I don’t have any control over my clients hosting plan/server. I can’t control what MySQL version is on my clients production server. So that is not a fix.

    I don’t have any control over my clients hosting plan/server. I can’t control what MySQL version is on my clients production server.

    You could copy/paste that and it would apply to so many of the responses people make in the threads about this subject it’s not even funny.

    Moderator Samuel Wood (Otto)

    (@otto42)

    WordPress.org Admin

    You don’t have control, but you do have a voice. Tell people about the problem, and encourage updating. That’s all any of us can do.

    Thanks for pointing out the obvious and then admitting that it’s not even a real solution. Have anything useful to contribute yet?

    Thread Starter mctenold

    (@mctenold)

    Does this guy actually work for WordPress? If so, this explains everything.

    I don’t thing he really understands the issue.

    Moderator Samuel Wood (Otto)

    (@otto42)

    WordPress.org Admin

    I do understand the issue, completely. However, what you’re not getting is that continuing to use old systems is a security problem.

    Look, I grasp that this makes life harder. Especially if you’re used to migrating databases between systems. But, there are mitigating factors here.

    First, you should find ways to stop doing that. Test and dev systems are not production systems. In my 16 years of experience, I have never used “real” data in a test or dev environment. That’s just not done. Production data should stay in production, period. It’s a simple rule.

    Second, insecure systems are insecure, and like it or not, using the MySQL “utf8” character set causes unusual and difficult to solve security problems. Upgrading fixes those. So, upgrade. I realize that you lack control over versions used. But it’s still true, whether you like that fact or not. Sorry.

    If you feel like discussing this further, you can email me at any time and I’ll happily try to explain it in more detail. My email is otto at wordpress dot org.

    WordPress went to *extreme* lengths to mitigate the issue here. Getting as many people as possible onto safe database schemas is part of that.

    Thread Starter mctenold

    (@mctenold)

    I guess I don’t really get your content argument. I don’t know one person that installs WordPress(as in clicks through the fresh installation process where it creates the database tables) directly on a production server, which is the process your argument suggests.

    I’ve been building websites at ad agencies for 8 years. Nothing ever gets installed directly on the production server. Things get tested on other servers (local or staging) before being moved to production.

    Moderator Samuel Wood (Otto)

    (@otto42)

    WordPress.org Admin

    “Things”, yes. “Content”, no.

    Things are code. Content is actual content in the database. You don’t have the same user accounts and passwords on the test systems. You don’t have the same posts on them either. You stage changes to code, certainly. But not changes to actual posts.

    Copying databases around is a bad idea in general. You back them up, you restore them, you create master/slave systems to expand them. But, you generally don’t set them up somewhere first and write your content before copying them somewhere else. That’s, weird, at best.

    Thread Starter mctenold

    (@mctenold)

    I don’t think I’m crazy for not installing WordPress directly on the production server. I’ve never done that, nor has anyone I’ve ever worked with.

    I think we are going in circles here and we’re going to have to agree to disagree. I just have a very different workflow than you are describing and I don’t think I am the only one.

    Moderator Samuel Wood (Otto)

    (@otto42)

    WordPress.org Admin

    I guess it depends on what you think of as “production”. We run WordPress here, in what I would consider “production”. We don’t write our posts somewhere else first and then copy them onto the website.

    So do millions and millions of other sites. It’s a CMS. It’s supposed to be run on the web, and used to manage your content. Not sure what you’re running in production if you don’t have WordPress on a server talking to the internet in general. That’s sort of the whole point of it.

    Thread Starter mctenold

    (@mctenold)

    Production to me is the client’s server, where the site will be live.

    A lot of times in my work, there’s already a site there, perhaps not WordPress, but there’s already a live production site there that I am updating (design and platform perhaps).

    I don’t think it’s good practice to interrupt that live site with a “maintenance” page, and also someone *could* possibly hit the “Welcome to WordPress” installation page at the exact moment I would be installing WordPress directly on the production server… that to me is bad practice. I’d rather the client have a seamless go live, no maintenance page, no downtime, just an *almost* instant swap to the new site.

    So typically everything happens (and yes, sometimes content entry) off of the production server, so the client can see the “new” site in it’s final state (content and all) before it goes live. In my experience, this is typically on a dev or staging server owned by the ad agency I’m working for. Or when I work for myself, I develop locally, then push to a dev/staging server I own, we sometimes do content entry there, and finalize everything for the client to review at that location, then push to their production server for go-live once everything (content + design) is approved.

    Once client approves, the site gets moved to production – aka the entire wordpress filesystem gets moved (because they might not have had WordPress before) and then the database gets migrated to the production server. This is where the issue lies. Since we aren’t developing directly on the production server, nor are we installing directly on the production server, a compatibility issue arises. Because locally/dev/staging I did have a higher version of MySQL, but my clients production server did not.

    This is my typical workflow, and the workflow I’ve seen in my 8 years of building websites.

    Moderator Samuel Wood (Otto)

    (@otto42)

    WordPress.org Admin

    That’s fair enough, and I don’t disagree with any particular part of it.

    So, your test or development or staging system should be as close to the actual system as possible. Meaning: Install the same version of MySQL that they have on your own system and use that.

    Yes, you may not have had to do that before. Well, that’s not the case anymore. Sorry. WordPress now behaves differently for different versions of MySQL, in the same way that it behaves differently on different versions of PHP. That’s really not going to change.

    DrProtocols

    (@drprotocols)

    @otto – I really think that you and WordPress.org in general are _still_ missing the point.

    Perhaps an analogy might help you. Suppose you were working on an important document for a client and you were both using the same application and version and you could exchange the document back and forth no problem. Then the application developers brought out a new version that you updated to and carried on writing the document and then sent it to your client and they couldn’t open it because your updated version of the application had (without notice, warning or choice) “upgraded” the document format so it was no longer compatible with the older application version – anyone wants to share that document _requires_ the updated application version. No problem you would say, I’ll just tell my client to update – but hey, you know they don;t want to pay for the update and anyway they have other concerns as they need to maintain compatibility with all their other stuff and they only do updates when _they_ need/want to and not just because _you_ say they should, and by the way, where is that document, you were supposed to be delivering it today… No problem you say, I’ll just contact the application developers and ask them how I can revert to the previous format…oh, what’s that, they say it’s not possible, sorry, you’ll just have to get your client to update won’t you…

    You can argue all you like about how things _should_ be and I’m sure everyone would agree with you more or less. Sure, what has been done has been done with the best of intentions _but_ the real issue is _how_ it has been done and how are WordPress.org going to help al those users that they have caused a problem for and will continue to cause a problem for – or does WordPress.org simply not care about a bit of “collateral damage” because their intentions are well meaning?

    This “problem” involving the use of utf8 has been known about for years (by WordPress.org), yes? Some related “fixes” were put in earlier versions of WordPress, yes? Additional issues were identified last year, right? A decision was made that changing to use utf8mb4 would aid in “fixing” those issues, right? The fact that this would now allow support for Unicode versions like 5.0 that would help _some_ users was a useful byproduct, yes?

    So the issue is _why_ was such a radical change not better handled and better communicated to give users an opportunity to be able to _plan_ what they would need to do, to be able to _plan_ moving to new hosting or trying to persuade their clients to move, to be able to _plan_ changes to their workflows and tools that would be required?

    The release announcement: https://wordpress.org/news/2015/04/powell/ has absolutely nothing that would indicate any possible issue or problem concerning MySQL version compatibility – so does this mean none of the assembled brains even gave it a thought, or did they think about it and just decide it didn’t matter and it would teach al those “amateur” developers out there a lesson, or what?

    Whilst it’s great that you have your tools and workflows and perfect hosting, that doesn’t mean that your way is the one true way or the right way – others have been doing just fine with their tools and workflows for years, for you to simply say they are “wrong” with just an insouciant shrug of the shoulders is just insulting to all those workaday site developers out there just struggling to make a living out of WordPress. They don’t all have the time to be “Tech Guys” like yourself and understand all the ins-and-out of how things “should” be done in your opinion, they are just trying to make a living.

    To just suddenly say that such a “non-tech” developer (and surely isn’t it a “selling” point of WordPress that you don’t have to be a “tech” to use it and develop sites with it) should just install the same version of MySQL as on the target system is just a naive suggestion from someone sitting in an ivory tower.

    In any case this “helpful” suggestion does not help all of those who have already been force upgraded to _require_ MySQL 5.5+ for the site they have been developing for a client and now find it is incompatible with the client hosting. And what about all those who still have no idea about this issue and will be similarly updating or starting client development sites on their dev systems only to eventually find then incompatible with their client hosting. Oh, by the way, try convincing a client who has paid up their hosting for a year that they should just write off that and move to a new host just because you say so.

    And as for this being an important security issue and so everyone must just accept it – if it is so important why is it that WordPress still only requires MySQL5.0 and 5.5.3 is only recommended – surely such an important security issue should mean that 5.5.3 was required?

    We’ve been living with utf8 for years and the world hasn’t collapsed has it? I think the world would have survived a little longer without having nanny tell it what is best for it and force the cod-liver oil down its throat.

    As for MySQL 5.5+ providing support for Unicode 5.0+ as opposed to 5.1 being only Unicode 3.0 – well that’s all great but how many users actually _need_ that so desperately that it has to be forced upon users unknowingly. I’m sure such users will be happy but at the same time would they mind if everyone was given a choice, at least initially? Whatever happened to deprecation followed later by removal? I don’t ever remember seeing pre-MySQL 5.5 being deprecated and notice given that support for older MySQL versions would be going away at some point under certain stated conditions?

    I thought WordPress always made a big thing about being a “community” – in fact on the WordPress.org home page it states “we’d love you to join the family.” – well guess what, it just kicked some of its family members in the teeth and can’t even be bothered to say sorry let alone help them to the dentist to get them fixed.

    So please, WordPress.org, think of _all_ your “family”, don’t hide behind ill-though out excuses, just admit you didn’t give things proper consideration, you didn’t respect your users and their needs, but that you’ll change things so that there will be a choice whether the upgrade will be made for some period of time that will be communicated so as to give users a chance to plan to accommodate it _and_ that you will also provide a means for sites that have already been force upgraded against the requirements of the users to “downgrade” back to utf8 until such a time as the users are _ready_ to upgrade.

    No one minds you got the approach wrong, everyone makes mistakes, the strength of commitment comes in how you acknowledge and repair the damage caused and I’m sure WordPress.org is big enough to come through for its disenfranchised family members in this respect.

    And before anyone “dismisses’ this as a “rant” – as one unfortunate WordPress forum mod did with similar comments elsewhere I can assure you I am perfectly calm and rational as is all the text above 🙂

    Ok, now I’m waiting for the next set of excuses and put-downs and clever rejoinders to be rolled out in order to try and humiliate and intimidate me. Or maybe you’ll just ignore this altogether showing an equal level of contempt. Honestly I don’t really care as it would all be bluster over substance. Why not prove me wrong instead and do the right thing.

    Regards

    Moderator Samuel Wood (Otto)

    (@otto42)

    WordPress.org Admin

    Clearly, this thread no longer is accomplishing anything of value here.

    This is a support forum. Support has been provided. If all you want to do is complain about the issue, then this is the wrong place to do it.

    The change to convert to utf8mb4 has been public and in the trunk code for 3 months now. The ticket discussing it is 3 years old, with 90 comments and 13 different patches. This isn’t something that was done capriciously, or spur of the moment. It was done intentionally, and it was done for very good reasons.

    While you may have been inconvenienced by this change, that doesn’t make it a bad change. In my opinion, it’s a good change, and your reaction should be to upgrade things properly, instead of allowing your clients to continue to rely on a 12 year old database system.

    Regardless of your opinions, upgrades and updates will continue to happen. Support for the latest and greatest technologies will continue to be added to WordPress, and sometimes, those cannot be ported backwards in time. We can’t make it work with everything, and I personally would be just fine to see the minimum requirements bumped to MySQL 5.5, if it wouldn’t leave a lot of users out in the cold. We’ll continue to support those users, but that doesn’t mean we won’t also take advantage of new technologies as well.

    So, for now, WordPress works on MySQL 5.0 systems, as it says on the requirements page. You’ll note that we also recommend 5.5 or greater. This is why.

Viewing 14 replies - 31 through 44 (of 44 total)
  • The topic ‘Unknown collation: 'utf8mb4_unicode_ci'’ is closed to new replies.