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.
MySQL version,
delimiter // Create PROCEDURE `dualshield`.`cleanlogs`(datetocut DATETIME, batchsize INT) BEGIN SET @datetocut = datetocut; SET @batchsize = batchsize; /* find the id first */ SET @qid=NULL; select id into @qid from log where log_date < @datetocut order by id desc limit 1; /* somehow variable in limit is not supported, at east on 5.7.12 delete from log_field where parent_id < @qid limit @batchsize */ IF @qid IS NOT NULL THEN select count(*) from log_field where parent_id <= @qid; select count(*) from log where id <= @qid; set @count = @batchsize; SET @sql_text1 = CONCAT('delete from log_field where parent_id <= ', @qid, ' LIMIT ', @batchsize); WHILE (@count = @batchsize) DO PREPARE stmt1 FROM @sql_text1; EXECUTE stmt1; select row_count() into @count; DEALLOCATE PREPARE stmt1; END WHILE; set @count = @batchsize; SET @sql_text2 = CONCAT('delete from log where id <= ', @qid, ' and parent_id is not null LIMIT ', @batchsize); WHILE (@count = @batchsize) DO PREPARE stmt2 FROM @sql_text2; EXECUTE stmt2; select row_count() into @count; DEALLOCATE PREPARE stmt2; END WHILE; set @count = @batchsize; SET @sql_text3 = CONCAT('delete from log where id <= ', @qid, ' LIMIT ', @batchsize); WHILE (@count = @batchsize) DO PREPARE stmt3 FROM @sql_text3; EXECUTE stmt3; select row_count() into @count; DEALLOCATE PREPARE stmt3; END WHILE; select count(*) from log_field where parent_id <= @qid; select count(*) from log where id <= @qid; END IF; END // delimiter ;
MS SQL Server version,
CREATE PROCEDURE cleanlogs @datetocut DATETIME, @batchsize INT=1000 AS BEGIN DECLARE @qid INT; DECLARE @count INT; SET @qid=NULL; SELECT TOP 1 @qid=id FROM log WHERE log_date < @datetocut ORDER BY id DESC; IF @qid IS NOT NULL BEGIN SELECT COUNT(*) FROM log_field WHERE parent_id <= @qid SELECT COUNT(*) FROM log WHERE id <= @qid set @count = @batchsize WHILE (@count = @batchsize) BEGIN DELETE TOP (@batchsize) FROM log_field WHERE parent_id <= @qid SELECT @count = @@ROWCOUNT END set @count = @batchsize WHILE (@count = @batchsize) BEGIN DELETE TOP (@batchsize) FROM log WHERE id <= @qid and parent_id is not NULL SELECT @count = @@ROWCOUNT END set @count = @batchsize WHILE (@count = @batchsize) BEGIN DELETE TOP (@batchsize) FROM log WHERE id <= @qid SELECT @count = @@ROWCOUNT END SELECT COUNT(*) FROM log_field where parent_id <= @qid SELECT COUNT(*) FROM log where id <= @qid END END GO