Migrating to MariaDB "ERROR 1273 (HY000) Unknown collation: 'utf8mb4_0900_ai_ci'"

[clubpromos]

Hi,

I am attempting to migrating from MySQL 8.0.26 to the latest MariaDB.

1. I dump my database with "mysqldump --no-tablespaces --default-character-set=utf8" or "mysqldump --no-tablespaces --default-character-set=utf8mb4", this doesn't seem to make a difference. In the past I've dumped and restored without any issues from MySQL to MySQL.
2. Now, when I import my database into MariaDB, it throws an error "ERROR 1273 (HY000) Unknown collation: 'utf8mb4_0900_ai_ci"

My board is fairly large, the language is French so it includes characters like é or à. Because it's so large I can't manually check everything so I'm trying to make sure I get it right.

Does anybody know how to can fix this error?

 

[clubpromos]

Bumping this a little. I hope that someone has a solution because nothing that I do seems to fix it.

 

[Masetrix]

(Resolved) Unknown collation: utf8mb4_0900_ai_ci – TecAdmin

The collation utf8mb4_0900_ai_ci is a character set collation for MySQL databases, introduced in MySQL 8.0.1. It is based on the Unicode Collation Algorithm (UCA) 9.0.0, and the character set is utf8mb4, which supports a wide range of Unicode characters. The “ai” in the collation name stands for...

tecadmin.net

MariaDB versus MySQL - Compatibility

Compatibility and differences with MariaDB related to high availability, security and scalability. Learn about the comparisons of MariaDB versus MySQL.

mariadb.com

Character Sets​

• The utf8 character set (and related collations) is now by default an alias for utf8mb3 rather than the other way around. It can be set to imply utf8mb4 by changing the value of the old_mode system variable (MDEV-8334)

Changes and Improvements in MariaDB 10.6

Current Version: 10.6.17 | Status: Stable (GA) | Release Date: 7 Feb 2024

mariadb.com

Hth...

 

[clubpromos]

Thank you @Masetrix for trying to help.

So, I first did the:

sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' backup.sql

Which throws the error "

ERROR 1253 (42000) at line 195: COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'"

Ok, so I moved on to do:

sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql

This should have worked, but no, now I get "

ERROR 1253 (42000) at line 6339: COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'"

This makes no sense to me anymore. One time it's utf8mb4', the other it's utf8.

I really wanted to take advantage of a new stack with MariaDB. All works well for my other app, but not for Xenforo

 

[Masetrix]

Try this..

sed -i 's/CHARSET=utf8mb4/CHARSET=utf8mb4_general_ci/g' backup.sql

I don't use MySQL8 and have already migrated to MariaDB 10.4.x with MySQL 5.7.

There are no of these problems because MySQL 8 and MariaDB are binary incompatible.
With MySQL 5.7 and MariaDB it was enough to simply replace MySQL5.7 with MariaDB 10.4.21 and everything went as before