Hi Piwik-users,
I have one Table which is about 7GB large.
It is the phpbb_piwik_archive_blob_2011_01 with 210,180 entries.
The other phpbb_piwik_archive_blob_YYYY_mm Tables have less than 10 MB.
Is there a way to repair the wrong table? or any other solution?
Greets
Sawascwoolf
Wow, that’s really surprising! are you running Piwik 1.8.4 ? did you setup: How to Set up Auto-Archiving of Your Reports - Analytics Platform - Matomo ?
this should not happen if the script is setup as above. if it still happens, probably you found a bug. please let me know!
I´ve updated to 1.8.4 after my post, without any problems.
I´ve setup a cron for every 5 minutes. When I load it manually there appear two memory-Errors.
Here is the output of the cron:
[quote=“Piwik Cron”][2012-09-19 06:40:28] [4564b60e] [11.24 Mb] ---------------------------
[2012-09-19 06:40:28] [4564b60e] [11.24 Mb] INIT
[2012-09-19 06:40:28] [4564b60e] [11.24 Mb] Querying Piwik API at: http://stats.example.de/misc/cron/../../index.php
[2012-09-19 06:40:28] [4564b60e] [11.24 Mb] Running as Super User: web505
[2012-09-19 06:40:28] [4564b60e] [11.40 Mb] Notes
[2012-09-19 06:40:28] [4564b60e] [11.40 Mb] - Reports for today will be processed at most every 10 seconds. You can change this value in Piwik UI > Settings > General Settings.
[2012-09-19 06:40:28] [4564b60e] [11.40 Mb] - Reports for the current week/month/year will be refreshed at most every 3600 seconds.
[2012-09-19 06:40:28] [4564b60e] [11.47 Mb] --force-all-periods was detected: we will process websites with visits in the last 7 Tage 0 Stunden
[2012-09-19 06:40:28] [4564b60e] [11.64 Mb] Will process 10 websites with new visits since 7 Tage 0 Stunden , IDs: 3, 4, 5, 6, 9, 10, 11, 13, 15, 16
[2012-09-19 06:40:28] [4564b60e] [11.66 Mb] Will process 4 other websites because the last time they were archived was on a different day (in the website’s timezone) , IDs: 1, 8, 12, 14
[2012-09-19 06:40:28] [4564b60e] [11.65 Mb] ---------------------------
[2012-09-19 06:40:28] [4564b60e] [11.65 Mb] START
[2012-09-19 06:40:28] [4564b60e] [11.65 Mb] Starting Piwik reports archiving…
[2012-09-19 06:40:28] [4564b60e] [11.67 Mb] Archived website id = 3, period = day, Time elapsed: 0.286s
[2012-09-19 06:40:28] [4564b60e] [11.66 Mb] Archived website id = 3, today = 6 visits, 1 API requests, Time elapsed: 0.286s [1/14 done]
[2012-09-19 06:40:28] [4564b60e] [11.67 Mb] Skipped website id 4, no visit today, Time elapsed: 0.202s
[2012-09-19 06:40:29] [4564b60e] [11.67 Mb] Archived website id = 5, period = day, Time elapsed: 0.265s
[2012-09-19 06:40:29] [4564b60e] [11.69 Mb] Archived website id = 5, period = week, 23809 visits, Time elapsed: 0.545s
[2012-09-19 06:40:31] [4564b60e] [11.69 Mb] Archived website id = 5, period = month, 57126 visits, Time elapsed: 1.594s
[2012-09-19 06:40:35] [4564b60e] [11.68 Mb] ERROR: Got invalid response from API request: http://stats.example.de/misc/cron/../../index.php?module=API&method=VisitsSummary.getVisits&idSite=5&period=year&date=last52&format=php&token_auth=token_auth_xyz&trigger=archivephp. Response was ‘
Fatal error: Allowed memory size of 163577856 bytes exhausted (tried to allocate 73 bytes) in /var/www/webXX/html/stats/core/DataTable.php on line 1071
’
[2012-09-19 06:40:35] [4564b60e] [11.68 Mb] Archived website id = 5, period = year, 0 visits, Time elapsed: 4.380s
[2012-09-19 06:40:35] [4564b60e] [11.67 Mb] Archived website id = 5, today = 4 visits, 4 API requests, Time elapsed: 6.785s [3/14 done]
[2012-09-19 06:40:35] [4564b60e] [11.67 Mb] Archived website id = 6, period = day, Time elapsed: 0.243s
[2012-09-19 06:40:35] [4564b60e] [11.67 Mb] Archived website id = 6, today = 1 visits, 1 API requests, Time elapsed: 0.243s [4/14 done]
[2012-09-19 06:40:35] [4564b60e] [11.67 Mb] Skipped website id 9, no visit today, Time elapsed: 0.192s
[2012-09-19 06:40:36] [4564b60e] [11.67 Mb] Skipped website id 10, no visit today, Time elapsed: 0.189s
[2012-09-19 06:40:36] [4564b60e] [11.67 Mb] Skipped website id 11, no visit today, Time elapsed: 0.189s
[2012-09-19 06:40:36] [4564b60e] [11.67 Mb] Archived website id = 13, period = day, Time elapsed: 0.258s
[2012-09-19 06:40:36] [4564b60e] [11.67 Mb] Archived website id = 13, today = 1 visits, 1 API requests, Time elapsed: 0.258s [8/14 done]
[2012-09-19 06:40:36] [4564b60e] [11.67 Mb] Archived website id = 15, period = day, Time elapsed: 0.247s
[2012-09-19 06:40:36] [4564b60e] [11.67 Mb] Archived website id = 15, today = 1 visits, 1 API requests, Time elapsed: 0.248s [9/14 done]
[2012-09-19 06:40:37] [4564b60e] [11.67 Mb] Skipped website id 16, no visit today, Time elapsed: 0.195s
[2012-09-19 06:40:37] [4564b60e] [11.67 Mb] Archived website id = 1, period = day, Time elapsed: 0.210s
[2012-09-19 06:40:37] [4564b60e] [11.68 Mb] Archived website id = 1, period = week, 0 visits, Time elapsed: 0.233s
[2012-09-19 06:40:37] [4564b60e] [11.68 Mb] Archived website id = 1, period = month, 0 visits, Time elapsed: 0.243s
[2012-09-19 06:40:37] [4564b60e] [11.68 Mb] Archived website id = 1, period = year, 1 visits, Time elapsed: 0.233s
[2012-09-19 06:40:37] [4564b60e] [11.67 Mb] Archived website id = 1, today = 0 visits, 4 API requests, Time elapsed: 0.920s [11/14 done]
[2012-09-19 06:40:38] [4564b60e] [11.67 Mb] Archived website id = 8, period = day, Time elapsed: 0.196s
[2012-09-19 06:40:38] [4564b60e] [11.68 Mb] Archived website id = 8, period = week, 0 visits, Time elapsed: 0.232s
[2012-09-19 06:40:38] [4564b60e] [11.68 Mb] Archived website id = 8, period = month, 10 visits, Time elapsed: 0.374s
[2012-09-19 06:40:39] [4564b60e] [11.68 Mb] Archived website id = 8, period = year, 21 visits, Time elapsed: 0.404s
[2012-09-19 06:40:39] [4564b60e] [11.67 Mb] Archived website id = 8, today = 0 visits, 4 API requests, Time elapsed: 1.208s [12/14 done]
[2012-09-19 06:40:39] [4564b60e] [11.68 Mb] Archived website id = 12, period = day, Time elapsed: 0.217s
[2012-09-19 06:40:39] [4564b60e] [11.69 Mb] Archived website id = 12, period = week, 0 visits, Time elapsed: 0.236s
[2012-09-19 06:40:39] [4564b60e] [11.68 Mb] Archived website id = 12, period = month, 4 visits, Time elapsed: 0.243s
[2012-09-19 06:40:40] [4564b60e] [11.68 Mb] Archived website id = 12, period = year, 21 visits, Time elapsed: 0.384s
[2012-09-19 06:40:40] [4564b60e] [11.67 Mb] Archived website id = 12, today = 0 visits, 4 API requests, Time elapsed: 1.080s [13/14 done]
[2012-09-19 06:40:40] [4564b60e] [11.68 Mb] Archived website id = 14, period = day, Time elapsed: 0.207s
[2012-09-19 06:40:40] [4564b60e] [11.69 Mb] Archived website id = 14, period = week, 0 visits, Time elapsed: 0.223s
[2012-09-19 06:40:40] [4564b60e] [11.68 Mb] Archived website id = 14, period = month, 1 visits, Time elapsed: 0.243s
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] Archived website id = 14, period = year, 43 visits, Time elapsed: 0.383s
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] Archived website id = 14, today = 0 visits, 4 API requests, Time elapsed: 1.057s [14/14 done]
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] Done archiving!
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] ---------------------------
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] SUMMARY
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] Total daily visits archived: 13
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] Archived today’s reports for 9 websites
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] Archived week/month/year for 5 websites.
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] Skipped 5 websites: no new visit since the last script execution
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] Skipped 0 websites day archiving: existing daily reports are less than 10 seconds old
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] Skipped 4 websites week/month/year archiving: existing periods reports are less than 3600 seconds old
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] Total API requests: 29
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] done: 14/14 100%, 13 v, 9 wtoday, 5 wperiods, 29 req, 13053 ms, 1 errors. eg. 'Got invalid response from API request: http://stats.example.de/misc/cron/../../index.php?module=API&method=VisitsSummary.getVisits&idSite=5&period=year&date=last52&format=php&token_auth=token_auth_xyz&trigger=archivephp. Response was ‘
Fatal error: Allowed memory size of 163577856 bytes exhausted (tried to allocate 73 bytes) in /var/www/webXX/html/stats/core/Data’
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] Time elapsed: 13.053s
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] ---------------------------
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] SCHEDULED TASKS
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] Starting Scheduled tasks…
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] No task to run
[2012-09-19 06:40:41] [4564b60e] [11.68 Mb] done
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] ---------------------------
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] SUMMARY OF ERRORS
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] Error: Got invalid response from API request: http://stats.example.de/misc/cron/../../index.php?module=API&method=VisitsSummary.getVisits&idSite=5&period=year&date=last52&format=php&token_auth=token_auth_xyz&trigger=archivephp. Response was '
Fatal error: Allowed memory size of 163577856 bytes exhausted (tried to allocate 73 bytes) in /var/www/webXX/html/stats/core/Data
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] 1 total errors during this script execution, please investigate and try and fix these errors
[2012-09-19 06:40:41] [4564b60e] [11.67 Mb] ERROR: 1 total errors during this script execution, please investigate and try and fix these errors. First error was: Got invalid response from API request: http://stats.example.de/misc/cron/../../index.php?module=API&method=VisitsSummary.getVisits&idSite=5&period=year&date=last52&format=php&token_auth=token_auth_xyz&trigger=archivephp. Response was '
Fatal error: Allowed memory size of 163577856 bytes exhausted (tried to allocate 73 bytes) in /var/www/webXX/html/stats/core/Data
Fatal error: 1 total errors during this script execution, please investigate and try and fix these errors. First error was: Got invalid response from API request: http://stats.example.de/misc/cron/../../index.php?module=API&method=VisitsSummary.getVisits&idSite=5&period=year&date=last52&format=php&token_auth=token_auth_xyz&trigger=archivephp. Response was '
Fatal error: Allowed memory size of 163577856 bytes exhausted (tried to allocate 73 bytes) in /var/www/webXX/html/stats/core/Data in /var/www/webXX/html/stats/misc/cron/archive.php on line 556
[/quote]
My Webhosting package is limited to 156MB Memory–> I can´t get more memory for piwik.
I´ve noticed an other “Bug” yesterday:
Thx for your fast reply.
Greets Sawascwoolf
this error, would be causing the huge table in the DB, because archiving fails to process and then the archives are triggered again and again…
Btw you should setup the cron to run every hour or 2 hours ,not 5min!
You can delete the table, if you stlil have the logs, it will be re-created.
Ok, i´ve changed the cron to “every hour”.
I deleted the table and started the Cron manually but the error still occurs.
The created table is getting bigger (in small steps of ca. 400 entries) with every cron-run.
Is it really to less Memory, or is it an error?
The problem is that, there is not enough memory so the archiving fails. so it tries again and fails again, etc.
maybe we shoul dnot try again so much, but that’s a bug I guess 