unknow collation: ‘utf8mb4_0900_ai_ci’

Eduardo Carrasco You may want to start by reviewing the instructions and information in this Dev Blog post:  MySQL8 Collation Conversion Made Easy: A Pre-Upgrade Guide for Sugar 25.1. I hope this helps!

Send next error when run script: "PHP Fatal error:  Uncaught mysqli_sql_exception: Unknown collation: 'utf8mb4_0900_ai_ci' in C:\xampp\htdocs\tactos\include\database\MysqliManager.php:437"

Just to confirm from Alex Nassi message did you upgrade you local MySQL to v8 prior to running that script? I ran into the same issue as you and I had to backup all my development instances, fully uninstall the previous MySQL and install v8.0.41. I should also point out that if you're installing using brew make sure to specify 8.0 (mysql@8.0) as it will by default install 9.2 which isn't supported.

I have 8.2.12 version of MySQL without Brew

Perfect! If you're restoring version 14.2 to MySQL 8.2, you won’t need to run the script since MySQL 8.2 supports utf8mb4_0900_ai_ci. Did the original error occur when you attempted to restore the 14.2 database to the upgraded MySQL?

Yes

That’s very odd since MySQL 8.2 fully supports utf8mb4_0900_ai_ci. As long as you're restoring the same original .sql files from your backup, it should work without issues.

The only problem I ran into after upgrading was ensuring that nothing from MySQL 5.7 was still running. Have you verified this by running mysql -v to confirm that MySQL 8.2 is actually active?

I know Sugar officially supports MySQL 8.0.31, but I can’t see why 8.2 would have this issue. I’m currently running 8.0.41 and have successfully restored multiple client instances without any problems.

One thing had to do was fully uninstall MySQL 5.7, flushing out any lingering my.cnf files, and ensuring a clean install of MySQL 8.0.41. Leftover configuration files caused issues for me during my upgrade. I also backed up all the databases I had, flushed out the database folders before installing a fresh MySQL.

I just get the feeling that something is lingering as you shouldn't be have an issue with 8.2 and utf8mb4_0900_ai_ci.

Can you try running

SHOW COLLATION LIKE 'utf8mb4_0900_ai_ci';

also 

SHOW CHARACTER SET LIKE 'utf8mb4';

Here is an image of mine

share.cleanshot.com/6b8YlRKB

The version is correct 8.2 of MySQL

SHOW COLLATION LIKE 'utf8mb4_0900_ai_ci'; no return records and 

SHOW CHARACTER SET LIKE 'utf8mb4'; shows the following:

Hey, 

Can you confirm this was an upgrade you did? I am asking as I am getting the feeling your system is using legacy collation settings from an older version.

If you run SHOW COLLATION WHERE Charset = 'utf8mb4'; and utf8mb4_0900_ai_ci is missing then there is good chances your missing other collations needed.

If you run SELECT @@collation_database, @@collation_server; and if it returns utf8mb4_general_ci try changing them using

SET GLOBAL collation_server = 'utf8mb4_0900_ai_ci';

SET GLOBAL character_set_server = 'utf8mb4';

Then restart MySQL

There is more you could do after this by checking your my.cnf or my.ini BUT personally I would consider uninstall, remove the my.cnf/ini and doing a clean re-install.

Hey, 

Can you confirm this was an upgrade you did? I am asking as I am getting the feeling your system is using legacy collation settings from an older version.

If you run SHOW COLLATION WHERE Charset = 'utf8mb4'; and utf8mb4_0900_ai_ci is missing then there is good chances your missing other collations needed.

If you run SELECT @@collation_database, @@collation_server; and if it returns utf8mb4_general_ci try changing them using

SET GLOBAL collation_server = 'utf8mb4_0900_ai_ci';

SET GLOBAL character_set_server = 'utf8mb4';

Then restart MySQL

There is more you could do after this by checking your my.cnf or my.ini BUT personally I would consider uninstall, remove the my.cnf/ini and doing a clean re-install.

When run SET GLOBAL collation_server = 'utf8mb4_0900_ai_ci'; send next Error Code: 1273. Unknown collation: 'utf8mb4_0900_ai_ci'

Ok, if it's me I am uninstalling, making sure the my.cnf/ini are gone and then re-installing. What are you installing on? Windows or Linux/Ubuntu?

What does SHOW VARIABLES LIKE "version_comment"; show you?

Kortnee Wilson it seems like Eduardo Carrasco 's MySQL 8.2 has been upgraded in place or something along those lines. I did install MySQL 8.2 (through docker) and it does have that collation and I managed to import a SugarCloud cloud 14.2 backup to my local environment without issues.

My suggestion is to Eduardo Carrasco work with his DBA team to have that collation installed (if that's a thing) or simply use containers for this by using our SugarDockerized as his local development environment.

It's new install on windows

SHOW VARIABLES LIKE "version_comment";

Hey Eduardo,

That explains it! MariaDB does not support utf8mb4_0900_ai_ci, which is why you're getting the "Unknown collation" error. Even though you expected MySQL 8.2, you're actually running MariaDB, which lacks this specific collation because it does not follow MySQL’s Unicode collation updates.

Since MariaDB and MySQL are separate projects, the only way to get utf8mb4_0900_ai_ci is to remove MariaDB and install official MySQL.

Just to add to my post I do suggest 8.0.41 and here is a link to the installer on MySQL's website - https://dev.mysql.com/downloads/installer/

When installing make sure the Character Set is utf8mb4 as the default.

we do not support MariaDB unfortunately, as I mentioned earlier, just go with SugarDockerized or install MySQL 8.2.