@eramirezprotec opened this Issue on November 6th 2017 Contributor

Hi! One of my websites has the Canary timezone. It was working great until the time change from summer to winter time. When this happened, all the data shows perfectly until the beginning of the next day, at that moment all metrics and reports from the day before are set to zero in all widgets.

In summary: the current day shows all reports and metrics correctly. When the next day starts, all data from the day before is set to zero. And Piwik keeps this behavior every day.

I checked the database (piwik_archive_numeric_2017_XX) and I believe that Piwik is building the last archive of the day without calculating any metric or report, so, it says “done” and it’s set to 1 (definitive, not temporarily or invalid or with errors) but there is nothing calculated at that moment.

My workaround has been to set the “done” register to 2 or 4 and force Piwik to archive the site for that day. But obviously, there has to be a better way to solve this issue.

I'll show images from today (2017-11-06) and yesterday (2017-11-05):

metricstozero_1_

metricstozero_2_

metricstozero_3_

metricstozero_4_

metricstozero_5_

This is the query made to get the results from the last image: _select * from piwik_archive_numeric_2017_11 where idsite=22 and period=1 and date1='2017-11-05' and date2='2017-11-05' order by tsarchived desc;

@eramirezprotec commented on November 21st 2017 Contributor

@sgiehl this keeps happening every day... am I the only one who has reported this issue? I saw a few tickets mentioning something similar, but they are all marked as "solved". This is a constant problem in my Piwik installation, there has to be a bug somewhere. I can give you access to the server and DB.

@sgiehl commented on November 21st 2017 Member

So if that happens "every day" it seems not to be an time zone issue, right? Are you using browser archiving or do you have set up a cron to archive?

@eramirezprotec commented on November 21st 2017 Contributor

I say that it could be some issue related to the timezone because it started happening when the time changed from summer to winter time. I am using a cron to archive.

@eramirezprotec commented on January 18th 2018 Contributor

@sgiehl This is still happening. The curious thing is that all metrics and reports go to zero at the end of the day except my own. I have a few plugins that calculate new metrics and reports. Those metrics and reports get aggregated and archived and Matomo shows them correctly.

I don't do anything special in my plugins, I just use the Archiver class and its two methods: aggregateDayReport and aggregateMultipleReports; inside those methods, I do some queries and calculate the metrics and reports values. Then, I use insertBlobRecord and insertNumericRecords.

Is this information helpful? Is there a chance that my plugins are interfering with Matomo normal archiving process?

Important: The problem is not happening every day, it happens once a week on a random day. No pattern is being followed, at least not one that I know about.

Thank you very much for your help.

@sgiehl commented on January 18th 2018 Member

Does everything work correctly when you disable your plugin?
Is the archiver of your plugin triggered within the normal archiving process, or do you trigger it within a cron or similar?

@tsteur commented on January 18th 2018 Member

if it started happening since summer/winter time it could be a possible timezone issue. Would it be possible that you set the timezone just for a while to the corresponding UTC+/-X hours entry?

@eramirezprotec commented on January 19th 2018 Contributor

@sgiehl,

  • Right now I can't disable my plugins because my clients would complain if they don't see the reports and metrics generated by the plugins. Same plugins were working perfectly before the summer to winter time change.

  • The archiver is being triggered within the normal archiving process. I use a cron:

5 apache /php/bin/php /apachepwk/htdocs/analytics/console core:archive --skip-all-segments >> /apachepwklogs/piwik-archive1.logdate +\%d-\%m-\%Y 2>&1
5 apache /php/bin/php /apachepwk/htdocs/analytics/console core:archive --skip-all-segments >> /apachepwklogs/piwik-archive2.log
date +\%d-\%m-\%Y 2>&1
5 apache /php/bin/php /apachepwk/htdocs/analytics/console core:archive --skip-all-segments >> /apachepwklogs/piwik-archive3.logdate +\%d-\%m-\%Y 2>&1
5 apache /php/bin/php /apachepwk/htdocs/analytics/console core:archive --skip-all-segments >> /apachepwklogs/piwik-archive4.log
date +\%d-\%m-\%Y 2>&1

