Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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 batchbulk-delete stored procedure below tries to avoid this problem. Please use it at your own risk. 

Its speed still relies on the correct indexing. The following indexes are essential,

...

Important: If you have configured SQL replication, please you will need to disable the binary logs first. On 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,


Code Block
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 ;


...