@vmalaga opened this Issue on November 29th 2019

Hi

We have a matomo installation and we are receiving these error messages in the process core:archive

INFO [2019-11-29 12:05:45] 27082  - pre-processing all visits
ERROR [2019-11-29 12:06:06] 27082  Got invalid response from API request: ?module=API&method=API.get&idSite=1&period=day&date=last1000&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:11557:"Segment cannot be added because no sql segment is set - in plugin Goals <a href='/0'>#0</a> /var/www/html/matomo/core/ArchiveProcessor/Loader.php(127): Piwik\ArchiveProcessor\PluginsArchiver-&gt;callAggregateAllPlugins('25250', '0', false) <a href='/1'>#1</a> /var/www/html/matomo/core/ArchiveProcessor/Loader.php(77): Piwik\ArchiveProcessor\Loader-&gt;prepareAllPluginsArchive('25250', '0') <a href='/2'>#2</a> /var/www/html/matomo/core/ArchiveProcessor/Loader.php(63): Piwik\ArchiveProcessor\Loader-&gt;prepareArchiveImpl('VisitsSummary') <a href='/3'>#3</a> /var/www/html/matomo/core/Context.php(75): Piwik\ArchiveProcessor\Loader-&gt;Piwik\ArchiveProcessor\{closure}() <a href='/4'>#4</a> /var/www/html/matomo/core/ArchiveProcessor/Loader.php(64): Piwik\Context::changeIdSite(1, Object(Closure)) <a href='/5'>#5</a> /var/www/html/matomo/core/Archive.php(867): Piwik\ArchiveProcessor\Loader-&gt;prepareArchive('VisitsSummary') <a href='/6'>#6</a> /var/www/html/matomo/core/Archive.php(677): Piwik\Archive-&gt;prepareArchive(Array, Object(Piwik\Site), Object(Piwik\Period\Day)) <a href='/7'>#7</a> /var/www/html/matomo/core/Archive.php(620): Piwik\Archive-&gt;cacheArchiveIdsAfterLaunching(Array, Array) <a href='/8'>#8</a> /var/www/html/matomo/core/Archive.php(549): Piwik\Archive-&gt;getArchiveIds(Array) <a href='/9'>#9</a> /var/www/html/matomo/core/Archive.php(303): Piwik\Archive-&gt;get(Array, 'numeric') <a href='/10'>#10</a> /var/www/html/matomo/plugins/VisitsSummary/API.php(36): Piwik\Archive-&gt;getDataTableFromNumeric(Array) <a href='/11'>#11</a> [internal function]: Piwik\Plugins\VisitsSummary\API-&gt;get('1', 'day', 'last1000', false, Array) <a href='/12'>#12</a> /var/www/html/matomo/core/API/Proxy.php(237): call_user_func_array(Array, Array) <a href='/13'>#13</a> /var/www/html/matomo/core/Context.php(28): Piwik\API\Proxy-&gt;Piwik\API\{closure}() <a href='/14'>#14</a> /var/www/html/matomo/core/API/Proxy.php(328): Piwik\Context::executeWithQueryParameters(Array, Object(Closure)) <a href='/15'>#15</a> /var/www/html/matomo/plugins/API/API.php(435): Piwik\API\Proxy-&gt;call('\\Piwik\\Plugins\\...', 'get', Array) <a href='/16'>#16</a> [internal function]: Piwik\Plugins\API\API-&gt;get('1', 'day', 'last1000', false, Array) <a href='/17'>#17</a> /var/www/html/matomo/core/API/Proxy.php(237): call_user_func_array(Array, Array) <a href='/18'>#18</a> /var/www/html/matomo/core/Context.php(28): Piwik\API\Proxy-&gt;Piwik\API\{closure}() <a href='/19'>#19</a> /var/www/html/matomo/core/API/Proxy.php(328): Piwik\Context::executeWithQueryParameters(Array, Object(Closure)) <a href='/20'>#20</a> /var/www/html/matomo/core/API/Request.php(265): Piwik\API\Proxy-&gt;call('\\Piwik\\Plugins\\...', 'get', Array) <a href='/21'>#21</a> /var/www/html/matomo/plugins/API/Controller.php(41): Piwik\API\Request-&gt;process() <a href='/22'>#22</a> [internal function]: Piwik\Plugins\API\Controller-&gt;index() <a href='/23'>#23</a> /var/www/html/matomo/core/FrontController.php(589): call_user_func_array(Array, Array) <a href='/24'>#24</a> /var/www/html/matomo/core/FrontController.php(165): Piwik\FrontController-&gt;doDispatch('API', false, Array) <a href='/25'>#25</a> /var/www/html/matomo/core/dispatch.php(34): Piwik\FrontController-&gt;dispatch() <a href='/26'>#26</a> /var/www/html/matomo/index.php(27): require_once('/var/www/html/m...') <a href='/27'>#27</a> /var/www/html/matomo/core/CliMulti/RequestCommand.php(79): require_once('/var/www/html/m...') <a href='/28'>#28</a> /var/www/html/matomo/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(257): Piwik\CliMulti\RequestCommand-&gt;execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/29'>#29</a> /var/www/html/matomo/vendor/symfony/console/Symfony/Component/Console/Application.php(874): Symfony\Component\Console\Command\Command-&gt;run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/30'>#30</a> /var/www/html/matomo/vendor/symfony/console/Symfony/Component/Console/Application.php(195): Symfony\Component\Console\Application-&gt;doRunCommand(Object(Piwik\CliMulti\RequestCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/31'>#31</a> [internal function]: Symfony\Component\Console\Application-&gt;doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/32'>#32</a> /var/www/html/matomo/core/Console.php(103): call_user_func(Array, Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/33'>#33</a> /var/www/html/matomo/core/Access.php(639): Piwik\Console-&gt;Piwik\{closure}() <a href='/34'>#34</a> /var/www/html/matomo/core/Console.php(104): Piwik\Access::doAsSuperUser(Object(Closure)) <a href='/35'>#35</a> /var/www/html/matomo/vendor/symfony/console/Symfony/Component/Console/Application.php(126): Piwik\Console-&gt;doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/36'>#36</a> /var/www/html/matomo/console(27): Symfony\Component\Console\Application-&gt;run() <a href='/37'>#37</a> {main}, caused by: Segment cannot be added because no sql segment is set <a href='/0'>#0</a> /var/www/html/matomo/plugins/CustomVariables/Columns/Base.php(27): Piwik\Columns\Dimension-&gt;addSegment(Object(Piwik\Plugins\CustomVariables\Segment)) <a href='/1'>#1</a> /var/www/html/matomo/plugins/CustomVariables/Columns/CustomVariableName.php(17): Piwik\Plugins\CustomVariables\Columns\Base-&gt;configureSegmentsFor('Name') <a href='/2'>#2</a> /var/www/html/matomo/core/Columns/Dimension.php(646): Piwik\Plugins\CustomVariables\Columns\CustomVariableName-&gt;configureSegments() <a href='/3'>#3</a> /var/www/html/matomo/plugins/API/SegmentMetadata.php(48): Piwik\Columns\Dimension-&gt;getSegments() <a href='/4'>#4</a> /var/www/html/matomo/plugins/API/API.php(205): Piwik\Plugins\API\SegmentMetadata-&gt;getSegmentsMetadata(Array, '0', true, '1') <a href='/5'>#5</a> [internal function]: Piwik\Plugins\API\API-&gt;getSegmentsMetadata(Array, '0', '1') <a href='/6'>#6</a> /var/www/html/matomo/core/API/Proxy.php(237): call_user_func_array(Array, Array) <a href='/7'>#7</a> /var/www/html/matomo/core/Context.php(28): Piwik\API\Proxy-&gt;Piwik\API\{closure}() <a href='/8'>#8</a> /var/www/html/matomo/core/API/Proxy.php(328): Piwik\Context::executeWithQueryParameters(Array, Object(Closure)) <a href='/9'>#9</a> /var/www/html/matomo/core/API/Request.
ERROR [2019-11-29 12:06:06] 27082  Empty or invalid response '' for website id 1, Time elapsed: 20.488s, skipping

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

