How to convert the database to utf8mb4 charset?

As of Matomo 4, the utf8mb4 charset is supported. New installations of Matomo will automatically use this charset if it is available.

This new charset has the advantage that it is able to store 4 bytes characters, such as emojis, less common characters of asian languages, various historic scripts or mathematical symbols. To prevent errors when inserting such characters in the database Matomo will automatically replace those chars with a � if utf8mb4 is not used.

There are several methods available for converting the database to utf8mb4, each detailed below.

Note: Before doing the update, we recommend creating a database backup, to circumvent any possible loss. As converting the database and its tables might take several hours depending on your database size, tracking might not work correctly in this time. See this FAQ for possible steps on how to update without losing any tracking data.

1. Convert the Matomo database tables to utf8mb4 using a console command

You can trigger the conversion by using this console command:

./console core:convert-to-utf8mb4

(Note: if the command returns Command "core:convert-to-utf8mb4" is not defined. then your database should already be using utf8mb4 and you don’t need to run the command.)

2. Convert your Matomo database tables to utf8mb4 using a background task

If you are not able to run a console command there is also a possibility to trigger a conversion as scheduled task in the background. You can enable that in the section Updater settings within Matomo’s General settings.

We do not recommend doing this for bigger instances as you can’t control at which time this will happen.

3. Convert your Matomo database tables to utf8mb4 manually

If you do not want to use one of the other possibilities for any reason, you can also convert your database manually. Ensure to change the charset of the database and all tables to utf8mb4. We strongly recommend to disable tracking during the conversion of Matomo’s log tables. Once all tables are converted, you need to set the database charset in the Matomo configuration file to utf8mb4 to tell Matomo it can use it. To get a list of all commands and queries you need to execute, you can use the conversion command this way:

./console core:convert-to-utf8mb4 --show

Column size adjustment during utf8mb4 conversion

One final note is that when upgrading your database to support utf8mb4, be aware that columns storing text data (such as TEXT, MEDIUMTEXT, etc.) will automatically increase in size to ensure the upgraded column can store as many characters as the source column. For example a MEDIUMTEXT column will become a LONGTEXT column. You will need to account for this if you’re manually updating your database schema or if you have specific data storage limits to ensure a smooth upgrade.