[Bug] Illegal mix of collations with MariaDB 11.5+

[felixlabrot]

What happened?

Since a few days the archiving job throws on 3 of 8 websites the error Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '=' - in plugin Actions.
The database default collection is utf8mb4_general_ci and so are all tables created since June 2021. Tables created before June 2021 are utf8mb4_unicode_ci. There is no table with utf8mb4_uca1400_ai_ci charset.
Database is MariaDB 11.5.2.

What should happen?

The archive job should complete without errors in 3 of 8 websites.

How can this be reproduced?

run the archive job

Matomo version

5.1.1

PHP version

8.2.22

Server operating system

Docker

What browsers are you seeing the problem on?

No response

Computer operating system

No response

Relevant log output

Error: Got invalid response from API request: ?module=API&method=CoreAdminHome.archiveReports&idSite=11&period=year&date=2024-01-01&format=json&trigger=archivephp. Response was '{\"result\":\"error\",\"message\":\"SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '=' - in plugin Actions. #0 \\/var\\/www\\/html\\/core\\/ArchiveProcessor\\/Loader.php(317): Piwik\\\\ArchiveProcessor\\\\PluginsArchiver->callAggregateAllPlugins(2199.0, 0, false) #1 \\/var\\/www\\/html\\/core\\/ArchiveProcessor\\/Loader.php(186): Piwik\\\\ArchiveProcessor\\\\Loader->prepareAllPluginsArchive(2199.0, 0) #2 \\/var\\/www\\/html\\/core\\/ArchiveProcessor\\/Loader.php(165): Piwik\\\\ArchiveProcessor\\\\Loader->insertArchiveData(2191.0, 0, false, NULL) #3 \\/var\\/www\\/html\\/core\\/ArchiveProcessor\\/Loader.php(105): Piwik\\\\ArchiveProcessor\\\\Loader->prepareArchiveImpl(false) #4 \\/var\\/www\\/html\\/core\\/Context.php(76): Piwik\\\\ArchiveProcessor\\\\Loader->Piwik\\\\ArchiveProcessor\\\\{closure}() #5 \\/var\\/www\\/html\\/core\\/ArchiveProcessor\\/Loader.php(102): Piwik\\\\Context::changeIdSite(11, Object(Closure)) #6 \\/var\\/www\\/html\\/plugins\\/CoreAdminHome\\/API.php(306): Piwik\\\\ArchiveProcessor\\\\Loader->prepareArchive(false) #7 [internal function]: Piwik\\\\Plugins\\\\CoreAdminHome\\\\API->archiveReports('11', Object(Piwik\\\\Period\\\\Year), '2024-01-01', false, false, false) #8 \\/var\\/www\\/html\\/core\\/API\\/Proxy.php(255): call_user_func_array(Array, Array) #9 \\/var\\/www\\/html\\/core\\/Context.php(29): Piwik\\\\API\\\\Proxy->Piwik\\\\API\\\\{closure}() #10 \\/var\\/www\\/html\\/core\\/API\\/Proxy.php(158): Piwik\\\\Context::executeWithQueryParameters(Array, Object(Closure)) #11 \\/var\\/www\\/html\\/core\\/API\\/Request.php(274): Piwik\\\\API\\\\Proxy->call('\\\\\\\\Piwik\\\\\\\\Plugins\\\\\\\\...', 'archiveReports', Array) #12 \\/var\\/www\\/html\\/plugins\\/API\\/Controller.php(46): Piwik\\\\API\\\\Request->process() #13 [internal function]: Piwik\\\\Plugins\\\\API\\\\Controller->index() #14 \\/var\\/www\\/html\\/core\\/FrontController.php(645): call_user_func_array(Array, Array) #15 \\/var\\/www\\/html\\/core\\/FrontController.php(169): Piwik\\\\FrontController->doDispatch('API', false, Array) #16 \\/var\\/www\\/html\\/core\\/dispatch.php(33): Piwik\\\\FrontController->dispatch() #17 \\/var\\/www\\/html\\/index.php(25): require_once('\\/var\\/www\\/html\\/c...') #18 \\/var\\/www\\/html\\/core\\/CliMulti\\/RequestCommand.php(76): require_once('\\/var\\/www\\/html\\/i...') #19 \\/var\\/www\\/html\\/core\\/Plugin\\/ConsoleCommand.php(109): Piwik\\\\CliMulti\\\\RequestCommand->doExecute() #20 \\/var\\/www\\/html\\/vendor\\/symfony\\/console\\/Command\\/Command.php(298): Piwik\\\\Plugin\\\\ConsoleCommand->execute(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput)) #21 \\/var\\/www\\/html\\/core\\/Plugin\\/ConsoleCommand.php(124): Symfony\\\\Component\\\\Console\\\\Command\\\\Command->run(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput)) #22 \\/var\\/www\\/html\\/vendor\\/symfony\\/console\\/Application.php(1040): Piwik\\\\Plugin\\\\ConsoleCommand->run(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput)) #23 \\/var\\/www\\/html\\/vendor\\/symfony\\/console\\/Application.php(301): Symfony\\\\Component\\\\Console\\\\Application->doRunCommand(Object(Piwik\\\\CliMulti\\\\RequestCommand), Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput)) #24 \\/var\\/www\\/html\\/core\\/Console.php(113): Symfony\\\\Component\\\\Console\\\\Application->doRun(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput)) #25 [internal function]: Piwik\\\\Console->originDoRun(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput)) #26 \\/var\\/www\\/html\\/core\\/Console.php(152): call_user_func(Array, Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput)) #27 \\/var\\/www\\/html\\/core\\/Access.php(672): Piwik\\\\Console->Piwik\\\\{closure}() #28 \\/var\\/www\\/html\\/core\\/Console.php(150): Piwik\\\\Access::doAsSuperUser(Object(Closure)) #29 \\/var\\/www\\/html\\/core\\/Console.php(92): Piwik\\\\Console->doRunImpl(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput)) #30 \\/var\\/www\\/html\\/vendor\\/symfony\\/console\\/Application.php(171): Piwik\\\\Console->doRun(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput)) #31 \\/var\\/www\\/html\\/console(32): Symfony\\\\Component\\\\Console\\\\Application->run() #32 {main}, caused by: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '=' #0 \\/var\\/www\\/html\\/libs\\/Zend\\/Db\\/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array) #1 \\/var\\/www\\/html\\/libs\\/Zend\\/Db\\/Adapter\\/Abstract.php(479): Zend_Db_Statement->execute(Array) #2 \\/var\\/www\\/html\\/libs\\/Zend\\/Db\\/Adapter\\/Pdo\\/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT value, n...', Array) #3 \\/var\\/www\\/html\\/core\\/Db\\/Adapter\\/Pdo\\/Mysql.php(336): Zend_Db_Adapter_Pdo_Abstract->query('SELECT value, n...', Array) #4 \\/var\\/www\\/html\\/core\\/DataAccess\\/ArchiveSelector.php(614): Piwik\\\\Db\\\\Adapter\\\\Pdo\\\\Mysql->query('SELECT value, n...', Array) #5 \\/var\\/www\\/html\\/core\\/ArchiveProcessor.php(399): Piwik\\\\DataAccess\\\\ArchiveSelector::querySingleBlob(Array, 'Actions_sitesea...') #6 \\/var\\/www\\/html\\/core\\/ArchiveProcessor.php(378): Piwik\\\\ArchiveProcessor->getAggregatedDataTableMapFromBlobs(Object(Generator), NULL, Array, 'Actions_sitesea...') #7 \\/var\\/www\\/html\\/core\\/ArchiveProcessor.php(234): Piwik\\ ... )\\n#43 \\/var\\/www\\/html\\/console(32): Symfony\\\\Component\\\\Console\\\\Application->run()\\n#44 {main}\"}'

