MYSQL

MYSQL Unknown collation: ‘utf8mb4_unicode_ci’

1 Comment

LogoMYSQLsite

When importing database, if you get an error that says Unknown collation: ‘utf8mb4_unicode_ci’ and the import fails.

MYSQL Version Specification:

  • My local MySQL version is: 5.6.22
  • The version of MySQL on my server is: 5.1.56.

Problem:

Bcoz, older versions of MySQL don’t support utf8mb4 properly. You cannot do hand-wavy things to add that support. They simply cannot support those character sets. Trying to “fix” an export file will simply result in the data becoming corrupted if you use any of those unsupported characters.

If you try to search and replace “utf8mb4_unicode_ci” with “utf8_unicode_ci” in your export file, then that might work. Probably won’t though. And it will definitely result in data loss most of the time.

Solution:

Steps when exporting database:

1) Click the “Export” tab for the database
2) Click the “Custom” radio button
3) Go the section titled “Format-specific options” and change the dropdown for “Database system or older MySQL server to maximize output compatibility with:” from NONE to MYSQL40.
4) Scroll to the bottom and click “GO”.

Steps when importing database:

1) Click the “Import” tab for the database
2) Choose export file
3) Go the section titled “Format-specific options” and change the dropdown for “Database system or older MySQL server to maximize output compatibility with:” from NONE to MYSQL40.
4) Scroll to the bottom and click “GO”.