0

I've created a MySQL event to delete rows older than 3 years. I want this to run at 3 am every morning. MySQL version 5.5.61.

CREATE EVENT event_clean_child_attendance
ON SCHEDULE EVERY 1 DAY STARTS '2022-08-21 03:00:00';
DO delete FROM childattendance WHERE date < DATE_SUB(NOW(),INTERVAL 3 YEAR);

However, I wanted to test it first to make sure it was deleting properly, so I temporarily modified it to start every five minutes, starting a minute in the future.

ALTER EVENT event_clean_child_attendance ON SCHEDULE EVERY 5 MINUTE STARTS '2022-08-20 13:10:00';

I waited for the time to pass and I can see in process list that it did run as expected, as the "time" value is showing 90 seconds, which means it ran 90 seconds ago.

Yet when I check the childattendance table I still have all my rows in there. I have a little over 20 million rows. 8 million of those are older than three years, so I'm expecting to have just 12 million rows left after this event runs, yet I still have 20 million rows.

I've been trying to get this to work for about an hour now and for some reason, none of these rows are being deleted. What could the problem be? And what other tools can I use to try to figure this out?

2
  • 1
    Is there an index on date? If not, the event may be taking more than 5 minutes.
    – Rick James
    Aug 21, 2022 at 2:20
  • There is an index on date, yes, but you were right to say that it would take more than 5 minutes to complete. I'm not sure how long exactly but it was definitely longer than 20 minutes.
    – Vincent
    Aug 22, 2022 at 14:14

1 Answer 1

0

Turns out it did work. I walked away for a while and came back and it was done. I guess the query just took a very long time to execute.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.