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?