-- -----------------------------------------------------------------------------
|
-- Procedure to delete records in TKO database older than 180 days.
|
-- -----------------------------------------------------------------------------
|
DELIMITER $$
|
CREATE PROCEDURE remove_old_tests_sp()
|
BEGIN
|
-- Delete tko_tests older than 180 days in batches of 5k. Wait for 5 seconds to
|
-- avoid database lock for too long.
|
SET @cutoff_date = DATE_SUB(CURDATE(),INTERVAL 180 DAY);
|
|
WHILE EXISTS (SELECT test_idx FROM tko_tests WHERE started_time < @cutoff_date LIMIT 1) DO
|
BEGIN
|
SELECT concat("Deleting 5k records in tko_tests older than ", @cutoff_date);
|
DELETE FROM tko_tests WHERE started_time < @cutoff_date LIMIT 5000;
|
SELECT SLEEP(5);
|
END;
|
END WHILE;
|
|
-- Some tests may have started_time being NULL, but with finished_time set.
|
-- Deletion for these records is done in a different while loop to make the
|
-- query go faster as finished_time is not indexed.
|
WHILE EXISTS (SELECT test_idx FROM tko_tests where started_time IS NULL and finished_time < @cutoff_date LIMIT 1) DO
|
BEGIN
|
SELECT concat("Deleting 5k records in tko_tests with finished time older than ", @cutoff_date);
|
DELETE FROM tko_tests WHERE started_time IS NULL AND finished_time < @cutoff_date LIMIT 5000;
|
SELECT SLEEP(5);
|
END;
|
END WHILE;
|
|
-- After tko_tests is cleaned up, we can start cleaning up tko_jobs, due to FK
|
-- constrain. Move the cutoff time to 5 days older as some tko_jobs records
|
-- may have later started_time comparing to tko_tests. Deleting these records
|
-- may lead to FK constrain violation.
|
SET @cutoff_date = DATE_SUB(@cutoff_date,INTERVAL 5 DAY);
|
WHILE EXISTS (SELECT job_idx FROM tko_jobs WHERE started_time < @cutoff_date LIMIT 1) DO
|
BEGIN
|
SELECT concat("Deleting 5k records in tko_jobs older than ", @cutoff_date);
|
DELETE FROM tko_jobs WHERE started_tim < @cutoff_date LIMIT 5000;
|
SELECT SLEEP(5);
|
END;
|
END WHILE;
|
END;$$
|
DELIMITER ;
|
