MYSQL Unknown collation: ‘utf8mb4_unicode_ci’

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”.

Advertisements

One thought on “MYSQL Unknown collation: ‘utf8mb4_unicode_ci’

  1. olshea says:

    you are literally the best im short of words due to my excitement thanks a million times. you steps were very explanatory and easy to follow

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s