@tsteur,

  • Yes, it started happening just after the timezone changed. It could be a coincidence, but it does not seem like it.

  • I can do that. I'm changing it right now.

Extra information:

  • Missing metrics and reports started happening to one of my websites (the one described in the first post), but now it is happening to other sites as well. The last one (and most affected lately) has Madrid timezone (UTC +1). The next images show the last drop down occurred on January 16:

last_visits_

pages_

  • The log files for January 16 and 17 show these errors:

INFO [2018-01-16 02:03:09] SUMMARY OF ERRORS
INFO [2018-01-16 02:03:09] Error: Got invalid response from API request: ?module=API&method=API.get&idSite=13&period=day&date=last2&format=php&trigger=archivephp. The response was empty. This usually means a server error. A solution to this error is generally to increase the value of 'memory_limit' in your php.ini file. For more information and the error message please check in your PHP CLI error log file. As this core:archive command triggers PHP processes over the CLI, you can find where PHP CLI logs are stored by running this command: php -i | grep error_log
INFO [2018-01-16 02:03:09] Error: Empty or invalid response '' for website id 13, Time elapsed: 7.839s, skipping
ERROR [2018-01-16 02:03:09] 2 total errors during this script execution, please investigate and try and fix these errors.

INFO [2018-01-17 10:33:18] SUMMARY OF ERRORS
INFO [2018-01-17 10:33:18] Error: Got invalid response from API request: ?module=API&method=API.get&idSite=13&period=day&date=last2&format=php&trigger=archivephp. The response was empty. This usually means a server error. A solution to this error is generally to increase the value of 'memory_limit' in your php.ini file. For more information and the error message please check in your PHP CLI error log file. As this core:archive command triggers PHP processes over the CLI, you can find where PHP CLI logs are stored by running this command: php -i | grep error_log
INFO [2018-01-17 10:33:18] Error: Empty or invalid response '' for website id 13, Time elapsed: 17.911s, skipping
ERROR [2018-01-17 10:33:18] 2 total errors during this script execution, please investigate and try and fix these errors.

There were no more errors in those two days. And I don't think it is related because the problem happens when the last archive process of the day occurs, that's when reports and metrics (except my own) are not being saved in the DB.

  • Database queries and results (showing all archives for January 16):
  1. select * from piwik_archive_numeric_2018_01 where idsite=13 and period=1 order by date1 desc, ts_archived desc;
    db_1_

  2. select * from piwik_archive_blob_2018_01 where idsite=13 and period=1 order by date1 desc, ts_archived desc;
    db_2_

  3. select * from piwik_archive_numeric_2018_01 where idsite=13 and idarchive=490315; (Last idarchive of the day with done = 1)
    db_3_
    These results are shown in the first image of this post (Last visits evolution graph).

  4. select * from piwik_archive_blob_2018_01 where idsite=13 and idarchive=490315;
    db_4_
    These reports are the only ones existing for January 16.

Thank you very much for your help, I know you don't have much time to invest in these "not common" issues. I'm trying to find out what's happening but with no luck yet. I've seen a few tickets describing the same problem, but they are all solved and it was using older versions of Matomo. My version is 3.2.0.

@eramirezprotec commented on January 30th 2018 Contributor

@tsteur, setting the timezone to the corresponding UTC+/-X hours entry didn't work.

I ended up creating a plugin that detects the "metrics and reports to zero" problem and invalidates all the archived stuff for that period and date. That way everything is recalculated when the archiving process runs again. Thank you for your help, I hope we can find a real solution in the future. For now, this is a workaround.

@tsteur commented on January 30th 2018 Member

@eramirezprotec thanks for letting me know re timezone. It would be good indeed to find a real solution. Those things are hard to find out. I don't think I've ever heard of a similar problem so wondering if it might be memory related or so but it seems to work when you invalidate and recalculate so this shouldn't be the problem. Did you find anything in the web server logs? I think so far there are only entries from Matomo log.

Powered by GitHub Issue Mirror