[Bug] Illegal mix of collations with MariaDB 11.5+ #22536
Description
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.