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?
each character is stored as a maximum of 4 bytes in the UTF-8 encoding scheme|
Unicode Collation Algorithm version (a method used to compare two Unicode strings that conform to the requirements of the Unicode Standard)|
accent insensitivity – no difference between e, è, é, ê and ë when sorting|
case insensitivity – no difference between c and C when sorting|
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.
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