Archive data not shown anymore


#1

My piwik 1.7.1 installation shows no archived data anymore. I can only see reports for January and February which are still in the piwik_log_-Tables.

Data from the piwik_archive_blob_ and piwik_archive_numeric-Tables isn’t shown anymore. This worked fine for me till Piwik 1.6. With the update to Piwik 1.6 or shortly thereafter i missed the archived reports. I updated my Piwik to 1.7 and 1.7.1 but didn’t get my archived reports shown.

I have cron/archive.sh running and deleted the older entries from piwik_log_visit and piwik_log_link_visit_action every month. The piwik option to purge old visits logs is not activated (it wasn’t yet available when i installed piwik the first time).

The old archive blob and archive numeric tables since 2010 still exist and contain data. Deleting and recreating them isn’t an option as i already purged the old entries from the piwik_log_-Tables.

How do i get piwik to show the data from the archive Tables again?


(Matthieu Aubry) #2

Have you made any other changes or simply upgraded? It should definitely display these stats. I can’t think of an obvious reason now, but let meknow if you have anything special about your use case?


#3

I noticed the missing stats when i installed the GeoIPMap plugin. But deactivating this plugin (and the GeoIP plugin) didn’t bring the stats back.


#4

I activated the sql profiler using the enable_sql_profiler = 1 option in config.ini.php. But all it shows on the main dashboard is:


Breakdown by query
Executed 4 times in 3,1ms (average = 0,8ms)
	SELECT language FROM piwik_user_language WHERE login = ? 
Executed 1 time in 1,5ms
	connect
Executed 2 times in 1,2ms (average = 0,6ms)
	SELECT * FROM piwik_site WHERE idsite IN (1) ORDER BY idsite ASC LIMIT 10
Executed 2 times in 1,1ms (average = 0,6ms)
	SELECT count(*) FROM piwik_user WHERE login = ?
Executed 1 time in 0,4ms
	SELECT option_value, option_name FROM `piwik_option` WHERE autoload = 1
Executed 1 time in 0,3ms
	SELECT * FROM piwik_site WHERE idsite = ?
Executed 1 time in 0,3ms
	SELECT * FROM piwik_goal WHERE idsite IN (1) AND deleted = 0
Executed 1 time in 0,2ms
	SELECT layout FROM piwik_user_dashboard WHERE login = ? AND iddashboard = ?
Executed 1 time in 0,2ms
	SELECT option_value FROM `piwik_option`WHERE option_name = ?
Executed 1 time in 0,2ms
	SELECT idsite FROM piwik_site

I can’t find the selects which fetch the data. Is it possible to see them using the sql_profiler or do i have to look in the piwik source code for them?


(Matthieu Aubry) #5

The dashboard does not select data, but widgets do. Open a widget with Firebug to see the sql ?


#6

I have Firebug installed, but how do i open a widget with it?


(Matthieu Aubry) #7

open the dashboard. In Firebug > Net panel you will the requests to each widget. Open in a new tab and you can debug there


#8

thanks, matt. I looked at the visits summary widget using this url:
piwik//index.php?module=VisitsSummary&action=getSparklines&viewDataTable[]=VisitsSummary&viewDataTable[]=getSparklines&filter_limit=10&widget=1&idSite=1&period=month&date=2011-11-15&debug

The widget works with date “2012-01-15”. But for “2011-11-15”, where no entries exist in the piwik_log-Tables, i only get 0 as result for every stat.

The sql profiler shows 32 executions of the following query:


SELECT idarchive, value, name, date1 as startDate FROM piwik_archive_numeric_2011_11 WHERE idsite = ? AND date1 = ? AND date2 = ? AND period = ? AND ( (name = 'done' AND value = 1) OR (name = 'done' AND value = 3) OR name = 'nb_visits') AND ts_archived >= ? ORDER BY ts_archived DESC

I tested the query on my mysql database but i’m not sure about the values:


SELECT idarchive, value, name, date1 AS startDate FROM piwik_archive_numeric_2011_11
WHERE idsite =1
AND date1 = '2011-11-01' AND date2 = '2011-11-30' AND period =3
AND (
(
name = 'done'
AND value =1
)
OR (
name = 'done'
AND value =3
)
OR name = 'nb_visits'
)
AND ts_archived >= '2011-11-01 00:00:00'
ORDER BY ts_archived DESC

This gave me the following database result:

idarchive: 490
value: 1
name: done
startDate: 2011-11-01

idarchive: 451
value: 1
name: done
startDate: 2011-11-01

idarchive: 451
value:
name: nb_visits
startDate: 2011-11-01

Compared to the output for January i seem to miss the nb_visits for idarchive 490.


(Matthieu Aubry) #9

Thanks for investigating.
I have fixed a bug in archive.php that could cause this problem

Please try the following:

if you still see the problem then there is another bug, let me know here

EDIT: linking to the right version of archive.php


#10

I tried the archive.php from the SVN Repository but without success. After 48 hours i still can’t see the archived data.

When i first noticed the problem i still used the old archive.sh. I changed the cron job to archive.php later.


(Matthieu Aubry) #11

OK it appears there is a problem, another user is experiencing it too., Please email me at matt@piwik.org so I can follow up on this!


#12

I’m experiencing the same problem:

  • archive tables do have entries and also actualize(!), therefore tracking seems to work
  • (log_visits always contains one old, unprocessed entry)
  • nothing shows up in the reports.

additionally, the “play” button for real time statistics doesn’t work.

would like to see this thread growing.

environment:
plesk 10.4.4 on ubuntu 10.04 LTS, VPS