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