1-- -----------------------------------------------------------------------------
2-- Procedure to delete records in TKO database older than 180 days.
3-- -----------------------------------------------------------------------------
4DELIMITER $$
5CREATE PROCEDURE remove_old_tests_sp()
6BEGIN
7  -- Delete tko_tests older than 180 days in batches of 5k. Wait for 5 seconds to
8  -- avoid database lock for too long.
9  SET @cutoff_date = DATE_SUB(CURDATE(),INTERVAL 180 DAY);
10
11  WHILE EXISTS (SELECT test_idx FROM tko_tests WHERE started_time < @cutoff_date LIMIT 1) DO
12    BEGIN
13      SELECT concat("Deleting 5k records in tko_tests older than ", @cutoff_date);
14      DELETE FROM tko_tests WHERE started_time < @cutoff_date LIMIT 5000;
15      SELECT SLEEP(5);
16    END;
17  END WHILE;
18
19  -- Some tests may have started_time being NULL, but with finished_time set.
20  -- Deletion for these records is done in a different while loop to make the
21  -- query go faster as finished_time is not indexed.
22  WHILE EXISTS (SELECT test_idx FROM tko_tests where started_time IS NULL and finished_time < @cutoff_date LIMIT 1) DO
23    BEGIN
24      SELECT concat("Deleting 5k records in tko_tests with finished time older than ", @cutoff_date);
25      DELETE FROM tko_tests WHERE started_time IS NULL AND finished_time < @cutoff_date LIMIT 5000;
26      SELECT SLEEP(5);
27    END;
28  END WHILE;
29
30  -- After tko_tests is cleaned up, we can start cleaning up tko_jobs, due to FK
31  -- constrain. Move the cutoff time to 5 days older as some tko_jobs records
32  -- may have later started_time comparing to tko_tests. Deleting these records
33  -- may lead to FK constrain violation.
34  SET @cutoff_date = DATE_SUB(@cutoff_date,INTERVAL 5 DAY);
35  WHILE EXISTS (SELECT job_idx FROM tko_jobs WHERE started_time < @cutoff_date LIMIT 1) DO
36    BEGIN
37      SELECT concat("Deleting 5k records in tko_jobs older than ", @cutoff_date);
38      DELETE FROM tko_jobs WHERE started_tim < @cutoff_date LIMIT 5000;
39      SELECT SLEEP(5);
40    END;
41  END WHILE;
42END;$$
43DELIMITER ;
44