When importing a database from MySQL8 server to MariaDB the following error is displayed:
Error: Unknown collation utf8mb4_0900_ai_ci
What is the meaning of the MySQL collation utf8mb4_0900_ai_ci?
utf8mb4 | each character is stored as a maximum of 4 bytes in the UTF-8 encoding scheme |
0900 | Unicode Collation Algorithm version (a method used to compare two Unicode strings that conform to the requirements of the Unicode Standard) |
ai | accent insensitivity – no difference between e, è, é, ê and ë when sorting |
ci | case insensitivity – no difference between c and C when sorting |
The Solution
Use utf8mb4_unicode_520_ci instead of utf8mb4_0900_ai_ci, or you may even use uca1400_as_ci if you have a newer version of MariaDB. Don’t use any of the “general_ci” variants, as they do not correctly sort or compare according to any version of Unicode and have been superseded decades ago.
Quick Fix
To fix this simply run a search and replace inside the dump file and replace all instances of utf8mb4_0900_ai_ci with utf8mb4_general_ci
Source: Monolune – What is the utf8mb4_0900_ai_ci collation?
