PHP 8.1 error with Mysqli "The number of variables must match the number of parameters in the prepared statement" #19413

When running the core:archive command on a Matomo install using PHP 8.1, the following error is returned:

Got invalid response from API request: ?module=API&method=CoreAdminHome.archiveReports&idSite=2&period=day&date=2022-06-15&format=json&segment=countryCode%3D%3DPK&trigger=archivephp. 
Response was '{"result":"error","message":"The number of variables must match the number of parameters in the prepared statement on \/var\/www\/piwik\/libs\/Zend\/Db\/Statement\/Mysqli.php(208)
<a href='/0'>#0</a> [internal function]: mysqli_stmt->bind_param()
<a href='/1'>#1</a> \/var\/www\/piwik\/libs\/Zend\/Db\/Statement\/Mysqli.php(208): mysqli_stmt->execute()
<a href='/2'>#2</a> \/var\/www\/piwik\/libs\/Zend\/Db\/Statement.php(300): Zend_Db_Statement_Mysqli->_execute()
<a href='/3'>#3</a> \/var\/www\/piwik\/libs\/Zend\/Db\/Adapter\/Abstract.php(479): Zend_Db_Statement->execute()
<a href='/4'>#4</a> \/var\/www\/piwik\/core\/DataAccess\/LogAggregator.php(325): Zend_Db_Adapter_Abstract->query()
<a href='/5'>#5</a> \/var\/www\/piwik\/core\/DataAccess\/LogAggregator.php(351): Piwik\\DataAccess\\LogAggregator->createTemporaryTable()
<a href='/6'>#6</a> \/var\/www\/piwik\/core\/DataAccess\/LogAggregator.php(561): Piwik\\DataAccess\\LogAggregator->generateQuery()
<a href='/7'>#7</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/PluginsArchiver.php(285): Piwik\\DataAccess\\LogAggregator->queryVisitsByDimension()
<a href='/8'>#8</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/PluginsArchiver.php(102): Piwik\\ArchiveProcessor\\PluginsArchiver->aggregateDayVisitsMetrics()
<a href='/9'>#9</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/Loader.php(278): Piwik\\ArchiveProcessor\\PluginsArchiver->callAggregateCoreMetrics()
<a href='/10'>#10</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/Loader.php(176): Piwik\\ArchiveProcessor\\Loader->prepareAllPluginsArchive()
<a href='/11'>#11</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/Loader.php(159): Piwik\\ArchiveProcessor\\Loader->insertArchiveData()
<a href='/12'>#12</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/Loader.php(99): Piwik\\ArchiveProcessor\\Loader->prepareArchiveImpl()
<a href='/13'>#13</a> \/var\/www\/piwik\/core\/Context.php(75): Piwik\\ArchiveProcessor\\Loader->Piwik\\ArchiveProcessor\\{closure}()
<a href='/14'>#14</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/Loader.php(103): Piwik\\Context::changeIdSite()
<a href='/15'>#15</a> \/var\/www\/piwik\/plugins\/CoreAdminHome\/API.php(277): Piwik\\ArchiveProcessor\\Loader->prepareArchive()
<a href='/16'>#16</a> [internal function]: Piwik\\Plugins\\CoreAdminHome\\API->archiveReports()
<a href='/17'>#17</a> \/var\/www\/piwik\/core\/API\/Proxy.php(244): call_user_func_array()
<a href='/18'>#18</a> \/var\/www\/piwik\/core\/Context.php(28): Piwik\\API\\Proxy->Piwik\\API\\{closure}()
<a href='/19'>#19</a> \/var\/www\/piwik\/core\/API\/Proxy.php(335): Piwik\\Context::executeWithQueryParameters()
<a href='/20'>#20</a> \/var\/www\/piwik\/core\/API\/Request.php(266): Piwik\\API\\Proxy->call()
<a href='/21'>#21</a> \/var\/www\/piwik\/plugins\/API\/Controller.php(45): Piwik\\API\\Request->process()
<a href='/22'>#22</a> [internal function]: Piwik\\Plugins\\API\\Controller->index()
<a href='/23'>#23</a> \/var\/www\/piwik\/core\/FrontController.php(631): call_user_func_array()
<a href='/24'>#24</a> \/var\/www\/piwik\/core\/FrontController.php(169): Piwik\\FrontController->doDispatch()
<a href='/25'>#25</a> \/var\/www\/piwik\/core\/dispatch.php(32): Piwik\\FrontController->dispatch()
<a href='/26'>#26</a> \/var\/www\/piwik\/index.php(25): require_once('...')
<a href='/27'>#27</a> \/var\/www\/piwik\/core\/CliMulti\/RequestCommand.php(79): require_once('...')
<a href='/28'>#28</a> \/var\/www\/piwik\/vendor\/symfony\/console\/Symfony\/Component\/Console\/Command\/Command.php(257): Piwik\\CliMulti\\RequestCommand->execute()
<a href='/29'>#29</a> \/var\/www\/piwik\/vendor\/symfony\/console\/Symfony\/Component\/Console\/Application.php(874): Symfony\\Component\\Console\\Command\\Command->run()
<a href='/30'>#30</a> \/var\/www\/piwik\/vendor\/symfony\/console\/Symfony\/Component\/Console\/Application.php(195): Symfony\\Component\\Console\\Application->doRunCommand()
<a href='/31'>#31</a> [internal function]: Symfony\\Component\\Console\\Application->doRun()
<a href='/32'>#32</a> \/var\/www\/piwik\/core\/Console.php(135): call_user_func()
<a href='/33'>#33</a> \/var\/www\/piwik\/core\/Access.php(670): Piwik\\Console->Piwik\\{closure}()
<a href='/34'>#34</a> \/var\/www\/piwik\/core\/Console.php(136): Piwik\\Access::doAsSuperUser()
<a href='/35'>#35</a> \/var\/www\/piwik\/core\/Console.php(87): Piwik\\Console->doRunImpl()
<a href='/36'>#36</a> \/var\/www\/piwik\/vendor\/symfony\/console\/Symfony\/Component\/Console\/Application.php(126): Piwik\\Console->doRun()
<a href='/37'>#37</a> \/var\/www\/piwik\/console(32): Symfony\\Component\\Console\\Application->run()
<a href='/38'>#38</a> {main}","backtrace":"The number of variables must match the number of parameters in the prepared statement on \/var\/www\/piwik\/libs\/Zend\/Db\/Statement\/Mysqli.php(208)
<a href='/0'>#0</a> [internal function]: mysqli_stmt->bind_param()
<a href='/1'>#1</a> \/var\/www\/piwik\/libs\/Zend\/Db\/Statement\/Mysqli.php(208): mysqli_stmt->execute()
<a href='/2'>#2</a> \/var\/www\/piwik\/libs\/Zend\/Db\/Statement.php(300): Zend_Db_Statement_Mysqli->_execute()
<a href='/3'>#3</a> \/var\/www\/piwik\/libs\/Zend\/Db\/Adapter\/Abstract.php(479): Zend_Db_Statement->execute()
<a href='/4'>#4</a> \/var\/www\/piwik\/core\/DataAccess\/LogAggregator.php(325): Zend_Db_Adapter_Abstract->query()
<a href='/5'>#5</a> \/var\/www\/piwik\/core\/DataAccess\/LogAggregator.php(351): Piwik\\DataAccess\\LogAggregator->createTemporaryTable()
<a href='/6'>#6</a> \/var\/www\/piwik\/core\/DataAccess\/LogAggregator.php(561): Piwik\\DataAccess\\LogAggregator->generateQuery()
<a href='/7'>#7</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/PluginsArchiver.php(285): Piwik\\DataAccess\\LogAggregator->queryVisitsByDimension()
<a href='/8'>#8</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/PluginsArchiver.php(102): Piwik\\ArchiveProcessor\\PluginsArchiver->aggregateDayVisitsMetrics()
<a href='/9'>#9</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/Loader.php(278): Piwik\\ArchiveProcessor\\PluginsArchiver->callAggregateCoreMetrics()
<a href='/10'>#10</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/Loader.php(176): Piwik\\ArchiveProcessor\\Loader->prepareAllPluginsArchive()
<a href='/11'>#11</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/Loader.php(159): Piwik\\ArchiveProcessor\\Loader->insertArchiveData()
<a href='/12'>#12</a> \/var\/www\/piwik\/core\/ArchiveProcessor\/Loader.php(99): Piwik\\ArchiveProcessor\\Loader->prepareArchiveImpl()
<a href='/13'>#13</a> \/var\/www\/piwik\/core\/Context.php(75): Piwik\\ArchiveProcessor\\Lo ... 
<a href='/18'>#18</a> \/var\/www\/piwik\/console(32): Symfony\\Component\\Console\\Application->run()
<a href='/19'>#19</a> {main}"}'

