How to Resolve MySQL Error:  Unknown Collation ‘utf8mb4_0900_ai_ci’

If you’ve encountered the error message “MySQL Error Unknown collation: ‘utf8mb4_0900_ai_ci’” while importing a database backup from a server to localhost, don’t worry! This error typically arises due to version mismatches in MySQL, which prevent the database engine from interpreting specific collation or character encoding types. Let’s explore what this error means and how you can fix the error.

Collation refers to the set of rules that dictate how character strings are compared, sorted, and stored in a database. In MySQL, collations play a crucial role in determining how data types like CHAR, VARCHAR, and TEXT handle sorting, case sensitivity, and how characters from different languages are treated. Each collation is associated with a specific character set, which defines the encoding of characters in the database. The error “Unknown collation ‘utf8mb4_0900_ai_ci’” often occurs due to version compatibility issues, where older MySQL versions do not recognize or support the newer collations introduced in later versions, such as. utf8mb4_0900_ai_ci This collation is commonly used in MySQL 8.0 and later, so if you’re working with a previous version of MySQL, it may not be supported, leading to this error. Resolving this issue typically requires updating the MySQL version or modifying the collation to one supported by the database version in use.

How to Resolve MySQL Error:  Unknown Collation ‘utf8mb4_0900_ai_ci’

Fixing the Issue: To resolve this error, edit the database backup file using a text editor. Then replace all occurrences of utf8mb4_0900_ai_ci with utf8mb4_general_ci. Similarly, update CHARSET=utf8mb4 to CHARSET=utf8. For example:

Replace:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

With:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

After editing the backup file as mentioned above, save the backup file and re-import the database. The error should be gone now and the database could be restored successfully without any errors.

Linux users can simplify this process using the sed command. For that use the following command to replace the lines.

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

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

Conclusion

After making these changes, the error “Unknown collation: ‘utf8mb4_0900_ai_ci’” should no longer persist, allowing for a successful database restoration. This adjustment resolves the collation issue and ensures compatibility across different MySQL versions, making the database restoration process smoother and error-free. With the correct collation settings in place, you’ll prevent future conflicts between MySQL versions and maintain optimal database performance.

If you need assistance with how to resolve MySQL Error:  Unknown Collation ‘utf8mb4_0900_ai_ci’ , feel free to contact us.