@PeteTrombone opened this Issue on November 15th 2022

We keep getting the message that the archiving didn’t run successfully but the setup for the crontab is correct and the job goes through.

We get the following error message:

ERROR [2022-10-31 23:33:18] 15503 Got invalid response from API request: ?module=API&method=CoreAdminHome.archiveReports&idSite=464&period=week&date=2022-10-31&format=json&segment=visitConvertedGoalId%3D%3D4&trigger=archivephp. Response was ‘{“result”:“error”,“message”:"SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘idlink_va’ in field list is ambiguous - in plugin PagePerformance. #0 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(284): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateAllPlugins(‘2654’, ‘2654’, false) #1 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(176): Piwik\ArchiveProcessor\Loader->prepareAllPluginsArchive(‘2654’, ‘2654’) #2 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(159): Piwik\ArchiveProcessor\Loader->insertArchiveData(‘2028’, ‘2028’) #3 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(99): Piwik\ArchiveProcessor\Loader->prepareArchiveImpl(‘VisitsSummary’) #4 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Context.php(75): Piwik\ArchiveProcessor\Loader->Piwik\ArchiveProcessor{closure}() #5 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(103): Piwik\Context::changeIdSite(464, Object(Closure)) #6 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/plugins/CoreAdminHome/API.php(284): Piwik\ArchiveProcessor\Loader->prepareArchive(‘VisitsSummary’) #7 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(894): Piwik\Plugins\CoreAdminHome\API->archiveReports(464, Object(Piwik\Period\Day), ‘2022-10-31’, ‘visitConvertedG…’, ‘VisitsSummary’, ‘’) #8 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(686): Piwik\Archive->prepareArchive(Array, Object(Piwik\Site), Object(Piwik\Period\Day)) #9 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(624): Piwik\Archive->cacheArchiveIdsAfterLaunching(Array, Array) #10 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(549): Piwik\Archive->getArchiveIds(Array) #11 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(347): Piwik\Archive->get(Array, ‘numeric’) #12 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor.php(630): Piwik\Archive->getDataTableFromNumeric(Array) #13 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor.php(265): Piwik\ArchiveProcessor->getAggregatedNumericMetrics(Array, false) #14 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/PluginsArchiver.php(306): Piwik\ArchiveProcessor->aggregateNumericMetrics(Array) #15 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/PluginsArchiver.php(104): Piwik\ArchiveProcessor\PluginsArchiver->aggregateMultipleVisitsMetrics() #16 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(278): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateCoreMetrics() #17 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(176): Piwik\ArchiveProcessor\Loader->prepareAllPluginsArchive(false, false) #18 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(159): Piwik\ArchiveProcessor\Loader->insertArchiveData(false, false) #19 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(99): Piwik\ArchiveProcessor\Loader->prepareArchiveImpl(false) #20 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Context.php(75): Piwik\ArchiveProcessor\Loader->Piwik\ArchiveProcessor{closure}() #21 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(103): Piwik\Context::changeIdSite(464, Object(Closure)) #22 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/plugins/CoreAdminHome/API.php(284): Piwik\ArchiveProcessor\Loader->prepareArchive(false) #23 [internal function]: Piwik\Plugins\CoreAdminHome\API->archiveReports(‘464’, Object(Piwik\Period\Week), ‘2022-10-31’, ‘visitConvertedG…’, false, false) #24 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/API/Proxy.php(244): call_user_func_array(Array, Array) #25 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Context.php(28): Piwik\API\Proxy->Piwik\API{closure}() #26 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/API/Proxy.php(335): Piwik\Context::executeWithQueryParameters(Array, Object(Closure)) #27 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/API/Request.php(267): Piwik\API\Proxy->call(’\Piwik\Plugins\…’, ‘archiveReports’, Array) #28 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/plugins/API/Controller.php(45): Piwik\API\Request->process() #29 [internal function]: Piwik\Plugins\API\Controller->index() #30 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/FrontController.php(631): call_user_func_array(Array, Array) #31 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/FrontController.php(169): Piwik\FrontController->doDispatch(‘API’, false, Array) #32 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/dispatch.php(32): Piwik\FrontController->dispatch() #33 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/index.php(25): require_once(’/app/dbuser/int/…’) #34 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/CliMulti/RequestCommand.php(79): require_once(’/app/dbuser/int/…’) #35 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(257): Piwik\CliMulti\RequestCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Co … .4-inst1/htdocs/piwik/console(32): Symfony\Component\Console\Application->run()\n#58 {main}"}’

