1 -- ----------------------------------------------------------------------------- 2 -- Procedure to delete records in TKO database older than 180 days. 3 -- ----------------------------------------------------------------------------- 4 DELIMITER $$ 5 CREATE PROCEDURE remove_old_tests_sp() 6 BEGIN 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; 42 END;$$ 43 DELIMITER ; 44
