Fix unknown collation utf8mb4_unicode_ci

Do you get an error Unknown collation utf8mb4_unicode_ci while migrating your WordPress database? No worries, this tutorial will explain how lớn fix both utf8mb4_unicode_ci collation và utf8mb4 character set errors.

Bạn đang xem: Fix unknown collation utf8mb4_unicode_ci

Before we see the fix, let’s understand the reason for the error và few snapshots.

Reason for Unknown collation utf8mb4_unicode_ci và utf8mb4 character mix errors

If you export WordPress database from MySQL hệ thống version 5.5.3+ and import into a MySQL hệ thống lower than version 5.5.3, then you are likely khổng lồ see the below errors.

Unknown collation utf8mb4_unicode_ci&Unknown character set: "utf8mb4"Here’s the table structure which uses utf8mb4_unicode_ci collation and utf8mb4 character set.

CREATE TABLE `wp_nfd_termmeta` ( `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `term_id` bigint(20) unsigned NOT NULL default "0", `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci mặc định NULL, `meta_value` longtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`meta_id`), KEY `term_id` (`term_id`), KEY `meta_key` (`meta_key`(191))) ENGINE=MyISAM default CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Note:The above table structure is just one of the table in the exported SQL dump. There might be many tables using these collation và charsets.

Import the SQL dump (exported from MySQL vps version 5.5.3) into MySQL server version source sample_wrdp1.sql;ERROR 1115 (42000): Unknown character phối "utf8mb4"ERROR 1273 : Unknown collation: utf8mb4_unicode_ciSo MySQL version mismatch is the issue? Well, you got it, that’s exactly I was trying khổng lồ explain. Because, the exported SQL dump tệp tin contains references for COLLATION phối to utf8mb4_unicode_ci và CHARSET mix to utf8mb4.

Xem thêm: Hóa Đơn Đóng Dấu Treo Có Hợp Lệ Không ? Hóa Đơn Gtgt Đóng Dấu Treo Có Hợp Lệ Không

So what are the COLLATION và CHARSET supported by MySQL versions lower than 5.5.3?

The MySQL versions Click ExportSelect “Custom – display all possible options” radio button under “Export Method“Scroll down to find “Format-specific” options headingLookout for ‘Database system or older MySQL vps to maximize đầu ra compatibility with:‘ drop down & select “MYSQL40” option.Now click “Go” lớn export.

In command-line:

Use ‘–compatible‘ option for mysqldump command as shown below.

$ mysqldump -u -p --compatible=mysql40 > sample_dump.sqlImport it into a lower version of MySQL and it should work.

Method 2: Edit the exported SQL file và replace collation & charset

You can quickly xuất hiện the exported SQL tệp tin in your favorite editor và replace utf8mb4_unicode_ci to utf8_general_ci & “utf8mb4” to “utf8”.

$vim sample_dump.sqlSearch & replace as shown below:

:g/utf8mb4_unicode_ci/s//utf8_general_ci/g&:g/utf8mb4/s//utf8/gSave the file and try importing.

mysql> source sample_dump.sql
Caution:It’s advised to always migrate your WordPress site to lớn a vps that has the latest of website server and database. The lower versions will always have compatibility and security issues. Moreover, you should STOP using utf8 & USE ONLY utf8mb4. The fix suggested in this tutorial is for those who wanted lớn use lower version of MySQL for some reason

Chuyên mục: Domain Hosting