Working on an Azure server I ran a mysqldump command to generate a copy of the db. No issues, the file downloaded on to the serve without cause.
The Problem
On my local environment, using Sequel Ace I imported the file, but it suddenly it stopped with a red message “Unknown collation: ‘utf8mb4_0900_ai_ci‘”
[ERROR in query 14] Unknown collation: 'utf8mb4_0900_ai_ci'
“Collation is the assembly of written information into a standard order. Many systems of collation are based on numerical order or alphabetical order, or extensions and combinations thereof. Collation is a fundamental element of most office filing systems, library catalogs, and reference books.”
It is basically a technique of encoding the information into a database. This is mainly caused due to the version difference and supports the inability of the database engine to render data encoding type.
Solution
Of course there are a couple of ways to resolve this issue:
Via shell
This can resolved by navigating to the directory where you have the mysqldump.sql file and then running the command
sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' mysqldump.sql
Note there is a slight difference in sed command parameters under MAC/Linux.
For Mac:
sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' mysqldump.sql
For Linux/Ubuntu:
sed -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' -i mysqldump.sql
Via text editor
Using shell isn't for you, this issue can also be resolved by opening the file in a program like Notes, Sublime Text or any other vanilla text editor. Then I ran a find and replace the string “utf8mb4_0900_ai_ci” and replaced it with “utf8mb4_general_ci“.
Yay imported successfully!