Statistics zeroing after manual DB update


#1

Hi All,

We’ve had this issue which even paid piwik support wasn’t able to shed any light into, so I’ll try my luck here.

We’re using 2 custom variables when recording a hit, as it happened, due to our mistake, Piwik recorded those custom variables with some requests only. It happened probably due to race condition. We fixed our tracking code and the custom variables started recording properly for all visitors. So far so good.

Now we wanted to update the previous hits with the custom variables so the data were correct retrospectively. A guy from support suggested this:
“For the past visits, you can update the custom variables directly in the database (in log_visit table) & drop the archive tables (archive_*) to force Piwik to re-process the data taking into account changes that you did in the log_visit table”

So I created this script that looks like this: (~18k records)
update piwik_log_visit set custom_var_k1 = ‘campaign’, custom_var_v1 = ‘309’, custom_var_k2=‘press_release’, custom_var_v2 = ‘578’ where idvisit=3019;

after running it (without errors or warnings) and deleting the archive tables (both numeric and blob) the visitor log indeed showed the custom variables properly for all visits - so that seems like it was fixed.

However the Visits Overview widget and any other widget suddenly started showing zero visits. When I apply the custom segment filter, they appear again, but without it, they’re zero.

So I have 2 questions:

  1. Does anyone have an idea why they were zeros?
  2. What is the correct way of adding the custom variables retrospectively?

EDIT: this issue is the same in old 1.x piwik and also when I upgraded to the newest 2.x version

Thanks, Michal


#3

Hi Matt, it was holub.michal@gmail.com. My boss Salvatore (salvatore@isebox.net) also spoke to Piwik. Thanks for looking at this issue. If you are interested, I can provide a full DB dump + my SQL update script.

Michal


#4

Hi Matt, did you have any luck with the issue? We’re still missing a lot of records for our customers and would like to somehow get them back.

Thanks, Michal


(Matthieu Aubry) #5

Hi there, I couldnt find the email exchange where you asked for help. Can you please forward to matt@piwik.org?


#6

Hi,

I have quite a similar problem, I share it here because possibly it adds additional info…
Everything worked correcly for me with 2.0.2., then I defined custom variables.
first they worked correctly but days later I found 5+ times that custom variable stats were shown wrongly:
under Custom Variable 1 eg. CV2’s value’s stats were shown, under CV2 the values of CV3, etc.
And finally from 10th Jan. around midday my widget report data is not refreshed!
( That day was first time website had more than 10k visits / day )
I see that I got the Visitors in Real-time data, but I got 0 values on all graphs and I got error message in widgets: "There is no data for this report. "
I tried the modifications suggested:

  • in General settings I increased report creation period from default 10 sec to 3600 sec and set up a cron job via CPanel for report processing
  • in Privacy I checked whether I should delete visitor logs or old reports. It showed it’s not necessary.

I still got the error, realtime visitor data shown correctly but reports show zeros.
Upgrade to 2.0.3 didn’t solve the problem.

Thanks,
A


#7

Hi Venetreg,

Matt from Piwik agreed to take a look at this issue. I’m compiling a test case for him as we speak…so I hope he’ll tell us what is going on.

M


#8

I checked at my company and cron job archiving works properly but I still don’t have report data.


#9

This has been finally resolved for us. Our problem was that we were comparing the stats IMMEDIATELY after we ran the script - that didn’t leave cron enough time to do his magic. When we turned on the feature to calculate archives after each page hit the right numbers popped up.

M


#10

Could you exactly explain what and where did you turn on?
Thanks,
A


#11

Check out Browser trigger archiving in general settings


#12

I use hourly cron job, so I use just the other setting, but thank you for the answer!

For the others facing this problem: I have a shared hosting account and using my tracking domain as an addon domain.
Hosting logs showed cron job completed correctly but it was not in reality!
My cron job hasn’t completed correctly since my output path was incorrect ( too short backward to home ), so I recommend to triplecheck that.

So this way I got correct report data now :slight_smile: