Write redcap_log_event purging tools

[pbchase]

The redcap_log_event* tables could use some management tools.

These threads in the REDCap community could be helpful

• Why are records left in redcap_log_event and redcap_log_view tables when a project is deleted? discusses what is and is not deleted from the log tables when a project is deleted. It appears to be inspiration for Sue Lowry's comments below about what she purges from the tables.

• redcap_log_event too big Here are some helpful snippets:

sue.lowry Deactivated commented • 5 years, 3 months ago (2018-03-27 14:38)
One thing that I've started to look into is purging out most of the records in the redcap_log_events and redcap_log_view table for projects that have already been deleted at least a year ago, but leaving the redcap_log_events records that are for the pages 'ProjectGeneral/create_project.php','ProjectGeneral/notifications.php', 'ProjectGeneral/delete_project.php' and 'cron.php', which leaves the history of when it was created, set to be deleted and actually deleted.

vincent.benoit2 commented • 1 year, 7 months ago (2021-11-26 04:45)
It would be useful to be able to purge log for completed projets, with a download all project's logs option so this information can be stored on a NAS if an audit is required for the project afterwards.

• Incomplete removal of data from redcap_log_event* on project delete provides more discussion on how REDCap overwrites log entries and strategies for doing that.

• Migrating log entries from redcap_log_event describes how Andy Martin cleaned up their log event tables via a mix of data migration and indexing. It includes code for migration, adding the indices, measuring tables size, etc. Paul Litwin provides code for moving the log data in chunks when the log row count is very large.

It's unclear to me how the log migration work relates to the changes required to fix database tables that impeded Unicode support in older REDCap installations.

[pbchase]

Related to this, REDCap might ask that older tables be altered to set ROW_FORMAT=DYNAMIC. This post explains that each such table will be copied in the process: Some database tables need to be modified - RECOMMENDED This temporarily doubles their disk space usage until the old copy is purged.

[pbchase]

There's a discussion of data preservation rules and the need to preserve log data for 7 years at How can we clear out large database tables logs?.

[pbchase]

There's a discussion of the ramifications of projects with a very large number of log rows jamming up the pre-population of the users filter in the REDCap Logging query UI. There is a discussion about the value of defragging and rebuilding a log table with ALTER TABLE ``redcap_log_event7`` ENGINE = InnoDB; See Logging page times out even after moving all log entries to redcap_log_event7