Efficiently Purging Audits

Cleaning up the audit tables will release some disk space and may be required to shrink the database enough to perform the dump and sftp for a manual database re-sync when replication is broken. You should stop replication and run the commands on each node. DO NOT perform this if replication is broken and you do not intend to run the replication restart shell script, otherwise this may prevent automatic recovery by removing records that are referenced in pending relay logs.

Clean up the tables that have no constraints first to shorten the time between drop/add constraints:

truncate table audit_detail_params;

truncate table audit_message;

truncate table audit_admin;

truncate table audit_system;

Drop the constraints that block truncating other tables:

alter table audit_system drop foreign key `audit_system_ibfk_1`;

alter table audit_admin drop foreign key `audit_admin_ibfk_1`;

alter table audit_message drop foreign key `audit_message_ibfk_1`;

alter table audit_detail drop foreign key `audit_detail_ibfk_1`;

alter table audit_detail_params drop foreign key `audit_detail_params_ibfk_1`;

Truncate all the audit tables to be sure that there will be no records preventing re-adding the constraints:

truncate table audit_main;

truncate table audit_detail;

truncate table audit_detail_params;

truncate table audit_message;

truncate table audit_admin;

truncate table audit_system;

Re-add the constraints:

alter table audit_system add CONSTRAINT `audit_system_ibfk_1` FOREIGN KEY (`goid`) REFERENCES `audit_main` (`goid`) ON DELETE CASCADE ON UPDATE NO ACTION;

alter table audit_admin add CONSTRAINT `audit_admin_ibfk_1` FOREIGN KEY (`goid`) REFERENCES `audit_main` (`goid`) ON DELETE CASCADE ON UPDATE NO ACTION;

alter table audit_message add CONSTRAINT `audit_message_ibfk_1` FOREIGN KEY (`goid`) REFERENCES `audit_main` (`goid`) ON DELETE CASCADE ON UPDATE NO ACTION;

alter table audit_detail add CONSTRAINT `audit_detail_ibfk_1` FOREIGN KEY (`audit_goid`) REFERENCES `audit_main` (`goid`) ON DELETE CASCADE ON UPDATE NO ACTION;

alter table audit_detail_params add CONSTRAINT `audit_detail_params_ibfk_1` FOREIGN KEY (`audit_detail_goid`) REFERENCES `audit_detail` (`goid`) ON DELETE CASCADE ON UPDATE NO ACTION;

You can run all of these commands at once and more than once. If there is any errors on the re-add then run all the commands again.