One can easily get this error but only when moving from a higher mysql version to a lower mysql version. The same doesn't apply in the reverse order if you have a mysql DB of a lower version it will easily get imported to a higher version without any errors. This is also due to the fact that every upgrade made to applications always take care of backward compatibility.
Moving DB from Higher to lower version
For instance exporting a DB from a mysql 8.0 and trying to import it in a mysql 5.7 you get the error as Unknown Collation utf8mb4_0900_ai_ci
Solution
- Take a backup of the mysql file you are going to make changes to.
- Open the mysql (.sql) file in a handy editor which can replace all instances of a string with another for instance Sublime text
- Replace "utf8mb4_0900_ai_ci" with "utf8mb4_general_ci"
- Replace "utf8mb4" with "utf8"
The Linux system users can use the sed command to replace text in files directly :
sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' abc.sql
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' abc.sql
sed -i 's/utf8mb4/utf8/g' abc.sql
Published on