MySQL 5.1 : La nouveauté du programmateur d’évènements (Event Scheduler)

Introduction

À partir de la version 5.1.6 de MySQL, les évènements ou triggers temporels sont introduits. Cette nouveauté se traduit par l’apparition d’un programmateur d’évènements (event scheduler) au sein du moteur MySQL sous la forme d’un thread.

Il n’existe pas de standard en ce qui concerne les programmateurs d’évènements dans le monde des moteurs de bases de données et MySQL a décidé de combiner les fonctionnalités du Job Scheduler d’Oracle (DBMS_JOB) et la simplicité de la syntaxe de Sybase SQL Anywhere.

Cette documentation présente techniquement le programmateur d’évènements de MySQL 5.1 (paramètres systèmes, syntaxes, comportement, privilèges, etc.).

La variable globale système event_scheduler

Activation et désactivation du programmateur d’évènements (events scheduler)

Le programmateur d’évènements est un thread qui tourne en tâche de fond (background task) à la recherche d’évènements à exécuter. Ce thread est dans la plupart du temps en mode « sleeping ». Pour mettre cette fonctionnalité en route :

mysql > SET GLOBAL event_scheduler = 1;

Pour désactiver l’event scheduler :

mysql > SET GLOBAL event_scheduler = 0;

Comme pour toutes les commandes SET GLOBAL, le privilège SUPER est requis pour manier la variable système event_scheduler. Il est également possible d’appliquer la variable système event_scheduler au démarrage du serveur :

mysqld … --event_scheduler=1

Visualisation du statut de l’event scheduler

Pour vérifier le statut actif ou inactif de l’event scheduler :

mysql > SHOW VARIABLES LIKE 'event_scheduler';
mysql > SELECT @@event_scheduler;

Commandes SQL de gestion des évènements

CREATE EVENT

Pour créer un évènement :

CREATE EVENT [ IF NOT EXISTS ] event_name
      ON SCHEDULE schedule
      [ ON COMPLETION [ NOT ] PRESERVE ]
      [ ENABLED | DISABLED ]
      [ COMMENT 'comment' ]
      DO sql_statement;

