I have the following script. The scripts runs but what i want is for it to run every 5 minutes following the wall clock time. Meaning it should run at 5, 10, 15 ... following the wall clock time at 5 minitues interval. What I can gauge for the result of the run is that the time is 10 - 30 seconds off. Is there a way to get it closer to the 5 minutues mark?. Thanks.
CREATE DEFINER=`root`@`%`
EVENT `run_event`
ON SCHEDULE EVERY 5 MINUTE STARTS '2016-04-06 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
DO CALL my_procedure
解决方案
Your configuration seems correct. The timing of events is not extremely precise, but it is reasonably precise -- enough that it shouldn't be half a minute off.
One interesting hack for event troubleshooting is to throw a warning from within the event. Since there is no client to which the warning can be sent, the warning is written to the MySQL error log. This should work in MySQL 5.5 and up:
DELIMITER $$
DROP EVENT IF EXISTS run_event $$
CREATE DEFINER=`root`@`%`
EVENT `run_event`
ON SCHEDULE EVERY 5 MINUTE STARTS '2016-04-06 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
DO
BEGIN
SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'run_event started';
CALL my_procedure;
SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'run_event finished';
END $$
It's been a while since I used this, but I believe the warnings will be written to the error log immediately, and not buffered until the event is finished.