ERROR 1273 (HY000): Unknown collation: ‘utf8mb4_0900_ai_ci’

This is a MySQL database error that we got in when we were trying to populate a database using a SQL dump file. In our case, we took the dump from AWS RDS prod instance and wanted to populate our staging environment database.

When we ran the following command, we got the issue:

> mysql -u user -p -D databasename < dump.sql 
// ERROR 1273 (HY000) at line 522: Unknown collation: 'utf8mb4_0900_ai_ci'

Solution of the issue:

The SQL dump we took from the production server had the new version of MySQL. The MySQL version was 5.6. Our staging server MySQL version was 5.5.

MySQL 5.5 does not support utf8mb4_0900_ai_ci. But it supports utf8mb4_unicode_ci.

We had to open the file and replace this utf8mb4_0900_ai_ci with utf8mb4_unicode_ci

utf8mb4_0900_ai_ci ===> utf8mb4_unicode_ci

// Here are vi commands if we want to do it using vi editor
$ vi dump.sql

// Search and replace using vi editor 
:%s/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g