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.

sql server article properties


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



  • No labels