Archiving old piwik_archive_blob_YYYY_MM tables

Support & Bugs
1

I have archive.sh set up, and it works fine. However, on a few previous months, it failed to complete this task. So I have a couple of months (eg archive_blob_2008_06) which are 700M while the rest of them are 10 - 20M

How do I ask Piwik (nicely) to go back and archive this old data?

I’m using Piwik SVN r836 currently.

Thanks!

2

this happens because before july/august the out of date records were not deleted from the DB.
you can run

DELETE
FROM archive_blob_2008_06
WHERE date1 = DATE(ts_archived)
AND DATE(ts_archived) <> CURRENT_DATE()

that should delete the right out of date records.
[size=14pt]PS: backup these tables first!!![/size]

3

thanks!

this matches no rows, though. before running the delete, i did a scan to see how many rows it would match. there are none.

mysql> select count(*) from piwik_archive_blob_2008_06 where date1 = DATE(ts_archived) AND DATE(ts_archived) <> CURRENT_DATE();
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (14.15 sec)

but the table is still quite big …

mysql> show table status where Name like "piwik_archive_blob_2008_06";
+----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name                       | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| piwik_archive_blob_2008_06 | MyISAM |      10 | Dynamic    | 2446379 |            297 |   729117320 | 281474976710655 |     62703616 |    960404 |           NULL | 2008-07-01 14:03:34 | 2008-07-07 04:22:56 | 2008-07-06 14:54:47 | latin1_swedish_ci |     NULL |                |         | 
+----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

so i think, before i run the DELETE, i need to ask piwik to process that month’s data?

how can I do that?