Versions Compared

Key

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

...

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 ;


...