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


1- Introduction

A 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…).

2- La variable globale système event_scheduler

2-1- 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

2-2- 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;

3- Commandes SQL de gestion des évènements

3-1- 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).

3-1-1- 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' signifie 'A 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 'A 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.

3-1-2- 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'éxécuter.

3-1-3- Clause ENABLED | DISABLED

Le mode ENABLED est 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é.

3-1-4- 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.

3-1-5- 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 //

3-2- 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;

3-3- 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'

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

4-1- 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 :

Colonne Description
db nom de la base de données qui contient l'événement.
name nom de l'événement, ce dernier doit être unique dans la base de données.
definer colonne qui permet de vérifier les privilèges de l'utilisateur quand l'événement est exécuté ; si l'utilisateur ne possède plus les privilèges requis pour éxécuter des évènements, une erreur est retournée.
execute_at indique quand l'évènement doit être exécuté lorsque cet évènement n'est pas cyclique, cette colonne a valeur NULL si la clause ON SCHEDULE utilise l'option EVERY.
interval_value indique la valeur numérique du cycle, cette colonne a la valeur NULL si la clause ON SCHEDULE utilise l'option AT.
interval_field indique l'unité du cycle (HOUR, SEC, MIN, DAY etc….), cette colonne a la valeur NULL si la clause ON SCHEDULE utilise l'option AT.
created date/heure de création de l'évènement.
modified date/heure de modification de l'événement. Cette colonne a la même valeur que la colonne created lorsqu'aucune commande ALTER EVENT n'est intervenu sur l'événement.
last_executed date/heure de la dernière exécution de l'événement. Cette colonne a la valeur NULL si l'événement n'a pas encore été déclenché.
starts indique la date/heure de démarrage de l'exécution de l'événement, cette colonne a la valeur NULL si la clause ON SCHEDULE utilise l'option AT.
ends indique la date/heure de fin de l'exécution de l'évènement, cette colonne a la valeur NULL si la clause ON SCHEDULE utilise l'option AT.
status indique si l'évènement est actif ou inactif (enabled | disabled). Dans cet exemple, l'événement est actif (ENABLED), ce qui est la valeur par défaut.
on_completion indique si l'évènement est conservé ou supprimé lorsqu'il est terminé au niveau de sa programmation. Dans cet exemple, l'événement est préservé, comme spécifié dans la commande CREATE EVENT.
comment commentaire inclus dans la commande CREATE EVENT.

4-2- Les commandes SHOW EVENTS et SHOW CREATE EVENT

4-2-1- Commande SHOW CREATE EVENT

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

Syntaxe : 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.

4-2-2- 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]

4-3- La vue INFORMATION_SCHEMA.EVENTS

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

INFORMATION_SCHEMA Name SHOW Name Remarques
EVENT_CATALOG NULL
EVENT_SCHEMA Db
EVENT_NAME Name
DEFINER Definer
EVENT_BODY
EVENT_TYPE Type
EXECUTE_AT Execute at
INTERVAL_VALUE Interval value
INTERVAL_FIELD Interval field
SQL_MODE
STARTS Starts
ENDS Ends
STATUS Status
ON_COMPLETION
CREATED
LAST_ALTERED
LAST_EXECUTED
EVENT_COMMENT

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.

5- 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 ... ];

Avec MySQL 5.1.6, si le user root ou tout autre utilisateur disposant de ce droit retire le privilège EVENT à un utilisateur, le retrait de ce droit ne supprime pas ou ne désactive pas en cascade les évènements créés par cet utilisateur. Pour supprimer les évènements créés par un autre utilisateur, la commande DELETE doit être directement lancée sur la table mysql.event. Par exemple, pour supprimer l'événement e_insert, root peut utiliser la commande ci-dessous :

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

A 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'.

6- Statistiques sur les évènements

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

Variable Description
com_create_event nombre de commandes CREATE EVENT exécutées depuis le démarrage du serveur
com_alter_event nombre de commandes ALTER EVENT exécutées depuis le démarrage du serveur
com_drop_event nombre de commandes DROP EVENT exécutées depuis le démarrage du serveur
com_show_create_event nombre de commandes SHOW CREATE EVENT exécutées depuis le démarrage du serveur
com_show_events nombre de commandes SHOW EVENTS exécutées depuis le démarrage du serveur

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

mysql > show status like '%event%';

7- 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, 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:

8- Le thread event_scheduler

Chaque évènement s'éxé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)

9- Quelques petites remarques


Annexe

Historique

Version Date Commentaires
1.0 02/2006 Version initiale
1.1 02/2006 Evolutions sur les méta données (SHOW EVENTS...) - Informations annexes (suppressions d'évènements etc...)

Liens

MySQL Books OnLine : Using the event scheduler