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
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.
Hope this solution is helpful to you.
Excellent tips. Really useful stuff .Never had an idea about this, will look for more of such informative posts from your side... Good job...Keep it up
ReplyDeletewordpress