Issue #6717 has been updated by Aaron Dummer.
Foreign keys make it easier to delete related data. <pre> ALTER TABLE resource_events ADD FOREIGN KEY (resource_status_id) REFERENCES resource_statuses(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE resource_statuses ADD FOREIGN KEY (report_id) REFERENCES reports(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE report_logs ADD FOREIGN KEY (report_id) REFERENCES reports(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE metrics ADD FOREIGN KEY (report_id) REFERENCES reports(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE reports ADD FOREIGN KEY (node_id) REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE; </pre> Please consider adding them to a future release of dashboard. Note that I only added keys for some of the tables. There are others that would also benefit (node_group_memberships, for instance). One table that wouldn't benefit from foreign keys is timeline_events. It has subject_type and subject_id columns, so I assume the data could relate to various tables. To maintain referential integrity at the database level, you'd either have to write a custom trigger which gets called on each INSERT/UPDATE/DELETE. ---------------------------------------- Feature #6717: Cleanup of table resource_statuses in dashboard-database https://projects.puppetlabs.com/issues/6717 Author: Bart Descamps Status: Accepted Priority: High Assignee: Category: Target version: 2.x Keywords: dashboard, resource_statuses, cleanup Branch: Affected URL: Affected Dashboard version: Recently our dashboard-database reached about 90% of it's assigned diskspace and I suspected the reports to be the cause. However, we do already use the rake prune:reports feature (in a cronjob) so our older reports are automatically purged. The problem wasn't with the reports though but with the table resource_statuses. It had grown over 21 GB. I deleted the oldest records (which wasn't that easy as this locks the mysql-table (innodb)). Having a similar tool (rakefile) as is available for the reports would be quite nice though. I don't really see the benefit of keeping lots of old resource statuses anyway, so being able to schedule another cronjob that cleans these up would be nice. -- You have received this notification because you have either subscribed to it, or are involved in it. To change your notification preferences, please click here: http://projects.puppetlabs.com/my/account -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/puppet-bugs?hl=en.
