15 49.0138 8.38624 1 0 4000 1 https://www.techpulsetoday.com 300

How to fix the Unknown collation: ‘utf8mb4_unicode_ci’ error

utf8 values are stored as 3 bytes, where utf8mb4 are stored as 4 bytes. This new encoding type was introduced in MySQL version 5.5.3. This can cause some issues if you’re attempting to migrate from 5.5.3+ to an older database.

The utf8mb4 Upgrade:

Since WordPress 4.2, they are upgrading tables to utf8mb4
Your site will only upgrade when the following conditions are met:

  1. You’re currently using the utf8 character set.
  2. Your MySQL server is version 5.5.3 or higher (including all 10.x versions of MariaDB).
  3. Your MySQL client libraries are version 5.5.3 or higher. If you’re using mysqlnd, 5.0.9 or higher.

Method 1:

Log in to your database server using phpMyAdmin.

If you export normally, you’ll most likely get the error message of Unknown collation: ‘utf8mb4_unicode_ci’ when importing into an older MySQL database.

To export to older MySQL databases, follow these few steps:

  1. Make sure you select your database and go to the “Export” tab
  2. Select the “Custom” radio button
  3. Go the section “Format-specific options” and in the setting for “Database system or older MySQL server to maximize output compatibility with:” select MYSQL40.
  4. Scroll to the bottom and click GO.



Method 2:

For a command line export using mysqldump.

mysqldump -u root -p --compatible=mysql4 techpulsetoday > techpulsetoday.sql
sed -i 's/TYPE=InnoDB/ENGINE=InnoDB/g' techpulsetoday.sql

then restore techpulsetoday.sql

mysql -u root -p techpulsetoday < techpulsetoday.sql

Method 3:

Search and Replace through Terminal.

utf8_unicode_ci to utf8_general_ci (or) utf8mb4_unicode_ci to utf8mb4_general_ci

sed -i 's/utf8mb4/utf8/g' techpulsetoday.sql
sed -i 's/utf8_unicode_ci/utf8_general_ci/g' techpulsetoday.sql
sed -i 's/utf8_unicode_520_ci/utf8_general_ci/g' techpulsetoday.sql

then restore techpulsetoday.sql

mysql -u root -p techpulsetoday < techpulsetoday.sql

Important Note: Converting from utf8mb4 to utf8 may cause loss of data. It is advisable to take a full database backup, before carrying out the steps below and inspect your site in full after the changes, to ensure there is no corruption.

Google Search from Linux Terminal (Googler)
Adding the time stamp to the History Command

Thanks for stopping by guys! I'm Vijayan and Techpulse is my beloved brainchild. Currently working as a PHP developer in a digital marketing start-up, I'm overly passionate about not just learning new things but also putting those into practice. I swear by a quote I once came across... 'What separates successful people from unsuccessful people is the former's ability to execute'. Feel free to reach out to me if you have any questions, suggestions or feedback. Hoping to see more of you here!

0 Comment


This site uses Akismet to reduce spam. Learn how your comment data is processed.