The task of purging the old audit logs in DualShield Management Console may fail when you are trying to delete a huge amount of records. On MySQL you may experience the following error,
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
The bulk-delete stored procedure below tries to avoid this problem.
Its speed still relies on the correct indexing. The following indexes are essential,
id, log_date,parent_id on log table
parent_id on log_field table
Important: If you have configured SQL replication, you will need to disable the binary logs first. For MySQL, you can do it with SET SESSION SQL_LOG_BIN = 0; MS SQL server should have the similar option.
MySQL Users: You will need to disable foreign key checks temporarily also as some logs have multiple levels of dependence. Use the command, SET Foreign_Key_Checks = 0; before running the stored procedure below and set it back to 1 after the procedure has finished running.
MS SQL Server version,