INFO [2019-11-29 12:05:45] 27082  - Will invalidate archived reports for 2019-11-29 for following websites ids: 1
INFO [2019-11-29 12:05:45] 27082  - Will invalidate archived reports for 2019-11-19 for following websites ids: 34333,36098
INFO [2019-11-29 12:05:45] 27082  Failed to invalidate archived reports: An unexpected website was found in the request: website id was set to '34333' .
INFO [2019-11-29 12:05:45] 27082  - Will invalidate archived reports for 2019-11-20 for following websites ids: 34333,36337
INFO [2019-11-29 12:05:45] 27082  Failed to invalidate archived reports: An unexpected website was found in the request: website id was set to '34333' .
INFO [2019-11-29 12:05:45] 27082  - Will invalidate archived reports for 2019-11-23 for following websites ids: 34333,36113,36337,36418
INFO [2019-11-29 12:05:45] 27082  Failed to invalidate archived reports: An unexpected website was found in the request: website id was set to '34333' .
INFO [2019-11-29 12:05:45] 27082  - Will invalidate archived reports for 2019-11-21 for following websites ids: 34842,35399,36054,36098
INFO [2019-11-29 12:05:45] 27082  Failed to invalidate archived reports: An unexpected website was found in the request: website id was set to '34842' .
INFO [2019-11-29 12:05:45] 27082  - Will invalidate archived reports for 2019-11-22 for following websites ids: 35111,36337,36492
INFO [2019-11-29 12:05:45] 27082  Failed to invalidate archived reports: An unexpected website was found in the request: website id was set to '35111' .
INFO [2019-11-29 12:05:45] 27082  - Will invalidate archived reports for 2019-11-24 for following websites ids: 35183,36337
INFO [2019-11-29 12:05:45] 27082  Failed to invalidate archived reports: An unexpected website was found in the request: website id was set to '35183' .
INFO [2019-11-29 12:05:45] 27082  - Will invalidate archived reports for 2019-11-18 for following websites ids: 35223,36113,36115,36190,36307,36337
INFO [2019-11-29 12:05:45] 27082  Failed to invalidate archived reports: An unexpected website was found in the request: website id was set to '35223' .
INFO [2019-11-29 12:05:45] 27082  - Will invalidate archived reports for 2019-11-27 for following websites ids: 35399
INFO [2019-11-29 12:05:45] 27082  Failed to invalidate archived reports: An unexpected website was found in the request: website id was set to '35399' .

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?

@tsteur commented on November 29th 2019 Member

@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?

@vmalaga commented on December 1st 2019

@tsteur this is the content of matomo_segment table

mysql> select definition from matomo_segment;
+---------------------------------+
| definition                      |
+---------------------------------+
| deviceType==smartphone          |                                                                                                                                                          
| dimension1=<a class='mention' href='https://github.com/www'>@www</a>.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=<a class='mention' href='https://github.com/qdq'>@qdq</a>                   |                                                                                                                                                          
| 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)
@tsteur commented on December 3rd 2019 Member

@vmalaga just checking are you using the latest version of Matomo? Did this happen after an update?

@tsteur commented on December 3rd 2019 Member

Any chance you deleted a custom dimension recently maybe or made changes to it?

@vmalaga commented on December 4th 2019

@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

@vmalaga commented on December 4th 2019

@tsteur What is the reason for not having foreign keys in the tables that use idsite refering to matomo_site.idsite ?

@tsteur commented on December 5th 2019 Member

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

@vmalaga commented on December 9th 2019

Hi @tsteur no the issue has not been fixed, even deleting all records with idsite != 1

@tsteur commented on December 11th 2019 Member

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?

Powered by GitHub Issue Mirror