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