Matomo version: 4.10.1
MySQL version: 10.3.31-MariaDB-log
PHP version: 8.1.7

For now we've asked the user to try switching the adapter to PDO to see if that temporarily resolves the issue.

There was a similar report with a prepared statement error #19129, but that might be unrelated, as it did happen in a live query.
Nevertheless I tried to reproduce with archiving the same segment, but for me that worked without a problem. Might be good to get some additional details on the setup. Like used plugins,...

@sgiehl here are their installed plugins:

Plugins Activated:

API, Actions, Annotations, BulkTracking, Contents, CoreAdminHome, CoreConsole, CoreHome, CorePluginsAdmin, CoreUpdater, CoreVisualizations, CoreVue, CustomDimensions, CustomJsTracker, CustomVariables 4.1.0, DBStats, Dashboard, DevicePlugins, DevicesDetection, Diagnostics, Ecommerce, Events, Feedback, GeoIp2, Goals, Heartbeat, ImageGraph, Insights, Installation, Intl, IntranetMeasurable, LanguagesManager, Live, Login, LoginSaml 4.1.2, Marketplace, MobileMessaging, Monolog, Morpheus, MultiSites, Overlay, PagePerformance, PrivacyManager, ProfessionalServices, Provider 4.0.3, Proxy, Referrers, Resolution, RssWidget, SEO, ScheduledReports, SegmentEditor, SitesManager, Tour, Transitions, TwoFactorAuth, UserCountry, UserCountryMap, UserId, UserLanguage, UsersManager, VisitFrequency, VisitTime, VisitorInterest, VisitsSummary, WebsiteMeasurable, Widgetize

Plugins Deactivated:

MobileAppMeasurable, TagManager

Hm... that doesn't look very special. I'm not able to reproduce that, so it might get hard to find the problem.
@Starker3 Is it possible to get some details on the failing query? It seems to happen here:
https://github.com/matomo-org/matomo/blob/70b004c968a1850b65c71156b465d189f4692a49/core/DataAccess/LogAggregator.php#L325
so maybe logging the query and the bound parameters in case of an error might help.

@sgiehl Would it be enough to run core:archive with -vvv ? Or should we enabled SQL profiling to get the full request?

I don't think -vvv would be enough. Guess you need to enabling logging of sql queries.

Changing the adapter to PDO/MYSQL fixed the error here, so the user didn't get the SQL queries logged. Not sure if you wanted to keep this one open or rather close it since we can't reproduce @sgiehl ?