blob: a2208668943e82cdc57b730a16a9ba85ac5213f8 [file] [log] [blame] [edit]
-- -----------------------------------------------------------------------------
-- 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 ;