Converting UTF Characters in MySQL Dump: MySQL to MariaDB Conversion

In environments where MariaDB is the database standard but external developers are using MySQL, importing database dumps can sometimes lead to compatibility issues. One common problem is due to collation differences between MySQL and MariaDB. Specifically, MySQL's utf8mb4_0900_ai_ci collation is not recognized in MariaDB, leading to the following error during import:

Error: Unknown collation: 'utf8mb4_0900_ai_ci'

Solution

The fix for this error is straightforward. To make the MySQL dump compatible with MariaDB, simply open the SQL dump file in a text editor and replace all instances of utf8mb4_0900_ai_ci with utf8mb4_general_ci. The utf8mb4_general_ci collation is compatible with both MySQL and MariaDB, making it a reliable choice for import.

Steps to Fix

1. Open the SQL dump file in a text editor (such as VSCode, Sublime Text, or even Notepad).
2. Use the "Find and Replace" function to search for utf8mb4_0900_ai_ci.
3. Replace each instance with utf8mb4_general_ci.
4. Save the file and re-run the import into your MariaDB database.

This simple adjustment should resolve the import error, allowing the MySQL dump to work seamlessly with MariaDB.

Why Does This Happen?

MySQL introduced the utf8mb4_0900_ai_ci collation in version 8.0, which has features that are not supported in MariaDB. MariaDB uses utf8mb4_general_ci as a general-purpose collation for utf8mb4 data. When MySQL-specific collations are present in a dump, MariaDB does not recognize them, resulting in the import error.