How do I resolve the error: Illegal mix of collations … for operation ‘=’?

A new configuration setting ([database] collation) is now available to address an issue encountered with archiving in Matomo. If you updated your database to MariaDB 11.5.2 prior to updating Matomo, you may still encounter collation-related issues, and a diagnostic check might flag the message “please set a collation.”

Understanding the impact of charset and collation is crucial because they determine how data is stored, compared, and sorted in a database.

  • A character set (charset) is a group of characters (letters, numbers, and symbols) that a database can store and manage.
  • A collation defines how these characters are compared and sorted.

You can think of the character set as the available alphabet, and the collation as the rules that determine the order and relationship between those characters when searching or sorting data. Different collations can affect how data is sorted and searched, even if the character set remains the same.

How to resolve the error for mixed collations

An error occurs when Matomo’s database connection and one of the database tables use different collations. While the exact error message may vary, an example is:

Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '=' - in plugin Actions.

Matomo is connected to the database using the utf8mb4_uca1400_ai_ci collation, but the accessed table uses utf8mb4_general_ci.

1) Update Matomo to version 5.1.2 or later

Starting with Matomo 5.1.2, a new configuration option ensures that the database connection collation is set to a fixed value, rather than relying on the database’s default setting.

During the update to 5.1.2, Matomo will attempt to automatically detect the best collation to use by checking the current database and connection settings. The following checks are performed:

  • If the « users » table collation matches the connection collation, that collation will be configured.

  • If the « users » table collation matches the most recent archiving table’s collation, that value will be used.

You can verify the current collation setting in Matomo’s System Check under the DB Collation section. If a collation is set, the issue should be resolved.

2) Manually configure the collation

If the update does not automatically configure a collation, you will see a warning under the Database abilities section of the system check.

To avoid future issues when upgrading your database, it is recommended to manually set a collation. The appropriate collation depends on your Matomo setup and database. Refer to your database documentation or seek advice on the Matomo forums to select a compatible collation.

3) Fixing archiving errors caused by collation mismatches

After updating Matomo and configuring the database collation, archiving errors may persist due to existing tables using incompatible collations. If the default collation used by your database vendor has changed, tables created before the update may still have different collations, causing archiving to fail. To fix this, you will need to manually update the collation of the affected tables.

  • Run the following command to see the collation of your tables in your database:

    SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'matomo';

For example, the following tables have different collations: archive_blob_2024_05 uses a different collation than archive_blob_2024_04, which can lead to archiving errors.

+--------------------------------+-------------------------+
| archive_blob_2024_04           | utf8mb4_general_ci      |
| archive_blob_2024_05           | utf8mb4_uca1400_ai_ci   |
+--------------------------------+-------------------------+

  • Decide which collation you want to use and then run the following command to alter the table’s collation:

    ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Note: This operation can take time depending on the table’s size. It should only be performed on archiving tables, such as those with « archive_numeric » or « archive_blob » in their names.

Alternatively, you can delete the affected tables, invalidate the reports for that month, and re-archive them from scratch.

Previous FAQ: How do I downgrade from Matomo 4 to Matomo 3?