Visits Log returned "The date '1970-01-01' is a date before first website was online" #215
Comments
|
Thanks @oxcid I can't reproduce this here and we have also not heard of a similar issue from other On-Premise users. Could you maybe post the content of your Matomo => System report here? Can you also in general still reproduce this issue? |
|
Hi @tsteur , I think I'm on to something here.. My initial report, the problem occurs for February 15-16, but today, the problem occurs for February 15, and 17. February 16 is displayed perfectly. Also I noticed the problem for date 15 and 17 is gone when I set the "Rows to display" at the bottom to 25, only to reappear when I try to go to the next page (or several next pages). Is it possible that there's a corrupted or invalid row? How to make sure of this? And may I know where do you trigger the error message above? I want to understand which event caused that, and maybe to pinpoint which field uses the timestamp value stated in the error message. EDIT: I've found out this is called from Date::factory. Still, I don't know where it got the $datestring value "2020". For now I assume some rows have an invalid date/time value, which only consists of "2020". System report: ` # Matomo
Endpoints
Crons
Mandatory checks
Optional checks
Matomo Settings
Logs
WordPress
WordPress PluginsMU Plugins
Plugins
Server
Database
Browser
|
|
Hi @tsteur , I found the invalid rows, it's in table "wp_matomo_log_conversion" and "wp_matomo_log_conversion_item", field server_time.
And here's a comparison between the server_time and the visit first & last action time.
During those times, there are no PHP errors, and no server downtime etc. I checked the users from 2 of those rows as examples, there are no similarities between the users. 1 is using desktop, Win10, Firefox, while the other is using mobile, iOS, Safari. When I changed the server_time in table wp_matomo_log_conversion to a time between visit first and last action time, the problem is gone. Nevertheless I also changed the server_time in table wp_matomo_log_conversion_item. Until now, I don't know what caused the wrong server time at the first place. On 4 separate visits, at different dates, all server_time has the exact same value. I hope this is just a one-time glitch, but is there a way you can add a validation for the server_time field? Or at least throws an error directly when it happens? That way we know what's causing this. Thanks. PS: Out of topic, would you mind to take a look at this? Thanks. |
refs matomo-org/matomo-for-wordpress#215 see matomo-org/matomo-for-wordpress#215 (comment) There should be no harm in this logic. Basically the last visit time is the current timestamp anyway. It be actually probably even more accurate to use the current timestamp there in general. Eg this could happen if an exception was triggered in https://github.com/matomo-org/matomo/blob/3.13.2/core/Tracker/Visit.php#L192-L194 where the visitor was not found for some reason when updating, or when no value changed I suppose because it was updated within a few ms. Another possibility that may be better be to set the `visit_last_action_time ` https://github.com/matomo-org/matomo/blob/3.13.2/core/Tracker/Visit.php#L249 at the beginning of that function or so. Not sure if there is maybe a reason it's set at the end. Or maybe it could be at least moved up one line before `updateExistingVisit` https://github.com/matomo-org/matomo/blob/3.13.2/core/Tracker/Visit.php#L247 so if it triggers an exception then we still have surely a value set. Not sure if it would fix that particular error though. Can't really think of a different way though how this would happen so far. I was able to trigger the VisitorNotFoundInDb exception when no value changed
|
Cheers for debugging this @oxcid I have created a PR in matomo-org/matomo#15587 that will hopefully fix this. |
|
Thanks @tsteur , really appreciate it! Just to update, this morning I got another invalid server_time value, exactly like the above. For now I'll fix these manually, no worries. This has never happened when I was still using the beta version (0.4 if I remember correctly), so anything causing this must be an update after that. Thanks. |
|
Cheers for letting us know 👍 I've had a look again and I have kind of an idea what it is related to. Created #217 and we're also generally looking for some additional fix in matomo-org/matomo#15587 on top of the already done fix. |
|
Closing this for now @oxcid I'm hoping it'll be fixed from the next release. Feel free to comment otherwise and I'll be happy to have another look |
* Ensure a valid timestamp is set when recording a conversion refs matomo-org/matomo-for-wordpress#215 see matomo-org/matomo-for-wordpress#215 (comment) There should be no harm in this logic. Basically the last visit time is the current timestamp anyway. It be actually probably even more accurate to use the current timestamp there in general. Eg this could happen if an exception was triggered in https://github.com/matomo-org/matomo/blob/3.13.2/core/Tracker/Visit.php#L192-L194 where the visitor was not found for some reason when updating, or when no value changed I suppose because it was updated within a few ms. Another possibility that may be better be to set the `visit_last_action_time ` https://github.com/matomo-org/matomo/blob/3.13.2/core/Tracker/Visit.php#L249 at the beginning of that function or so. Not sure if there is maybe a reason it's set at the end. Or maybe it could be at least moved up one line before `updateExistingVisit` https://github.com/matomo-org/matomo/blob/3.13.2/core/Tracker/Visit.php#L247 so if it triggers an exception then we still have surely a value set. Not sure if it would fix that particular error though. Can't really think of a different way though how this would happen so far. I was able to trigger the VisitorNotFoundInDb exception when no value changed * throw exception for visit not found only if visit has really not found
|
Hi @tsteur , I've updated to Matomo ver 1.0.3 probably 2 days ago, but last night this error comes up again, still exactly the same as before. Can you please have another look at this? Thanks. |
|
@oxcid I've been looking through the code and debugging for quite some time but have no idea how this could potentially happen. Can you double check it happened again in the log_conversion issue and it was indeed a visit that was tracked after the update? |
|
Hi @tsteur , I can confirm it's indeed the log_conversion issue again, with the exact same value "1970-01-01 00:33:40", and tracked after the plugin update. I've traced the visit log based on the action_time and HEX(idvisitor), here's the log:
EDIT: There is time difference between the log in database and the report, since the database is using GMT, while the report is using GMT+7 (my local time). Not sure if this info can make a difference or not. |
|
Hi @tsteur , just to update, there are no more invalid rows right now, 4 days after my last reply. So I'd guess it's just a cache issue back then. Thanks again, I'll let you know in case this issue is back. |
|
Awesome, thanks. Feel free to comment any time if it happens again. |
|
Hi @tsteur , I've thought this issue as solved, so I didn't check for a few days. But sadly, it does still occurs.. Please find the SQL queries below. I'm thinking.. could a cache mechanism cause this? I'm not sure how though..? Thanks.
|
|
Hi @tsteur , I've created some SQL triggers, before INSERT and UPDATE, for those tables (wp_matomo_log_conversion, wp_matomo_log_conversion_item). First purpose is to log which event causes the invalid rows, and second purpose is to fix those invalid rows, so I won't have to do it manually everyday. Here are the triggers (DATE_SUB is to calculate my local time zone difference with UTC): And after a few days, here are the log tables: wp_matomo_log_conversion (so far I see only UPDATE queries causing invalid rows here)
wp_matomo_log_conversion_item
Hope this can help you fix this issue. Thanks. |
|
Thanks for this that's definitely helping. Sorry about replying to late. I'll try another patch in the next release which is today or tmrw. It might not help though. If that doesn't work though I will try debug again. I'm thinking it might be related to some MySQL setting maybe or so but can't really imagine as it only happens in the log conversion tables but doesn't seem to happen in wp_matomo_log_visit. |
|
BTW can you again confirm this is only happening for some entries but not for all? Wonder if maybe all inserts work but all updates fail or so. Probably not too easy to find out. |
|
And one last thing: What is the output of |
@tsteur Confirmed, only some, and not that many actually. That's why I created the log table, just to find out which ones having invalid values. One thing you may also have noticed, currently we're in year 2020, and in timestamps term, '2020' = '1970-01-01 00:33:40'. Thought maybe there's an issue somewhere during the dates parsing, and only taken the year value? For table wp_matomo_log_conversion, only updates gives the invalid values, while for table wp_matomo_log_conversion_item, updates and inserts gives invalid values. Here's the output of Thanks. |
I actually did not notice that. so it uses always Almost seems like some PHP bug but I'm doubting that is the case (if so, it likely be in |
|
@oxcid looked again into things and likely the I have otherwise another idea but be good to see if that works. Looking through the code I have not yet seen though where this happens. |
|
@oxcid be great to let me know if you could give it a try. |
|
@tsteur Sorry for the late reply, been hectic lately on my side. I probably skipped a few updates, but I noticed since I updated to version 1.0.5 (since last Friday), my trigger events don't catch anything that needed to be fixed anymore. So any fix you've implemented around that version definitely works! Thanks so much! |
|
Awesome, great to hear and thanks for all your help @oxcid |
Hi,
Starting this evening, when I tried to open Visits Log, or Ecommerce Log, and selecting date February 15-16, I get this error:
This is the URL:
https://example.com/wp-content/plugins/matomo/app/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday#?idSite=1&period=day&date=2020-02-15&segment=&category=General_Visitors&subcategory=Live_VisitorLogAlso I checked at MySQL log, this is the query executed:
SELECT log_visit.* FROM wp_matomo_log_visit AS log_visit WHERE log_visit.idsite in ('1') AND log_visit.visit_last_action_time >= '2020-02-14 17:00:00' AND log_visit.visit_last_action_time <= '2020-02-15 20:34:43' ORDER BY log_visit.idsite DESC, log_visit.visit_last_action_time DESC LIMIT 0, 102So I see the dates value are correct (timezone GMT+7), but I got the error above. Other dates from February 14 backward work fine. Other pages also work fine, including Real-time, it keeps tracking visits.
No PHP error at the logs.
Thanks.
The text was updated successfully, but these errors were encountered: