Unknown collation 1273 error MySQL

Question

I am migrating a wordpress site but when I try to import the database I get the following error:

1273 - Unknown collation: 'utf8mb4_unicode_520_ci'

I think that it is caused because of the different version of MySQL, is that correct? How to fix the code below? This is how every table is created.

CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Welcome to Stack Overflow. Before posting a question, you are expected to put some effort in to solve it yourself. A simple search would have turned up the following question which has a number of potential solutions: [#1273 - Unknown collation: 'utf8mb4_unicode_ci' Cpanel ](https://stackoverflow.com/questions/29916610/1273-unknown-collation-utf8mb4-unicode-ci-cpanel) — FluffyKitten, Aug 21 '17 at 19:24
Possible duplicate of [#1273 - Unknown collation: 'utf8mb4\_unicode\_ci' Cpanel](https://stackoverflow.com/questions/29916610/1273-unknown-collation-utf8mb4-unicode-ci-cpanel) — FluffyKitten, Aug 22 '17 at 01:33

score 4 · Answer 1 · answered Aug 13 '18 at 13:57

4

You can solve this by finding

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

in your .sql file, and swapping it with

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

answered Aug 13 '18 at 13:57

Ashish Odich

573
1
10
25

2

From your answer, I replaced utf8mb4_unicode_520_ci with utf8_general_ci and then utf8mb4 with utf8 and it worked nicely. thanks. – Dr. Aaron Dishno Sep 27 '18 at 15:51
@Dr.AaronDishno Welcome – Ashish Odich Sep 28 '18 at 07:21

score 1 · Answer 2 · edited Jul 16 '19 at 18:41

1

If you are using a Linux server, you can simply use the following command to change SQL file before import it into the database:

sudo sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' your_sql_file.sql

Do not forget to replace your_sql_file.sql with path and name of your own file.

edited Jul 16 '19 at 18:41

Eduardo Baitello

10,469
7
46
74

answered Jul 16 '19 at 17:36

Babak Momeni

31
3

Unknown collation 1273 error MySQL

2 Answers2