ClearFoundation Tracker - ClearOS
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0001973ClearOSapp-resource-report - Resource Reportpublic2014-09-29 05:572017-04-13 14:00
ReporterNickH 
Assigned To 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionopen 
PlatformOSOS Version
Product Version 
Target Version6.9.0Fixed in Version 
Summary0001973: The "resource" table in system-mysql grows indefinitely, but reports can only show 30 days of data
DescriptionMy 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.
Steps To ReproduceInstall the app and wait a long time?
Additional Information
TagsNo tags attached.
Relationships
related to 0001975closed user2 The "resource" table in system-mysql grows indefinitely, but reports can only show 30 days of data 
related to 0014231closed  The "network" table in system-mysql grows indefinitely, but reports can only show 30 days of data 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2014-09-29 05:57NickHNew Issue
2014-09-29 07:35user2Statusnew => confirmed
2014-09-29 07:35user2Target Version => 6.6.0 Beta 2
2014-09-29 07:36user2Note Added: 0001297
2014-09-29 07:37user2Issue cloned: 0001975
2014-09-29 07:37user2Relationship addedrelated to 0001975
2014-11-13 10:27user2Target Version6.6.0 Beta 2 => 6.6.0
2014-12-11 10:13user2Target Version6.6.0 => 6.6.0 Updates
2015-08-13 18:16user2Target Version6.6.0 Updates => 6.7.0 Updates
2016-02-16 21:33user2Target Version6.7.0 Updates => 6.8.0 Updates
2017-03-21 08:43user2Target Version6.8.0 Updates => 6.9.0
2017-04-05 21:27user2Issue cloned: 0014231
2017-04-05 21:27user2Relationship addedrelated to 0014231
2017-04-06 08:18user2Statusconfirmed => resolved
2017-04-13 14:00user2Statusresolved => closed

Notes
(0001297)
user2   
2014-09-29 07:36   
There are purge routines for other reports -- it's just a copy/paste job. We were hoping to go back a year in the UI for this report. That hasn't happened.