Illegal mix of collations

none · August 25, 2024, 6:18am

54mu3l · August 25, 2024, 7:59pm

I have the same problem:

Using version 5.1.1

Any solution to that problem?

heurteph-ei · August 28, 2024, 9:33am

Hi @54mu3l, @none
I seems your database uses several SQL collations (kind of language for string characters).
Is this error following an update or is it a fresh installation?

none · August 28, 2024, 9:45am

It was an update to the latest 5.x version

none · August 28, 2024, 9:46am

Before the update everything worked well.

an0nfunc · August 28, 2024, 3:13pm

Same problem here, happened with the upgrade from 11.4.3 → 11.5.2. I checked all my collations, and they all match.

heurteph-ei · August 29, 2024, 8:57am

Hi @an0nfunc
If you did the DB migration, then I suppose the problem comes from there. You have to fix this manually, or revert to the previous DB…

heurteph-ei · August 29, 2024, 8:58am

Hi @none
When upgrading, did you also add new plugins?

none · August 29, 2024, 9:28am

Hi, i did not install anything. I just run the update to the latest matomo version via matomo interface. Thete were no error messages diring the update. Since the updaze i get those sql errors.

an0nfunc · August 29, 2024, 10:00am

Fix what manually? The tables and columns all have the correct collation, that’s what’s so weird about this.

Matomo was not upgraded since this started. Was only the mariadb upgrade.

heurteph-ei · August 30, 2024, 3:02pm

Hi @an0nfunc, @none, @54mu3l
It seems the bug has been reported in the Matomo GitHub:

github.com/matomo-org/matomo

[Bug] Illegal mix of collations

opened 06:57AM - 28 Aug 24 UTC

felixlabrot

Bug

### 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 ```shell 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 - [X] Read our [Contributing Guidelines](https://github.com/matomo-org/matomo/blob/5.x-dev/CONTRIBUTING.md). - [X] Follow our [Security Policy](https://github.com/matomo-org/matomo/blob/5.x-dev/SECURITY.md). - [X] Check that there isn't already an issue that reports the same bug to avoid creating duplicates. - [X] The provided steps to reproduce is a [minimal reproducible](https://stackoverflow.com/help/minimal-reproducible-example) of the Bug.

54mu3l · August 30, 2024, 3:48pm

@heurteph-ei It’s not a fresh installation. The installation is running for several years now.

The issue arose a couple days ago. It might be linked to the update to Matomo v5.1.1.

But, there might also have been an automatic MariaDB update. So this could also be the root of the problem. Currently running MariaDB 11.5.2

As @an0nfunc mentioned, I also checked my collations and they seem correct.
Therefore, I don’t know how I could fix this manually…

But let’s see what the GitHub-Issue concludes…

Since the data collection continues to work for now, we can wait a couple of days. But the archiving job also throws an error. Therefore I hope for a solution soon.

an0nfunc · August 30, 2024, 4:01pm

This will need changes on Matomo’s side. Setting the connection collation should fix this, probably.

yellowdog · September 4, 2024, 12:04pm

We also have the same issue. We updated Matomo to 5.1.1 and then the docker, which is running 11.5.2, and now we get the mismatch error.
Should we expect an update from Matomo, or do we have to fix this ourselves, and if so, how?

heurteph-ei · September 4, 2024, 1:18pm

Hi @yellowdog,
I think you’ll have to wait for a fix.
As soon as a a pull request will be linked to the ticket and the merge approved, you’ll be able to apply manually the fix on your installation, before waiting for the official release containing this fix.