Closed vmalaga closed 2 years ago
@vmalaga do you know if you have any custom configs set in config/config.ini.php
? Any chance you could send us the output of this MySQL query?
select definition from matomo_segment
You may need to change matomo_segment
eg to piwik_segment
or something else.
The messages re the unexpected websites we can probably look into as we maybe mark them as to be reprocessed even though the idsite doesn't exist but can't really imagine it would do that. We will need to investigate that.
Could you let us know which version of Matomo you are on?
@tsteur this is the content of matomo_segment table
mysql> select definition from matomo_segment;
+---------------------------------+
| definition |
+---------------------------------+
| deviceType==smartphone |
| dimension1=@www.qdq.com |
| pageUrl=^www.qdq.com |
| dimension4==sw;dimension2==apu |
| dimension4==sw;dimension2==hans |
| dimension4==sw;dimension2==ray |
| dimension2==qdq |
| dimension4==sw;dimension2==lisa |
| dimension4==sw;dimension2==moe |
| dimension4==sw;dimension2==kent |
| dimension4==sw;dimension2==stu |
| eventUrl=@qdq |
| dimension4==sw |
| dimension4==ln |
| dimension4==pn |
| dimension1==www.qdq.com |
+---------------------------------+
and this is the matomo_site where you can see that we have only 1 site
mysql> select * from matomo_site\G
*************************** 1. row ***************************
idsite: 1
name: ALL
main_url: https://mtmall.qdqmedia.com
ts_created: 2019-01-01 00:00:00
ecommerce: 0
sitesearch: 1
sitesearch_keyword_parameters:
sitesearch_category_parameters:
timezone: Europe/Madrid
currency: EUR
exclude_unknown_urls: 0
excluded_ips:
excluded_parameters:
excluded_user_agents:
group:
type: website
keep_url_fragment: 0
creator_login: anonymous
1 row in set (0.00 sec)
@vmalaga just checking are you using the latest version of Matomo? Did this happen after an update?
Any chance you deleted a custom dimension recently maybe or made changes to it?
@tsteur we are using now matomo 3.13.0, but this happends since 2 versions ago. The problem appeared one day checking the logs of archiver after being notified that there were no statistics.
trying to solve the problem, I tried to delete all archive tables, delete all segments and all rows on matomo_log*
tables where idsite != 1
And about the dimensions, I see that there are records in that table with idsites != 1
I am going to delete this rows and run the archiver again to check if this solve the problem
@tsteur What is the reason for not having foreign keys in the tables that use idsite
refering to matomo_site.idsite
?
@vmalaga Myisam didn't support foreign keys which was used initially. In general we're not really wanting to use foreign keys though anyway as it's not really needed and they don't have that many advantages in the end.
Be great to let us know if it fixes the issue. So far I can only imagine that maybe a custom dimension was deleted but a segment definition exists using that dimension and we're not dealing with this yet maybe.
Hi @tsteur no the issue has not been fixed, even deleting all records with idsite != 1
Could you temporarily disable each segment one after another until the issue resolves to find out which one is causing it? Also could you check which custom dimensions are active for idsite1?
I'm having the same error message on a fresh installation of Matomo 4.5.0 without any custom plugins, one site and no tracking in it yet. The matomo_segment table is empty, but still I get that error message:
A fatal error occurred
The following error just broke Matomo (v4.5.0):
An exception has been thrown during the rendering of a template ("Segment cannot be added because no sql segment is set").
in /var/www/matomo/gopa/plugins/CoreHome/templates/getDefaultIndexView.twig line 7
This happens on https://analytics.example.com/index.php?module=CoreHome but I can manually go to https://analytics.example.com/index.php?module=CoreAdminHome without a problem.
@jurgenhaas Are you maybe able to see a stack trace for that error in the error log? That might help to figure out where that problem actually is triggered.
Hmm, when calling the crashing site, nothing is shown in the log. But the error.log is full of records like this:
Error in Matomo (tracker): Error query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'location_provider' in 'field list' In query: INSERT INTO piwik_log_visit (idvisitor, config_id, location_ip, idsite, profilable, visit_first_action_time, visit_goal_buyer, visit_goal_converted, visit_last_action_time, visitor_returning, visitor_seconds_since_first, visitor_seconds_since_order, visitor_count_visits, visit_entry_idaction_name, visit_entry_idaction_url, visit_exit_idaction_name, visit_exit_idaction_url, visit_total_actions, visit_total_interactions, visit_total_searches, referer_keyword, referer_name, referer_type, referer_url, location_browser_lang, config_browser_engine, config_browser_name, config_browser_version, config_client_type, config_device_brand, config_device_model, config_device_type, config_os, config_os_version, visit_total_events, visitor_localtime, visitor_seconds_since_last, config_resolution, config_cookie, config_flash, config_java, config_pdf, config_quicktime, config_realplayer, config_silverlight, config_windowsmedia, visit_total_time, location_country, location_provider) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) Parameters: array ( 0 => '\xb5B\xdbm\x98\xda0\xbc', 1 => '\xac \r#\x0c\xe6{\x11', 2 => '\xd9n' . "\\0" . '' . "\\0" . '', 3 => 1, 4 => 0, 5 => '2021-11-08 11:54:08', 6 => 0, 7 => 0, 8 => '2021-11-08 11:54:08', 9 => 0, 10 => 0, 11 => NULL, 12 => 1, 13 => 5, 14 => 6, 15 => 5, 16 => 6, 17 => 1, 18 => 1, 19 => 0, 20 => NULL, 21 => NULL, 22 => 1, 23 => '', 24 => 'de-de', 25 => 'Blink', 26 => 'CH', 27 => '95.0', 28 => 1, 29 => '', 30 => 'generic desktop', 31 => 0, 32 => 'WIN', 33 => '10', 34 => 0, 35 => '12:54:08', 36 => 0, 37 => '1920x1080', 38 => 1, 39 => 0, 40 => 0, 41 => 1, 42 => 0, 43 => 0, 44 => 0, 45 => 0, 46 => 0, 47 => 'de', 48 => 'Ip', )
This is when I'm looking into the Apache log files. Are there any other logs by Matomo elsewhere?
When I run ./console core:archive --url=https://analytics.example.com/
I'm getting errors like these:
Error: Got invalid response from API request: ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=day&date=2021-11-08&format=json&trigger=archivephp. Response was '{"result":"error","message":"Segment cannot be added because no sql segment is set - in plugin Goals. #0 \/var\/www\/matomo\/gopa\/core\/ArchiveProcessor\/Loader.php(218): Piwik\\ArchiveProcessor\\PluginsArchiver->callAggregateAllPlugins() #1 \/var\/www\/matomo\/gopa\/core\/ArchiveProcessor\/Loader.php(158): Piwik\\ArchiveProcessor\\Loader->prepareAllPluginsArchive() #2 \/var\/www\/matomo\/gopa\/core\/ArchiveProcessor\/Loader.php(101): Piwik\\ArchiveProcessor\\Loader->prepareArchiveImpl() #3 \/var\/www\/matomo\/gopa\/core\/Context.php(75): Piwik\\ArchiveProcessor\\Loader->Piwik\\ArchiveProcessor\\{closure}() #4 \/var\/www\/matomo\/gopa\/core\/ArchiveProcessor\/Loader.php(105): Piwik\\Context::changeIdSite() #5 \/var\/www\/matomo\/gopa\/plugins\/CoreAdminHome\/API.php(278): Piwik\\ArchiveProcessor\\Loader->prepareArchive() #6 [internal function]: Piwik\\Plugins\\CoreAdminHome\\API->archiveReports() #7 \/var\/www\/matomo\/gopa\/core\/API\/Proxy.php(244): call_user_func_array() #8 \/var\/www\/matomo\/gopa\/core\/Context.php(28): Piwik\\API\\Proxy->Piwik\\API\\{closure}() #9 \/var\/www\/matomo\/gopa\/core\/API\/Proxy.php(335): Piwik\\Context::executeWithQueryParameters() #10 \/var\/www\/matomo\/gopa\/core\/API\/Request.php(266): Piwik\\API\\Proxy->call() #11 \/var\/www\/matomo\/gopa\/plugins\/API\/Controller.php(46): Piwik\\API\\Request->process() #12 [internal function]: Piwik\\Plugins\\API\\Controller->index() #13 \/var\/www\/matomo\/gopa\/core\/FrontController.php(619): call_user_func_array() #14 \/var\/www\/matomo\/gopa\/core\/FrontController.php(168): Piwik\\FrontController->doDispatch() #15 \/var\/www\/matomo\/gopa\/core\/dispatch.php(32): Piwik\\FrontController->dispatch() #16 \/var\/www\/matomo\/gopa\/index.php(25): require_once('\/var\/www\/matomo...') #17 \/var\/www\/matomo\/gopa\/core\/CliMulti\/RequestCommand.php(79): require_once('\/var\/www\/matomo...') #18 \/var\/www\/matomo\/gopa\/vendor\/symfony\/console\/Symfony\/Component\/Console\/Command\/Command.php(257): Piwik\\CliMulti\\RequestCommand->execute() #19 \/var\/www\/matomo\/gopa\/vendor\/symfony\/console\/Symfony\/Component\/Console\/Application.php(874): Symfony\\Component\\Console\\Command\\Command->run() #20 \/var\/www\/matomo\/gopa\/vendor\/symfony\/console\/Symfony\/Component\/Console\/Application.php(195): Symfony\\Component\\Console\\Application->doRunCommand() #21 [internal function]: Symfony\\Component\\Console\\Application->doRun() #22 \/var\/www\/matomo\/gopa\/core\/Console.php(130): call_user_func() #23 \/var\/www\/matomo\/gopa\/core\/Access.php(670): Piwik\\Console->Piwik\\{closure}() #24 \/var\/www\/matomo\/gopa\/core\/Console.php(131): Piwik\\Access::doAsSuperUser() #25 \/var\/www\/matomo\/gopa\/core\/Console.php(82): Piwik\\Console->doRunImpl() #26 \/var\/www\/matomo\/gopa\/vendor\/symfony\/console\/Symfony\/Component\/Console\/Application.php(126): Piwik\\Console->doRun() #27 \/var\/www\/matomo\/gopa\/console(32): Symfony\\Component\\Console\\Application->run() #28 {main}, caused by: Segment cannot be added because no sql segment is set #0 \/var\/www\/matomo\/gopa\/plugins\/CustomVariables\/Columns\/Base.php(50): Piwik\\Columns\\DimensionSegmentFactory->createSegment() #1 \/var\/www\/matomo\/gopa\/plugins\/CustomVariables\/Columns\/CustomVariableName.php(19): Piwik\\Plugins\\CustomVariables\\Columns\\Base->configureSegmentsFor() #2 \/var\/www\/matomo\/gopa\/core\/Segment\/SegmentsList.php(132): Piwik\\Plugins\\CustomVariables\\Columns\\CustomVariableName->configureSegments() #3 \/var\/www\/matomo\/gopa\/plugins\/API\/SegmentMetadata.php(27): Piwik\\Segment\\SegmentsList::get() #4 \/var\/www\/matomo\/gopa\/plugins\/API\/API.php(194): Piwik\\Plugins\\API\\SegmentMetadata->getSegmentsMetadata() #5 [internal function]: Piwik\\Plugins\\API\\API->getSegmentsMetadata() #6 \/var\/www\/matomo\/gopa\/core\/API\/Proxy.php(244): call_user_func_array() #7 \/var\/www\/matomo\/gopa\/core\/Context.php(28): Piwik\\API\\Proxy->Piwik\\API\\{closure}() #8 \/var\/www\/matomo\/gopa\/core\/API\/Proxy.php(335): Piwik\\Context::executeWithQueryParameters() #9 \/var\/www\/matomo\/gopa\/core\/API\/Request.php(266): Piwik\\API\\Proxy->call() #10 \/var\/www\/matomo\/gopa\/core\/API\/Request.php(559): Piwik\\API\\Request->process() #11 \/var\/www\/matomo\/gopa\/core\/Segment.php(193): Piwik\\API\\Request::processRequest() #12 \/var\/www\/matomo\/gopa\/core\/Segment.php(201): Piwik\\Segment->getAvailableSegments() #13 \/var\/www\/matomo\/gopa\/core\/Segment.php(263): Piwik\\Segment->getSegmentByName() #14 \/var\/www\/matomo\/gopa\/core\/Segment.php(241): Piwik\\Segment->getExpressionsWithUnionsResolved() #15 \/var\/www\/matomo\/gopa\/core\/Segment.php(168): Piwik\\Segment->initializeSegment() #16 \/var\/www\/matomo\/gopa\/core\/ArchiveProcessor.php(681): Piwik\\Segment->__construct() #17 \/var\/www\/matomo\/gopa\/plugins\/Goals\/Archiver.php(121): Piwik\\ArchiveProcessor->processDependentArchive() #18 \/var\/www\/matomo\/gopa\/core\/Plugin\/Archiver.php(89): Piwik\\Plugins\\Goals\\Archiver->aggregateDayReport() #19 \/var\/www\/matomo\/gopa\/core\/ArchiveProcessor\/PluginsArchiver.php(164): Piwik\\Plugin\\Archiver->callAggregateDayReport() #20 \/var\/www\/matomo\/gopa\/core\/ArchiveProcessor\/Loader.php(218): Piwik\\ArchiveProcessor\\PluginsArchiver->callAggregateAllPlugins() #21 \/var\/www\/matomo\/gopa\/core\/ArchiveProcessor\/Loader.php(158): Piwik\\ArchiveProcessor\\Loader->prepareAllPluginsArchive() #22 \/var\/www\/matomo\/gopa\/core\/ArchiveProcessor\/Loader.php(101): Piwik\\ArchiveProcessor\\Loader->prepareArchiveImpl() #23 \/var\/www\/matomo\/gopa\/co ... /var\/www\/matomo\/gopa\/console(32): Symfony\\Component\\Console\\Application->run()\n#48 {main}"}'
I had to deactivate the plugins Goals, Provider and VisitFrequency - then I activated them again and the archive console command now runs without a problem. But the original error when using the web interface still exists.
Ok. that points out, that the problem is triggered here:
https://github.com/matomo-org/matomo/blob/3efc65b31beee69d5bc512893f4ca4eea0ef53c4/plugins/Goals/Archiver.php#L121
So it seems the visitorType
segment produced the problem, which is a bit weird, as that segment actually is defined in CoreHome
plugin and should always be valid. I really don't know why that might happen upon a fresh install. Maybe @tsteur has an idea?
\/var\/www\/matomo\/gopa\/console(32): Symfony\Component\Console\Application->run() #28 {main}, caused by: Segment cannot be added because no sql segment is set #0 \/var\/www\/matomo\/gopa\/plugins\/CustomVariables\/Columns\/Base.php(50): Piwik\Columns\DimensionSegmentFactory->createSegment() #1 \/var\/www\/matomo\/gopa\/plugins\/CustomVariables\/Columns\/CustomVariableName.php(19): Piwik\Plugins\CustomVariables\Columns\Base->configureSegmentsFor() #2
@sgiehl Looking at the stack trace there's a problem here: https://github.com/matomo-org/plugin-CustomVariables/blob/4.0.1/Columns/Base.php#L45-L57
These segments don't define sqlSegment but instead use a union of segments. It seems $this->getSegmentColumns('customVariablePage' . $segmentNameSuffix, $numCustomVariables)
isn't returning any columns and therefore it triggers that exception.
This would happen when for some reason custom variable columns weren't installed.
We should try to reproduce this with a fresh install
Although next release won't include custom variables anymore anyway and then it will be only a plugin on the marketplace. So this might be solved then.
So, is there a way to re-install the instance without CustomVariables?
Oh, I have just deactivated the CustomVariables plugin and the site is now working.
👍 from Matomo 4.6 CustomVariables will also no longer be included by default. I'm glad it's now working @jurgenhaas
Let us know if there's another issue. I'll close it meanwhile as it should be fixed with the next release.
Hi
We have a matomo installation and we are receiving these error messages in the process core:archive
also although we have 1 idsite only, motivated by an error on our part, records were inserted in the db different from idsite = 1 and when running the file, these messages come out
I have delete this record from db tables
matomo_log_*
but the messages still appear and i think this is the reason for the error during the archiving process. How can I clean the db to remove the entire trace of those idsites?