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


Active member

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?

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)

Last edited:
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 :(
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 :)
Last edited:
Top Bottom