Page History
...
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.
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 ;
|
...