New problem with latest MySQL

[RichardParticipant]

Hi,

I’ve come upon a problem I can’t find a fix for, if migrating a WP site from a new MySQL installation to a server with an older MySQL causes a fatal error, and breaks the destination database, unrepairable.

I tried copying the MySQL backup manually instead, and doing a SQL statement into the destination server (1&1 hosting) and get this:
Unknown collation: ‘utf8mb4_unicode_ci

Looking further, it describes the issue here:
https://wordpress.org/support/topic/unknown-collation-utf8mb4_unicode_ci

Is there anything you can do your end on a backup, to create a compatible MySQL copy for older installations for the migration tool in the near future?

[udadminKeymaster]

Hi Richard,

Samuel (Otto’s) answers in that thread are spot on, which is unsurprising since he’s a core WP dev. The UTF8MB4 encoding that is used by WordPress on MySQL servers that support it can encode characters which earlier MySQL servers aren’t *capable* of encoding (i.e. it’s not a question of translating them into something else that it can cope with). UTF8MB4 is a super-set of UTF8 – it contains more characters. So it’s not possible to import a database onto those MySQL servers containing any of those characters. The database will truncate the content, and drop anything that comes after.

The only thing that you can do is restore a UTF8MB4 database onto a MySQL server that supports it. If you’re sure that your database contains no UTF8MB4 characters that aren’t in UTF8 (which would be really hard to know – someone could put them in a comment, or filled form with them), then you can hand-edit the database SQL (UD backups are just SQL) to search/replace utf8mb4 for utf8. Then you can restore. But that’s also risky, and it’d be much better to use a more modern MySQL version. The situation’s not as bad as I think you think it is – it sounds like you’re thimking as if it was a recent MySQL change. But in fact, the last MySQL series to *not* support UTF8MB4 is the MySQL 5.1 series, which was released in 2008. Any more modern series supports it. It’s never a good idea to try to install something on a significantly *older* release than the original came from – that presumes upon forwards compatibility (i.e. that the older ‘something’ was written so as to be compatible with the unknown future, i.e. a psychic programmer) – and this is an example of that (when MySQL 5.1 was released, a need for supporting UTF8MB4 encodings was not anticipated) – even when doing something of this kind *appears* to work, you may have hidden problems that will bite you later.

The latest release of UD did have some code to detect this, and pre-warn before a restore goes ahead – but that seems not to catch every instance of it. So I’ve added a to-do item to try to find out which cases it’s not catching.

David

[RichardParticipant]

Great explanation as ever, thanks David and keep up the amazing work.

[RichardParticipant]

Hopefully you can consider the export to older MySQL versions in later or pro editions, rather like WP Migrate DB Pro includes as a workaround to this problem and seems to work.
I know it’s rare instances this would happen, but I do rely on UpdraftPlus being rock solid as the perfect backup/restore solution.

[udadminKeymaster]

Hi Richard,

What I was saying in my previous explanation (and as Otto says), UFF8MB4 includes characters which *can’t* be encoded in UTF8. In mathematical terms, is a strict true super-set. The only way another tool could be appearing to do it is by silently dropping data that can’t be imported on the destination site, which isn’t an approach we’d want to take (and especially not to sell it as a premium feature!). It gives a false sense of security that something which technically can’t be done, can – but, it really can’t, without crossing fingers and hoping that the user’s database didn’t use any of the un-encodable characters.

David

[Author]

Posts