ERROR [2022-10-31 23:40:22] 15503 Got invalid response from API request: ?module=API&method=CoreAdminHome.archiveReports&idSite=464&period=year&date=2022-01-01&format=json&segment=visitConvertedGoalId%3D%3D4&trigger=archivephp. Response was ‘{“result”:“error”,“message”:“SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘idlink_va’ in field list is ambiguous - in plugin PagePerformance. #0 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(284): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateAllPlugins(‘471’, ‘471’, false) #1 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(176): Piwik\ArchiveProcessor\Loader->prepareAllPluginsArchive(‘471’, ‘471’) #2 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(159): Piwik\ArchiveProcessor\Loader->insertArchiveData(‘471’, ‘471’) #3 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(99): Piwik\ArchiveProcessor\Loader->prepareArchiveImpl(‘VisitsSummary’) #4 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Context.php(75): Piwik\ArchiveProcessor\Loader->Piwik\ArchiveProcessor{closure}() #5 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(103): Piwik\Context::changeIdSite(464, Object(Closure)) #6 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/plugins/CoreAdminHome/API.php(284): Piwik\ArchiveProcessor\Loader->prepareArchive(‘VisitsSummary’) #7 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(894): Piwik\Plugins\CoreAdminHome\API->archiveReports(464, Object(Piwik\Period\Day), ‘2022-08-14’, ‘visitConvertedG…’, ‘VisitsSummary’, ‘’) #8 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(686): Piwik\Archive->prepareArchive(Array, Object(Piwik\Site), Object(Piwik\Period\Day)) #9 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(624): Piwik\Archive->cacheArchiveIdsAfterLaunching(Array, Array) #10 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(549): Piwik\Archive->getArchiveIds(Array) #11 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(347): Piwik\Archive->get(Array, ‘numeric’) #12 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor.php(630): Piwik\Archive->getDataTableFromNumeric(Array) #13 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor.php(265): Piwik\ArchiveProcessor->getAggregatedNumericMetrics(Array, false) #14 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/PluginsArchiver.php(306): Piwik\ArchiveProcessor->aggregateNumericMetrics(Array) #15 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/PluginsArchiver.php(104): Piwik\ArchiveProcessor\PluginsArchiver->aggregateMultipleVisitsMetrics() #16 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(278): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateCoreMetrics() #17 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(176): Piwik\ArchiveProcessor\Loader->prepareAllPluginsArchive(‘31206’, ‘31206’) #18 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(159): Piwik\ArchiveProcessor\Loader->insertArchiveData(‘31206’, ‘31206’) #19 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(99): Piwik\ArchiveProcessor\Loader->prepareArchiveImpl(‘VisitsSummary’) #20 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Context.php(75): Piwik\ArchiveProcessor\Loader->Piwik\ArchiveProcessor{closure}() #21 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(103): Piwik\Context::changeIdSite(464, Object(Closure)) #22 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/plugins/CoreAdminHome/API.php(284): Piwik\ArchiveProcessor\Loader->prepareArchive(‘VisitsSummary’) #23 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(894): Piwik\Plugins\CoreAdminHome\API->archiveReports(464, Object(Piwik\Period\Week), ‘2022-08-08’, ‘visitConvertedG…’, ‘VisitsSummary’, ‘’) #24 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(686): Piwik\Archive->prepareArchive(Array, Object(Piwik\Site), Object(Piwik\Period\Week)) #25 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(624): Piwik\Archive->cacheArchiveIdsAfterLaunching(Array, Array) #26 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(549): Piwik\Archive->getArchiveIds(Array) #27 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/Archive.php(347): Piwik\Archive->get(Array, ‘numeric’) #28 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor.php(630): Piwik\Archive->getDataTableFromNumeric(Array) #29 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor.php(265): Piwik\ArchiveProcessor->getAggregatedNumericMetrics(Array, false) #30 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/PluginsArchiver.php(306): Piwik\ArchiveProcessor->aggregateNumericMetrics(Array) #31 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/PluginsArchiver.php(104): Piwik\ArchiveProcessor\PluginsArchiver->aggregateMultipleVisitsMetrics() #32 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(278): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateCoreMetrics() #33 /app/dbuser/int/apache-2.4-inst1/htdocs/piwik/core/ArchiveProcessor/Loader.php(176): Piwik\ArchiveProcessor\Loader->prepareAllPluginsArchive(‘68708’, ‘68708’) #34 /app/dbus … .4-inst1/htdocs/piwik/console(32): Symfony\Component\Console\Application->run()\n#90 {main}”}’

Environment:

  • Matomo-Version: 4.12.0
  • MySQL-Version: 10.4.26-MariaDB-log
  • PHP-Version: 7.4.32

Additionally installed plugins:

  • Media Analytics
  • Heatmap & Session Recording
  • Custom Reports
  • Users Flow
  • Search Engine Keywords Performance
  • A/B Testing
  • Activity Log
  • Custom Alerts
  • Log Viewer
@sgiehl commented on November 15th 2022 Member

Hi @PeteTrombone
Thanks for this report. Are you able to apply the code changes in https://github.com/matomo-org/matomo/pull/20017 to check if that fixes the problem for you?
Cheers,

@PeteTrombone commented on November 16th 2022

Hi @sgiehl changing the code fixed the problem :-)
Many Thanks!!

@sgiehl commented on November 16th 2022 Member

Awesome. Thanks for confirmation

This Issue was closed on November 17th 2022
Powered by GitHub Issue Mirror