Le nom de l’évènement 'event_name' ne doit pas dépasser 64 caractères (les délimiteurs ` sont autorisés) et peut être complètement qualifié database1.event1. Les évènements sont des objets d’une base de données et leurs noms doivent être uniques dans une base de données. Pour vérifier l’unicité d’un évènement d’une base de données, MySQL est case-insensitive (insensible à la casse).

Clause ON SCHEDULE

La programmation 'schedule' peut être un timestamp dans le futur, un intervalle récurrent ou une combinaison d’intervalles récurrents et de timestamp.

Les possibilités sont :

• AT TIMESTAMP [ + intervalle valeur_entière]
• EVERY interval
• EVERY interval STARTS timestamp
• EVERY interval ENDS timestampcode>
• EVERY interval STARTS timestamp ENDS timestamp

'AT TIMESTAMP' signifie 'À déclencher une seule fois à l’heure spécifiée'. Le timestamp doit contenir à la fois la date et l’heure et doit être dans le futur.

Quelques exemples avec la syntaxe 'AT TIMESTAMP' :

CREATE EVENT `Something To Do`
      ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
      DO DROP TABLE t;

Cet évènement s’exécutera une seule fois dans 5 jours à partir de maintenant et supprimera la table t.

CREATE EVENT The_Main_Event
      ON SCHEDULE AT TIMESTAMP '2006-01-20 12:00:00'
      DO DROP TABLE t;

Cet évènement s’exécutera une seule fois le 20 janvier 2006 et supprimera la table t.

'EVERY INTERVAL' signifie 'À déclencher cycliquement'. Un intervalle récurrent commence avec le mot clé EVERY dans la commande CREATE EVENT et est suivi d’un entier positif plus un des mots clés YEAR, MONTH, WEEK, DAY, HOUR, MINUTE ou SECOND afin de décrire le cycle.

Quelques exemples avec la syntaxe 'EVERY INTERVAL' :

CREATE EVENT e
      ON SCHEDULE EVERY 1 YEAR
      DO DROP TABLE t;

Cet évènement s’exécutera une fois par an et supprimera la table t.

CREATE EVENT e
      ON SCHEDULE EVERY 1 YEAR
      STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
      DO DROP TABLE t;

Cet évènement s’exécutera une fois par an, démarrera exactement dans 5 jours à partir de maintenant et supprimera la table t.

CREATE EVENT e
      ON SCHEDULE EVERY 1 YEAR
      ENDS CURRENT_TIMESTAMP + INTERVAL 5 YEAR
      DO DROP TABLE t;

Cet évènement s’exécutera une fois par an, s’arrêtera exactement dans 5 ans à partir de maintenant et supprimera la table t.

CREATE EVENT e
      ON SCHEDULE EVERY 1 YEAR
      STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
      ENDS CURRENT_TIMESTAMP + INTERVAL 5 YEAR
      DO DROP TABLE t;

Cet évènement s’exécutera une fois par an, démarrera exactement dans 5 jours à partir de maintenant, s’arrêtera exactement dans 5 ans à partir de maintenant et supprimera la table t.

Clause ON COMPLETION [NOT] PRESERVE

Avec la commande ON COMPLETION NOT PRESERVE (qui est le mode par défaut), l’évènement est supprimé lorsque l’évènement n’a plus de raison de s’exécuter.

Clause ENABLED | DISABLED

Le mode ENABLEDest le mode par défaut et indique que l’évènement est activé dès la programmation spécifiée. Lorsqu’un évènement est désactivé (DISABLED), même si l’heure de programmation est atteinte, l’évènement n’est pas déclenché.

Clause COMMENT

La clause COMMENT est une chaîne de caractères uniquement utilisé à des fins de documentations et cette chaîne est stockée dans la table de méta données mysql.event pour l’évènement en questions.

Clause sql_statement

La commande sql_statement spécifie l’action à effectuer lorsque l’évènement est exécuté. Il s’agit d’une commande unique, mais il peut s’agir aussi d’une commande composée (comme les procédures stockées ou les triggers). La règle générale est la suivante : toutes les commandes SQL qui peuvent être exécutées dans des routines (procédures stockées, triggers), peuvent être exécutées dans un évènement.

Exemple :

CREATE EVENT e
      ON SCHEDULE EVERY 5 SECOND
      DO
      BEGIN
        DECLARE v INTEGER;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
        SET v = 0;
      
        WHILE v < 5 DO
           INSERT INTO t1 VALUES (0);
           UPDATE t2 SET s1 = s1 + 1;
           SET v = v + 1;
        END WHILE;
      END //

ALTER EVENT

Pour modifier un évènement, utiliser la commande ALTER EVENT :

ALTER EVENT event_name
      [ ON SCHEDULE schedule ]
      [ RENAME TO event_name2 ]
      [ ON COMPLETION [ NOT ] PRESERVE ]
      [ COMMENT 'comment' ]
      [ ENABLED | DISABLED ]
      [ DO sql_statement ]

La commande ALTER EVENT doit invoquer un évènement qui existe déjà. Les clauses de la commande ALTER EVENT sont les mêmes clauses que celles de la commande CREATE EVENT, toutefois toutes les clauses de la commande ALTER EVENT sont optionnelles. Lorsqu’une clause n’est pas spécifiée dans la commande ALTER EVENT, la valeur originale pour cette clause appliquée lors de la commande CREATE EVENT ou bien lors de la dernière commande ALTER EVENT modifiant cette clause est conservée.

Ainsi , si la commande ALTER EVENT invoque la clause ON SCHEDULE, la programmation de l’évènement change ; dans le cas contraire la programmation de l’évènement reste inchangée.

La commande ALTER EVENT inclut une seule clause qui n’existe pas dans la commande CREATE EVENT : 'RENAME TO event_name2'. Cette clause permet de renommer un évènement, la commande RENAME EVENT n’existant pas.

Voici un exemple de commande ALTER EVENT qui modifie la programmation, le commentaire et la commande SQL déclenchée :

ALTER EVENT event1
      ON SCHEDULE EVERY 5 WEEK
      COMMENT 'This happens every 5 weeks'
      DO DROP TABLE t1;

DROP EVENT

Pour supprimer un évènement :

DROP EVENT [ IF EXISTS ] event_name;

Comme d’habitude, si la clause IF EXISTS est omise alors que l’évènement n’existe pas, le serveur retourne une erreur :

mysql > DROP EVENT e6 ;
ERROR 1513 (HY000) : Unknown event 'e6'

Méta-données sur les évènements

La table système mysql.event

Pour stocker les méta données des évènements, il y a une nouvelle table système dans la base de données mysql : mysql.event. Si cette table n’apparaît pas dans l’installation MySQL, exécuter le script mysql_fix_privilege_tables. (Lors de la migration depuis des versions précédentes de MySQL, il est impératif de toujours exécuter le script mysql_fix_privilege_tables pour créer les nouveaux objets et privilèges qui ont été ajoutés dans la nouvelle version).

La commande CREATE EVENT ajoute une nouvelle ligne dans la table mysql.event, la commande ALTER EVENT met à jour une ligne dans la table mysql.event, la commande DROP EVENT supprime une ligne dans la table mysql.event. Il est fortement déconseillé de modifier directement la table mysql.event.

Pour visualiser les informations sur les méta données des évènements :

mysql> SELECT * FROM mysql.event ;

Les commandes SHOW EVENTS, SHOW CREATE EVENT ou SELECT … FROM INFORMATION_SCHEMA.EVENTS ne sont proposées et supportées qu’à partir de MySQL 5.1.6.

Exemple :

mysql> CREATE EVENT e
       ON SCHEDULE EVERY 5 SECOND
       STARTS TIMESTAMP '2006-01-01 16:00:00'
       ENDS TIMESTAMP '2006-12-31 12:00:00'
       ON COMPLETION PRESERVE
       COMMENT 'runs every 5 seconds in 2006'
       DO INSERT INTO tx VALUES (CURRENT_TIMESTAMP, 10);

mysql> SELECT * FROM mysql.event\G
      
      *************************** 1. row ***************************
      db: tp
      name: e
      body: INSERT INTO tx VALUES (CURRENT_TIMESTAMP, 10)
      definer: tp@localhost
      execute_at: NULL
      interval_value: 5
      interval_field: SECOND
      created: 2005-12-20 14:53:03
      modified: 2005-12-20 14:53:03
      last_executed: NULL
      starts: 2006-01-01 23:00:00
      ends: 2006-12-31 20:00:00
      status: ENABLED
      on_completion: PRESERVE
      comment: runs every 5 seconds in 2006

Voici la description du résultat ci-dessus :

Les commandes SHOW EVENTS et SHOW CREATE EVENT

Commande SHOW CREATE EVENT

La commande SHOW CREATE EVENT permet de régénérer la commande de création d’un évènement :

SHOW CREATE EVENT event_name

Exemple :

mysql> SHOW CREATE EVENT test.e_daily\G
      
      *************************** 1. row ***************************
      Event: e_daily
      Create Event: CREATE EVENT e_daily
      ON SCHEDULE EVERY 1 DAY
      STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR
      ENABLE
      COMMENT 'Saves total number of sessions and
      clears the table once per day.'
      DO
      BEGIN
      INSERT INTO site_activity.totals (when, total)
      SELECT CURRENT_TIMESTAMP, COUNT(*)
      FROM site_activity.sessions;
      DELETE FROM site_activity.sessions;
      END

Le résultat de la commande SHOW CREATE EVENT retourne le statut courant de l’évènement (ENABLE) et non le statut lors de la création de l’évènement.

Commande SHOW EVENTS

Dans sa forme la plus simple, la commande SHOW EVENTS liste tous les évènements dans le schéma courant pour lequel l’utilisateur courant est le créateur des évènements.

mysql> SELECT CURRENT_USER(), SCHEMA();
+----------------+----------+
| CURRENT_USER() | SCHEMA() |
+----------------+----------+
| jon@ghidora    | myschema |
+----------------+----------+
mysql> SHOW EVENTS\G
*************************** 1. row ***************************
      Db: myschema
      Name: e_daily
      Definer: jon@ghidora
      Type: RECURRING
      Execute at: NULL
      Interval value: 10
      Interval field: INTERVAL_SECOND
      Starts: 0000-00-00 00:00:00
      Ends: 0000-00-00 00:00:00
      Status: ENABLED

La syntaxe complète de la commande SHOW EVENTS est la suivante :

mysql > SHOW [FULL] EVENTS [FROM schema_name] [LIKE pattern]

La vue INFORMATION_SCHEMA.EVENTS

La vue INFORMATION_SCHEMA.EVENTS comporte toutes les informations sur les évènements :

Voici un exemple d’interrogation de la vue INFORMATION_SCHEMA.EVENTS pour un évènement :

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
       WHERE EVENT_NAME = 'e_daily'
       AND EVENT_SCHEMA = 'myschema'\G
      
      *************************** 1. row ***************************
      EVENT_CATALOG: NULL
      EVENT_SCHEMA: myschema
      EVENT_NAME: e_daily
      DEFINER: jon@ghidora
      EVENT_BODY: BEGIN
      INSERT INTO site_activity.totals (when, total)
      SELECT CURRENT_TIMESTAMP, COUNT(*)
      FROM site_activity.sessions;
      DELETE FROM site_activity.sessions;
      END
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
      INTERVAL_VALUE: 1
      INTERVAL_FIELD: INTERVAL_DAY
      SQL_MODE: NULL
      STARTS: 2006-02-10 20:41:23
      ENDS: NULL
      STATUS: ENABLED
      ON_COMPLETION: DROP
      CREATED: 2006-02-09 14:35:35
      LAST_ALTERED: 2006-02-09 14:41:23
      LAST_EXECUTED: NULL
      EVENT_COMMENT: Saves total number of sessions and
      clears the table once per day.

Privilèges

Un nouveau privilège a été introduit pour les évènements : le privilège EVENT. Ce droit peut être donné pour une base de données ou toutes les bases de données :

GRANT EVENT ON database_name.* TO user [ , user ... ];
GRANT EVENT ON *.* TO user [ , user ... ];

Sans le privilège EVENT, un utilisateur ne peut créer d’évènement.

Le privilège EVENT ne peut être accordé que pour l’intégralité d’un schéma, il ne peut pas être attribué pour une seule table, voici l’erreur générée si l’on tente d’attribuer ce privilège uniquement pour une seule table :

mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please
consult the manual to see which privileges can be used

Pour retirer le droit de création d’évènements :

REVOKE EVENT ON database_name.* FROM user [ , user ... ];
REVOKE EVENT ON *.* FROM user [ , user ... ];

DELETE FROM mysql.event
WHERE db = 'myschema'
AND definer = 'jon@ghidora'
AND name = 'e_insert';

À partir de MySQL 5.1.7, les commandes DROP USER et DROP SCHEMA supprimeront en cascade les évènements associés à l’utilisateur à supprimer.

Pour retrouver les utilisateurs qui ont le privilège EVENT, il suffit de rechercher dans la table mysql.user toutes les lignes pour lesquelles Event_priv='Y'.

Statistiques sur les évènements

Cinq variables de statut sont introduites pour compter les opérations relatives aux évènements :

Pour visualiser les valeurs courantes de ces statistiques liées aux évènements :

mysql> show status like '%event%';

Troubleshooting des évènements

Deux méthodes sont disponibles pour vérifier les erreurs d’exécution des évènements :

• le fichier de log du serveur MySQL
• l’interrogation de la table INFORMATION_SCHEMA.EVENTS

Le fichier de log du serveur MySQL retourne les échecs d’exécution des évènements avec le mot clé RetCode=0 :

060209 22:39:44 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:44 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0
060209 22:39:54 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:54 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0
060209 22:40:04 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:40:04 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0

Lorsque la colonne LAST_EXECUTED est NULL dans la vue INFORMATION_SCHEMA.EVENTS pour un évènement qui aurait du se produire, cela signifie clairement que l’évènement a eu un statut d’échec :

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
         WHERE EVENT_NAME='e_store_ts'
         AND EVENT_SCHEMA='myschema'\G

*************************** 1. row ***************************
      EVENT_CATALOG: NULL
      EVENT_SCHEMA: myschema
      EVENT_NAME: e_store_ts
      DEFINER: jon@ghidora
      EVENT_BODY: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
      INTERVAL_VALUE: 5
      INTERVAL_FIELD: INTERVAL_SECOND
      SQL_MODE: NULL
      STARTS: 0000-00-00 00:00:00
      ENDS: 0000-00-00 00:00:00
      STATUS: ENABLED
      ON_COMPLETION: NOT PRESERVE
      CREATED: 2006-02-09 22:36:06
      LAST_ALTERED: 2006-02-09 22:36:06
      LAST_EXECUTED: NULL
      EVENT_COMMENT:

Le thread event_scheduler

Chaque évènement s’exécute avec son propre thread qui demeure quoiqu’il arrive unique. Pour illustrer tout ça, créons un mauvais évènement qui génère une boucle infinie.

Première étape : création d’une procédure stockée qui effectue une boucle infinie et ne s’arrête jamais.

DELIMITER //
CREATE PROCEDURE pe () BEGIN x: LOOP ITERATE x; END LOOP; END//

Seconde étape : création d’un évènement qui invoque la procédure stockée, évènement déclenché toutes les 2 secondes.

CREATE EVENT ee
      ON SCHEDULE EVERY 2 SECOND
      COMMENT 'This is a bad idea'
      DO CALL tp.pe() //

Si un évènement récurrent est encore en cours d’exécution alors qu’il est l’heure de déclencher une nouvelle exécution de cet évènement, le serveur n’ouvrira jamais un nouveau thread pour exécuter à nouveau l’évènement, ainsi le moteur MySQL garantit qu’il ne peut y avoir une infinité de threads suite à une erreur de développement ou bien qu’une concurrence d’un même évènement ne se produise. MySQL sait identifier un thread qui est rattaché à un évènement.

La commande SHOW PROCESSLIST permet de voir le thread du scheduler et celui de l’évènement en cours d’exécution :

mysql> SHOW PROCESSLIST\G
      
      *************************** 1. row ***************************
      Id: 1
      User: root
      Host: localhost
      db: tp
      Command: Query
      Time: 0
      State: NULL
      Info: show processlist
      *************************** 2. row ***************************
      Id: 2
      User: event_scheduler
      Host:
      db: NULL
      Command: Connect
      Time: 1
      State: Sleeping
      Info: NULL
      *************************** 3. row ***************************
      Id: 31
      User: root
      Host:
      db: tp
      Command: Connect
      Time: 0
      State: NULL
      Info: CALL tp.pe(
      3 rows in set (0.00 sec)

Quelques petites remarques

• Il est possible de créer deux évènements à la même programmation, mais il n’y a aucun moyen de contrôler l’ordonnancement de l’un par rapport à l’autre. L’évènement e1 peut démarrer avant l’évènement e2 et vice versa.
• Un évènement est toujours exécuté avec les privilèges du créateur.
• Les commandes SELECT et SHOW peuvent être implémentées dans les évènements mais les résultats ne seront jamais affichées ! Dans le jargon Unix, la sortie est redirigée vers /dev/null.
• Comme pour les routines (procédures stockées et fonctions), l’éxècution d’une commande dans un évènement ne modifie pas le nombre de fois que la commande a été lancée. Plus techniquement, il n’y a pas d’effet sur les variables de statut com_insert, com_delete, com_update, com_select ,etc. par rapport aux commandes déclenchées dans les évènements.
• Un évènement ne peut être être créé au sein de trigger, de procédures stockées ou d’un autre évènement. En revanche, un évènement peut créer , altérer, supprimer des triggers et des procédures stockées.