Starting with version 4.2 WordPress has changed the database character set from utf8_general_ci to utf8mb4_unicode_ci (which was necessary, for example, for full emoji support) if a few requirements have been met. In this article I will teach you how to manually make the switch from utf8_general_ci to utf8mb4_unicode_ci using phpMyAdmin if you did not meet the requirements at the time of the WordPress 4.2 upgrade. Character sets from the utf8_general_ci group allow the storing of a maximum of 3-bytes long characters. Character sets from the utf8mb4_unicode_ci group allow the storing of a maximum of 4-bytes long characters. You can convert utf8_general_ci database to utf8mb4_unicode_ci if you’re currently using the utf8_general_ci character set.
What is Collation? In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.
STEP 1
Please Support My work by Making a Donation.
STEP 2
Back up your WordPress database. Read more on how to manually back up your WordPress database and how to back up your WordPress configurations using Hyper Backup.
STEP 3
Go to your WordPress folder and open your wp-config.php file using Text Editor. Find this line: define(‘DB_CHARSET’, ‘utf8′);
Change the utf8 text to utf8mb4 then save the file. If utf8mb4 is already set in your wp-config.php continue with STEP 4. Follow the instructions in the image below.
STEP 4
Log into your phpMyAdmin using your MariaDB 10 root password. Select your database and check if the latest Collation is set to utf8_general_ci. As you can see in the image below, WordPress correctly uses utf8mb4_unicode_ci but the database was created some time ago using utf8_general_ci Collation. To correct this issue just follow the next STEP. Follow the instructions in the image below.
STEP 5
In phpMyAdmin select your WordPress database, then click the Operations tab. Scroll down the page a little bit until you find the Collation option. From the drop-down menu select utf8mb4_unicode_ci instead of utf8_general_ci and check Change all tables collations and Change all tables columns collations, then click Go. Follow the instructions in the image below.
STEP 6
If everything goes well, you will see the following message: Your SQL query has been executed successfully. Follow the instructions in the image below.
STEP 7
If you go back to your database, you can see that the database has been correctly changed from utf8_general_ci to utf8mb4_unicode_ci which is the same collation used by WordPress. Follow the instructions in the image below.
STEP 8
The next time you want to create a database using phpMyAdmin to be used exclusively with WordPress, remember to create it by choosing utf8mb4_unicode_ci from the drop-down menu instead of the first option utf8_general_ci just to avoid changing it later like I did with mariushosting. Follow the instructions in the image below.
Note: In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.
Note: By changing the database Collation with the same Collation used by WordPress (utf8mb4_unicode_ci), you ensure 100% compatibility between WordPress and the database.
Note: It is mandatory to back up your database and your entire website before continuing, just to avoid surprises. Many of us are using a variety of plugins and not all configurations and solutions that work for me might work for you.
Note: I made this change in my database to avoid potential issues with future MariaDB 10 updates on my Synology NAS.
This post was updated on Sunday / November 1st, 2020 at 4:49 AM