Issue importing WordPress database from development to live server: 1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

Issue importing WordPress database from development to live server: 1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

Issue importing WordPress database from development to live server: 1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

So I was trying to import a MySQL database exported from local development machine to a live server and I got the error thrown by phpMyAdmin:

#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

On some systems, the error could be: Unknown collation: 'utf8mb4_unicode_ci'

 
The error in detail:
CREATE TABLE wp_commentmeta (
meta_id bigint(20) UNSIGNED NOT NULL,
comment_id bigint(20) UNSIGNED NOT NULL DEFAULT ‘0’,
meta_key varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
meta_value longtext COLLATE utf8mb4_unicode_520_ci
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
MySQL said: Documentation
#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

Cause of the error:
This error is normally caused by a difference in encoding types between the source database and the destination database. It usually happens when a database is exported from a newer MySQL database (MySQL 5.5.3 and above) which uses utf8mb4, and attempt to import into an older MySQL version using utf8

Solution that worked:

On the local machine:
On the local development machine, I accessed phpMyAdmin and exported the database using the following settings:

  • On local development machine, access phpMyAdmin
  • Select the desired database and click Export
  • Instead of the default, Select  “Custom – display all possible options”
  • Scroll down
  • Look for “Database system or older MySQL server to maximize output compatibility with” and in the dropdown next to it, select MYSQL40 
  • Click Export
wordpress-database-import-error-older-mysql

The SQL file is exported from the local MySql database to the computer.

On the live server:

  • Access the phpMyAdmin on the live server
  • Click Import and select the sql file exported earlier on the local machine.

The database from the local machin was imported successfully without the error #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’.

Hope this solution is helpful to you. 

Leave a Reply