@Starker3 opened this Issue on June 26th 2022 Contributor

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.

@sgiehl commented on June 27th 2022 Member

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,...

@Starker3 commented on June 28th 2022 Contributor

@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

@sgiehl commented on June 28th 2022 Member

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.

@Starker3 commented on June 28th 2022 Contributor

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

@sgiehl commented on June 29th 2022 Member

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

@Starker3 commented on July 6th 2022 Contributor

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 ?

This Issue was closed on July 15th 2022
Powered by GitHub Issue Mirror