Validations

• Read our Contributing Guidelines.
• Follow our Security Policy.
• Check that there isn't already an issue that reports the same bug to avoid creating duplicates.
• The provided steps to reproduce is a minimal reproducible of the Bug.

[sgiehl]

Hey @felixlabrot,
Sorry to hear you have trouble around collations. It seems like MariaDb changed the default collation to utf8mb4_uca1400_ai_ci at some point. So maybe the temporary tables created during archiving have that collation and thus fails.
Did you maybe update MariaDb lately?

[felixlabrot]

Hey @sgiehl!
Yes, as of our regular update schedule, we update all applications in our datacenter regularily, when there are no breaking changes known.
It is insane to me that the database created stuff with it's own internal will instead of the configured default for the database.
I have now additionally set --collation-server=utf8mb4_general_ci as database parameter, which didn't help. It is still failing.

[mneudert]

Hey @felixlabrot,

we checked what happens with the most recent MariaDB version, and were able to reproduce the problem.

It looks like this change from MariaDB 11.5.0 is causing problems: MDEV-25829

The issue is indeed linked to a changed collation default, being problematic due to the way Matomo connects to the database (and the tables are created). Locally it was possible to fix the error by adding a new configuration value to the database (see MDEV-30164):

[mysqld]
character_set_collations='utf8mb4=utf8mb4_general_ci'

No extensive testing has been done except running archiving, so please be aware of this if you want to change your configuration!

---

Some more details on reproduction and the source, assuming the Matomo is configured with charset = "utf8mb4".

In a default MariaDB 11.4.3 docker container, when the archiver is creating a new table, it should have the following output for SHOW CREATE TABLE:

CREATE TABLE `archive_blob_2024_08` (
  ...
  `name` varchar(190) NOT NULL,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC

During archiving, queries like the following (modified from original query) will be executed:

SELECT name
FROM archive_blob_2024_08
WHERE idarchive IN (1,2,3) AND (name = 'Actions_sitesearch' OR (name LIKE 'Actions_sitesearch%' AND (SUBSTRING(name, 19, 7) = '_chunk_' OR (SUBSTRING(name, 20, 1) >= '0' AND SUBSTRING(name, 20, 1) <= '9'))))
ORDER BY CAST(IF((@idsubtable := SUBSTRING(SUBSTRING(name, IF(SUBSTRING(name, 19, 7) = '_chunk_', 26, 20)), 1, IF((@secondunderscore := LOCATE('_', SUBSTRING(name, IF(SUBSTRING(name, 19, 7) = '_chunk_', 26, 20))) - 1) < 0, LENGTH(name), @secondunderscore))) = '', -1, @idsubtable ) AS SIGNED) ASC;

After upgrading to MariaDB 11.5.2, the change of the default unicode collation is creating problems:

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '='

Changing the query a bit makes it work (adding a collation to @idsubtable := SUBSTRING(SUBSTRING(name in the ORDER BY clause):

SELECT name
FROM archive_blob_2024_08
WHERE idarchive IN (1,2,3) AND (name = 'Actions_sitesearch' OR (name LIKE 'Actions_sitesearch%' AND (SUBSTRING(name, 19, 7) = '_chunk_' OR (SUBSTRING(name, 20, 1) >= '0' AND SUBSTRING(name, 20, 1) <= '9'))))
ORDER BY CAST(IF((@idsubtable := SUBSTRING(SUBSTRING(name COLLATE utf8mb4_uca1400_ai_ci, IF(SUBSTRING(name, 19, 7) = '_chunk_', 26, 20)), 1, IF((@secondunderscore := LOCATE('_', SUBSTRING(name, IF(SUBSTRING(name, 19, 7) = '_chunk_', 26, 20))) - 1) < 0, LENGTH(name), @secondunderscore))) = '', -1, @idsubtable ) AS SIGNED) ASC;

A different solution would be to update the database connection init command:

SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;

This would also lets the query run without an error. Though there is no configuration available to fetch the to-be-used collation from, and a static value will probably become a problem with the various database environments Matomo runs in.

However, as mentioned in MDEV-30164, a configuration variable was introduced and, as of MariaDB 11.5.0, has the following default:

mysql> SELECT @@character_set_collations;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| @@character_set_collations                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| utf8mb3=utf8mb3_uca1400_ai_ci,ucs2=ucs2_uca1400_ai_ci,utf8mb4=utf8mb4_uca1400_ai_ci,utf16=utf16_uca1400_ai_ci,utf32=utf32_uca1400_ai_ci |
+-----------------------------------------------------------------------------------------------------------------------------------------+

With the description of "@@collation_connection after a SET NAMES without COLLATE", setting the value at the server level works.

[felixlabrot]

Hey @mneudert!
Either Matomo or MariaDB goes into denial when attempting anything to fix that. It is 100% ensured that the database settings are indeed correct and the database confirms that when asking for it. The archiving job is still in total denial about that. The database is explicitly started with options to force those settings. Also the database specific setting has been correct to begin with. That wrong collation comes out of nowhere and as the database is for sure correct, I guess Matomo messes around and creates that bug.

show variables like 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+

show variables like 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_general_ci |
+------------------+--------------------+

[mneudert]

Hi @felixlabrot,

your database configuration should not be at fault.

Can you check if SELECT @@collation_connection value changes after running SET NAMES utf8mb4 when connected to the database?

mysql> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+
1 row in set (0.00 sec)

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_uca1400_ai_ci  |
+------------------------+
1 row in set (0.00 sec)

Once Matomo connects to the database there are several points where the character set is getting messed with, one of them being SET NAMES as an init command.

Simply removing all of those commands and parameters does not fix the issue in my test instance. Modifying the code to use SET NAMES ... COLLATE ... and setting character_set_collations on the server side both do.

[felixlabrot]

Hi @mneudert,

running SET NAMES utf8mb4 indeed messes it up.

[MatomoForumNotifications]

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/illegal-mix-of-collations/59254/11

[DaAwesomeP]

Since PR #22564 won't touch installations that have already upgraded MariaDB (and may already have a mix of tables) and will intentionally fail to update, should we add a docs page somewhere on how to fix the issue manually?

[sgiehl]

@DaAwesomeP Thanks for mentioning that. Yes, we have planned to create an FAQ for that with details how to solve the problem and will mention that also in the release notes.

[felixlabrot]

When will a fixed version be provided for Docker? I upgraded just now to the latest version and it's still v5.1.1 and the issue is still there.

[sgiehl]

It was yet only released in a release candidate. We are planning the final release for this week. But I'm unable to say how fast the docker image will be updated, as I'm not involved in that process.

[DaAwesomeP]

Just checking in on when the patch release may be released. It doesn't look like there have been any more commits to the 5.1.x-dev branch since release 5.1.2-rc1.

[michalkleiner]

@DaAwesomeP as you can see above, at the moment we are planning for the stable version to go out this week.

[MatomoForumNotifications]

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/illegal-mix-of-collations/59254/20

[felixlabrot]

I can confirm that the issue is indeed resolved and the repair guide works.
Here the guide for reference for anyone who'll finds this issue looking for a fix.

https://matomo.org/faq/troubleshooting/how-do-i-resolve-the-error-illegal-mix-of-collations-for-operation/