MySQL create stored procedure called by scheduler

Veröffentlicht von philipp am

If you want to run a SQL command or function periodically you have to do the following:

  1. Check if the event scheduler is already enabled by executing the SQL command
    SHOW PROCESSLIST
  2. 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.

  3. 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 ;
  4. 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();
Kategorien: Linux

0 Kommentare

Schreibe einen Kommentar

Avatar-Platzhalter

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert