MySQL create stored procedure called by scheduler
If you want to run a SQL command or function periodically you have to do the following:
- Check if the event scheduler is already enabled by executing the SQL command
- If it shows the following entry
The scheduler is already running and you can continue with step 3.
If there is no such entry execute the SQL command
SET GLOBAL event_scheduler = ON;
to start the event scheduler.
- The next step is to write your stored procedure/function you want to execute periodically.
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `myprocedure`() MODIFIES SQL DATA UPDATE SystemStatus SET isAlive=0$$ DELIMITER ;
- The last step is to create the event you want to schedule and enter the name for the event, the time schedule and the name of the function you want to execute
CREATE EVENT myevent ON SCHEDULE EVERY 5 SECOND DO CALL myfunction();