installer broken – check collation capability incorrect

Hey @nbi1

Thanks for the feedback!

That is a bit odd, we have never had this reported before. The collation check actually happens at build time and doesn’t correlate to the SQL file directly but from the active tables in the database. The check to gather the collations is from the following query:

SHOW TABLE STATUS FROM {$wpdb->dbname}

That list is then stored in the installer and then validated against the destination server’s collations at install time. I’m not 100% sure as to the root of the issue, but if you would like to submit a support ticket for this issue we can have a closer look at your setup. Please provide a link to this forum thread when submitting the ticket.

Thanks~

My guess is that the bug consists of just looking for ‘utf8’ to infer which collations are needed. This wouldn’t be a problem with older versions of MariaDB (pre 10.6.4 ?) because these older server versions still included utf8/utf8_general_ci. So even if utf8 is erroneously flagged as needed it’ll still get past the installer because the server supports it. But the newer versions (10.6.4+) do not so the bug is exposed. Googling shows numerous reports about the MariaDB utf8 problem, this is typical: https://www.mail-archive.com/[email protected]/msg179329.html. If my guess is correct the fix should be simple – don’t look for ‘utf8’, look for ‘utf8mb4’.

Sorry, didn’t see your response when I posted – disregard my last post. Based on your response I have a theory on what might be going on, but I’ll need to do some testing to confirm.

Figured it out. My instincts about an incorrect check were on target – the installer is indeed performing an invalid check.

During the backup of the original you execute the above query to get the list of collations. That’s fine. But when you store the list in the installer you store utf8_general_ci when in fact utf8_general_ci does not appear in the query result! Why? Because you find utf8mb3_general_ci which is an alias for utf8_general_ci and you unalias before storing the list. So at the restore end of the operation the installer consults the list which includes utf8_general_ci and the validation fails because it’s not found in the server collation list. But it is there – as the alias utf8mb3_general_ci ! Actually the installer hints at this problem with its output – it should not be showing utf8_general_ci it should show utf8mb3_general_ci because the aliased form is used in newer versions of MariaDB (I didn’t know that until I researched this issue).

So the fix consists of 2 checks when backing up:
1. If the collation query returns utf8mb3_general_ci store that – do not store the unaliased utf8_general_ci.
2. If the collation query returns utf8_general_ci (old MariaDB) store the alias utf8mb3_general_ci instead.

These checks will ensure that with a newer (older doesn’t make sense) MariaDB the installer will properly try to match utf8mb3_general_ci in its list against utf8mb3_general_ci in the server collation list.

This fix will solve the problem for anyone going through the backup/restore cycle for the first time. It won’t help those like me who already have an installer backup set with utf8_general_ci instead of utf8mb3_general_ci. Couple of possible solutions for that: 1. a standalone tool/script that fixes the installer’s collation list, or 2. add installer option to handle this case (can’t just omit this validation because if utf8mb3_general_ci is missing from the server then there is a real problem). Manually hacking the installer list would probably work if I knew where it was buried, but I’m sure you don’t want the headaches resulting from asking users to do this.

Hey @nbi1,

Thanks for the feedback! I think you should be able to fix the issue on your end just open up this file /dup-installer/dup-archive__[hash].txt after you have browsed to the installer.php file.

At the top of the JSON structure you should see something like this:

{
    "created": "2022-03-01 01:06:56",
    "version_dup": "1.4.5",
    "version_wp": "5.9.1",
    "version_db": "10.4.14",
    "version_php": "7.4.9",
    "version_os": "WINNT",
    "dbInfo": {
        "buildMode": "MYSQLDUMP",
        "collationList": [
            "utf8mb4_unicode_520_ci",
            "latin1_swedish_ci",
            "utf8_unicode_ci",
            "utf8mb4_general_ci",
            "utf8mb4_unicode_ci",
            "utf8_general_ci"
        ],

In the “collationList” you should be able to add/update/change the collation list to get you past the issue temporarily. You can also do this before the archive.zip file is extracted as well. We’ll put this in our backlog to look into a fix for it.

Thanks

Thanks, that’s what I was looking for. I’ll give that a try. I opened a ticket just to make sure this issue gets tracked. I think the fixes I proposed will work just fine – I assumed it’s just a matter of constructing a correct collation list when the archive is built and your suggested workaround indicates that this is the case.

• This reply was modified 3 years ago by nbi1.

Your workaround solves the problem, thank you. Now I’ve got other issues to deal with, but at least the collation check isn’t derailing the process.

ok thxs for the update!

I’ve just encountered this issue when trying to migrate from and Older Host to Newer host. The increment in MariaDB to 10.6.x throws a spanner in the works by disallowing ut8. Really hope you can get an update out to patch this as the manual method suggested above isn’t working for our case.

Old Hosting Version MariaDB 10.5.15-MariaDB-cll-lve

New Hosting Version MariaDB 10.6.7-MariaDB

Duplicator Error thrown in import:-

“STATUS

character set and collation isn’t supported on current database. “Legacy Character set” and “Legacy Collation” will be replaced with default values.
DETAILS

This test checks to make sure this database can support the character set and collations found in the dup-installer/dup-database__[HASH].sql script.
Character set list
utf8mb4
utf8
Collations list
utf8_general_ci
utf8mb4_unicode_ci

The database where the package was created has a character set and collation that is not supported on this server. This issue happens when a site is moved from an newer version of MySQL to a older version of MySQL. The recommended fix is to update MySQL on this server to support the character set that is failing below. If this is not an option for your host, then you can continue the installation. Invalid values will be replaced with the default values. For more details about this issue and other details regarding this issue see the FAQ link below.

Default charset and setting in current installation
DB_CHARSET = utf8mb4
DB_COLLATE = utf8mb4_general_ci`”

I’m also having this problem.
I have created a new install on a new host, ready to deploy my Duplicator Pro package.
But like the others above, I am being warned that utf8 is not available.
Of course, what I have done is created a database on the new standard as recommended.
When I did this previously, the migration slowed down to a crawl, possibly due to charset alias converting?

This test checks to make sure this database can support the character set and collations found in the dup-installer/dup-database__[HASH].sql script.

Character set list
utf8mb4	
latin1	
utf8	
Collations list
latin1_swedish_ci	
utf8_bin	
utf8_general_ci	
utf8_unicode_ci	
utf8mb4_general_ci	
utf8mb4_unicode_520_ci	
utf8mb4_unicode_ci	
The database where the package was created has a character set that is not supported on this server. This issue happens when a site is moved from an newer version of MySQL to a older version of MySQL. The recommended fix is to update MySQL on this server to support the character set that is failing below. If this is not an option for your host, then you can continue the installation. Invalid values will be replaced with the default values. For more details about this issue and other details regarding this issue see the FAQ link below.

Default charset and setting in current installation
DB_CHARSET = utf8mb4
DB_COLLATE = utf8mb4_0900_ai_ci

I’d be really happy to hear what’s going on – really don’t want to screw up this migration
Thanks!

I ended up having to manually convert on our old hosting before migrating away to the newer host. It just seems to have been one of those issues that hadn’t garnered much attention from many users unless you were doing specific things with your database.

The basic process is here:

https://mariushosting.com/phpmyadmin-how-to-convert-utf8_general_ci-database-to-utf8mb4_unicode_ci/