ClearFoundation Tracker - ClearOS
View Issue Details
0001975ClearOSapp-resource-report - Resource Reportpublic2014-09-29 07:372016-12-23 11:24
user2 
user2 
normalminorhave not tried
closedfixed 
 
7.3.0 Beta 17.3.0 Beta 1 
0001975: The "resource" table in system-mysql grows indefinitely, but reports can only show 30 days of data
My resource table in system-mysql is currently 19.5MiB in size with entries going back to November 2012 - about 170,000 records (presumaly when app-resource-report was first installed). In the webconfig you can view a maximum of 30 days of data. There is a big mismatch between data logged and data viewable. If you can only view 30 days of data why keep all history? Alternatively if you keep all history why can you only view 30 days of it?

As far as I can see the database will grow indefinitely which I don't think is a good idea. I think a database pruning routine has been missed from the report. It can be achieved with the following one liner in cron.daily cron.weekly or cron.monthly:

/usr/clearos/sandbox/usr/bin/mysql -D reports -u reports -p`cut -f3 -d" " /var/clearos/system_database/reports` -e 'delete from resource where TIMESTAMPDIFF(MONTH,timestamp,curdate()) > 2 ;'

This would keep 3 months of data. It would be really easy to switch it to working in days.
Install the app and wait a long time?
No tags attached.
related to 0001973closed  The "resource" table in system-mysql grows indefinitely, but reports can only show 30 days of data 
related to 0011501closed user2 Add reports database purge script 
related to 0011511closed user2 The "network" table in system-mysql grows indefinitely, but reports can only show 30 days of data 
Issue History
2014-09-29 07:37user2New Issue
2014-09-29 07:37user2Issue generated from: 0001973
2014-09-29 07:37user2Relationship addedrelated to 0001973
2014-09-29 07:40user2Statusnew => confirmed
2014-10-21 15:35user2Target Version7.0.0 Alpha 2 => 7.1.0 Beta 2
2015-05-25 05:18user2Target Version7.1.0 Beta 2 => 7.1.0 Beta 3
2015-07-12 11:26user2Target Version7.1.0 Beta 3 => 7.1.0 RC1
2015-07-28 18:17user2Target Version7.1.0 RC1 => 7.1.1
2015-10-08 15:15user2Target Version7.1.1 => 7.1.0 Updates
2015-11-22 20:40user2Target Version7.1.0 Updates => 7.2.0 Updates
2016-08-30 09:57user2Target Version7.2.0 Updates => 7.3.0 Beta 1
2016-11-16 09:12user2Target Version7.3.0 Beta 1 => 7.3.0 Beta 1
2016-12-09 09:50user2Issue cloned: 0011501
2016-12-09 09:50user2Relationship addedrelated to 0011501
2016-12-09 09:52user2Issue cloned: 0011511
2016-12-09 09:52user2Relationship addedrelated to 0011511
2016-12-09 10:07user2Statusconfirmed => resolved
2016-12-09 10:07user2Fixed in Version => 7.3.0 Beta 1
2016-12-09 10:07user2Resolutionopen => fixed
2016-12-09 10:07user2Assigned To => user2
2016-12-23 11:24user2Statusresolved => closed

There are no notes attached to this issue.