MySQL Import error - Unknown collation: 'utf8mb4_unicode_520ci_'

Question

Here is the Error Message I can't import my sql file .

Answer 1

You should change collation utf8mb4_unicode_ci to utf8_general_ci. One way to do this is provided here.

You can also edit the sql file and replace ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci with ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

Answer 2

Before editing .sql file. Have a look at the version between both(backup and restore) of your MySQL. It was a version issue in my case. If you manage to get it on the same version, it solves the issue in most cases. If not then go for other recommended ways.

Answer 3

You need to replace charset "utf8mb4" with "utf8" and COLLATE "utf8mb4_unicode_520ci" in your case with "utf8_general_ci".

You can achieve the replacement in MAC with below sed command. In Linux remove ''.

sed -i '' s/utf8mb4_unicode_520ci/utf8_general_ci/g NEW-CLEANED-DUMP.sql

sed -i '' s/utf8mb4/utf8/g NEW-CLEANED-DUMP.sql

Answer 4

open .sql file and rome

INGINE=InnoDB AUTO_INCREMENT=8193 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

manually and it will work