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.
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.
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.
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.
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.
If everything goes well, you will see the following message: Your SQL query has been executed successfully. Follow the instructions in the image below.
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.
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