Le programmateur d'évènements sous MySQL.

Date de publication : 19 juin 2006

Date de mise à jour : 18 juin 2006

Par Alexandre TRANCHANT (CV)
Autres articles

Cet article présente une des nouvelles fonctionnalités de MySQL 5.1 : le programmateur d'évènements. Cette fonctionnalité permet de programmer des évènements qui vont déclencher des requêtes SQL, des procédures stockées. Seront présentés la mise en service et la désactivation de ce service, les manipulations d'évènements, leur activation et désactivation individuelles.

Prérequis:
  • Connaître les bases du langage SQL
 Il est conseillé de savoir :
  • Se connecter sur une base MySQL via la console
  • Installer une version 5.1.x de MySQL
 Vous allez apprendre à :
  • Programmer des évènements automatiques sous MySQL

Sommaire

1 - Présentation

1.1 - Définition de "Event Scheduler"

Vous lirez beaucoup de traductions plus ou moins déformées de cette expression. Mot à mot "Event Scheduler" signifie programmateur d'évènements. Ce processus (interne au moteur) déclenche des évènements en fonction de la date et de l'heure auxquelles ils sont programmés. Ce sont donc des déclencheurs temporels (en anglais triggers) qui vont ordonner l'exécution de l'évènement. L'évènement réalise une combinaison de procédures stockées ou de une à plusieurs requêtes SQL programmées par l'utilisateur. Le déclenchement peut être périodique et se lancer de une à plusieurs fois.

1.2 - Utilité du programmateur d'évènements

Le programmateur va vous permettre de configurer un ensemble de requêtes qui seront exécutées régulièrement. Voici quelques exemples de tâches que vous pourrez mettre en oeuvre, sans avoir à passer par des artifices de programmation de plus ou moins bonnes qualités. Vous pourrez, par exemple :

  • Programmer des requêtes de suppression pour délester de vieilles discussions pour votre forum.
  • Gestion de fin de tours pour les jeux Internet.
  • Calcul annuel d'intérêt.
  • Programmer des requêtes de sauvegardes automatiques chaque nuit.
  • Différer l'exécution d'un traitement gourmand en ressources aux heures creuses de la prochaine nuit.
  • Analyser et optimiser l'ensemble des tables mises à jour dans la journée

2 - Configuration du serveur

2.1 - Les prérequis

  1. Le programmateur d'évènements n'a été introduit que dans la version 5.1.6 de MySQL.
  2. L'équipe MySQL annonce qu'avant la version 5.1.12, un utilisateur ne pourra pas voir les évènements d'un autre utilisateur dans les informations du schéma de base (INFORMATION_SCHEMA.EVENTS)

A la date d'écriture de cet article, les versions 5.1.x sont des versions de développement et ne sont pas suffisamment stables pour être déployées sur un environnement de production.
A ce jour, la version stable est la 5.0.22. Quant à la version "béta" actuelle, il s'agit de la version 5.1.11-beta.

Voici d'ailleurs la requête qui vous permet de savoir quelle est la version installée :

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.1.11-beta |
+-------------+
1 row in set (0.00 sec)

2.2 - Etat d'activité du programmateur d'évènements

L'état d'activité du service des évènements est fourni par la requête suivante

mysql> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | 1     |
+-----------------+-------+
1 row in set (0.00 sec)

La variable event_scheduler peut avoir trois valeurs : 0, 1 et 2.

  • L'état 0 signifie que le service est volontairement coupé et ne pourra pas être activé sans un redémarrage et une modification des paramètres de configuration.
  • L'état 1 signifie que le service est actif.
  • L'état 2 signifie que le service est suspendu. Néanmoins une requête SQL peut à tout moment le réactiver

Remarquez que ces valeurs viennent de changer entre la version 5.1.9 et cette version 5.1.11. Auparavant les deux seules valeurs possibles étaient ON / OFF

Par défaut le service des évènements, chargé de déclencher les évènements est désactivé. Il existe trois méthodes pour l'activer : depuis le fichier de configuration, depuis la ligne de commande de démarrage du serveur MySQL ou par une requête SQL.

2.2.1 - Activation, désactivation et suspension depuis le fichier de configuration

Editez le fichier de configuration my.ini et remplacez, ou ajoutez à défaut, la ligne event-scheduler=VALEUR.

  • Si VALEUR vaut 0, alors le service de programmation d'évènements ne sera pas démarré ET ne pourra pas être démarré.
  • Si VALEUR vaut 1, alors le service de programmation d'évènements sera démarré et pourra être suspendu par une requête.
  • Si VALEUR vaut 2, alors le service de programmation d'évènements sera démarré mais suspendu. Il pourra être relancé par une requête.

2.2.2 - Activation, désactivation et suspension par option de la ligne de commande de mysqld-nt

Au lancement du processus/démon MySQL-NT, ajoutez à la ligne de commande l'option --event-scheduler=VALEUR.

  • Si VALEUR vaut 0, alors le service de programmation d'évènements ne sera pas démarré et ne pourra pas être démarré.
  • Si VALEUR vaut 1, alors le service de programmation d'évènements sera démarré et pourra être suspendu par une requête.
  • Si VALEUR vaut 2, alors le service de programmation d'évènements sera démarré mais suspendu. Il pourra être réactivé par une requête.

2.2.3 - Activation et suspension par requête SQL

Cette requête active le service si et seulement si son état n'est pas à zéro.

mysql> SET GLOBAL event_scheduler = 1 ;
Query OK, 0 row affected (0.02 sec)

Dans le cas où son état est à zéro, on obtient le message d'erreur suivant :

mysql> SET GLOBAL event_scheduler = 1 ;
ERROR 1290 (HY000): The MySQL server is running with the --event-scheduler=0
    option so it cannot execute this statement

Cette requête suspend le service si et seulement si son état n'est pas à zéro.

mysql> SET GLOBAL event_scheduler = 2 ;
Query OK, 0 row affected (0.02 sec)

2.3 - Privilèges

2.3.1 - Privilèges nécessaires

Tout utilisateur qui souhaite créer un évènement doit posséder les privilèges sur EVENT. De plus cet utilisateur doit posséder les privilèges nécessaires pour exécuter chacune des requêtes présentes dans l'évènement.

2.3.2 - Attribution des privilèges

La requête suivante donne à l'utilisateur UTILISATEUR les droits de créer un évènement pour la base ARTICLE, que nous étudierons plus loin dans cet article : 

mysql>GRANT EVENT ON ARTICLE.* TO UTILISATEUR;
Query OK, 1 row affected (0.06 sec)

La requête suivante les lui retire :

mysql>REVOKE EVENT ON ARTICLE.* FROM UTILISATEUR;
Query OK, 1 row affected (0.06 sec)

3 - Manipulation des évènements

3.1 - Modèle et jeu d'essai pour illustrer nos exemples.

Pour tester les évènements, nous allons disposer d'une table nommée DECLENCHEUR. Cette table va illustrer tous les exemples. La table DECLENCHEUR contient trois champs :

  • un champ ID_DECLENCHEUR autoincrémenté,
  • un champ INS_DECLENCHEUR qui, par défaut, est à la date courante,
  • un champ INFORMATION_DECLENCHEUR de type chaîne de caractères.

Le format de cette table va permettre de savoir à quelle heure un évènement est exécuté grâce au champ INS_DECLENCHEUR. INFORMATION_DECLENCHEUR sera initialisé avec le nom de l'exemple. Ainsi, nous saurons quel est l'évènement à l'origine de la création ou de la modification de l'enregistrement.

Le script suivant :

  • crée la base de données ARTICLE,
  • crée un utilisateur UTILISATEUR,
  • donne tous les droits nécessaires pour travailler sur cette base de données,
  • donne les droits de création d'évènements à cet utilisateur,
  • crée la table DECLENCHEUR,
  • crée une procédure qui insère également un enregistrement dans la table DECLENCHEUR.
mysql>CREATE DATABASE ARTICLE;
Query OK, 1 row affected (0.13 sec)
mysql>CREATE USER UTILISATEUR;
Query OK, 0 row affected (0.00 sec)
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON ARTICLE.* TO UTILISATEUR;
Query OK, 0 row affected (0.00 sec)
mysql>GRANT EVENT ON ARTICLE.* TO UTILISATEUR;
Query OK, 0 row affected (0.00 sec)
mysql>USE ARTICLE;
Database changed
mysql>CREATE TABLE DECLENCHEUR (
  ID_DECLENCHEUR INTEGER UNSIGNED AUTO_INCREMENT,
  INS_DECLENCHEUR TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INFORMATION_DECLENCHEUR VARCHAR(32),
  PRIMARY KEY PK_DECLENCHEUR(ID_DECLENCHEUR)
);
Query OK, 0 row affected (0.05 sec)
mysql> DELIMITER |
mysql>  CREATE PROCEDURE INSERTION ( IN EXEMPLE VARCHAR(20))
     BEGIN
       INSERT INTO DECLENCHEUR ( INFORMATION_DECLENCHEUR )
       VALUES ( CONCAT ( 'PROCEDURE : ', EXEMPLE ));
     END|
Query OK, 0 row affected (0.02 sec)
mysql> DELIMITER ;

Nous allons alors nous déconnecter pour nous reconnecter en tant que UTILISATEUR.

Remarque : Pour connaître les évènements que vous créez, vous trouverez ci-dessous une requête qui les liste. Nous détaillerons ces différentes commandes pour lister les évènements plus loin dans cet article. 

mysql> show events;
Empty set (0.02 sec)
Exemple 0.1 - Insertion manuelle d'une valeur. 
mysql>INSERT INTO DECLENCHEUR (INFORMATION_DECLENCHEUR) VALUES ('Exemple 0.1');
Query OK, 1 row affected (0.00 sec)
mysql>SELECT CURRENT_TIMESTAMP, DECLENCHEUR.* FROM DECLENCHEUR;
+---------------------+----------------+---------------------+-------------------------+
| CURRENT_TIMESTAMP   | ID_DECLENCHEUR | INS_DECLENCHEUR     | INFORMATION_DECLENCHEUR |
+---------------------+----------------+---------------------+-------------------------+
| 2006-06-10 22:11:16 |              1 | 2006-06-10 22:10:36 | Exemple 0.1             |
+---------------------+----------------+---------------------+-------------------------+
1 row in set (0.03 sec)

La requête de sélection permet de voir le contenu de la table DECLENCHEUR. Le premier champ CURRENT_TIMESTAMP permet également de faire savoir au lecteur à quel moment la sélection a été exécutée. Quant au troisième champ, INS_DECLENCHEUR, il permet de savoir à quel moment l'insertion a été effectuée.

3.2 - Programmation de nouveaux évènements

3.2.1 - Syntaxe

CREATE EVENT [IF NOT EXISTS] nom_evenement
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE]
    [COMMENT 'commentaire']
    DO requete_sql;

schedule:
    AT timestamp [+ INTERVAL intervalle]
  | EVERY intervalle [STARTS timestamp] [ENDS timestamp]

intervalle:
    quantite {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Remarque : La base de données sur laquelle vous travaillez n'est pas implicite, il faut donc toujours indiquer la base de données de la table. Ce n'est pas une option.

3.2.2 - Exemple de programmation d'évènements

Exemple 1.1 - Insérer une ligne dans la table DECLENCHEUR toutes les minutes. 
mysql>CREATE EVENT EXEMPLE_1_1
    ON SCHEDULE  EVERY 1 MINUTE
    DO INSERT INTO ARTICLE.DECLENCHEUR (INFORMATION_DECLENCHEUR) VALUES ('Exemple 1.1');

Query OK, 1 row affected (0.19 sec)

Le mot clef EVERY indique que l'évènement est récurrent. Il est suivi par l'intervalle entre chaque répétition.

Exemple 1.2 - Insérer une ligne dans la table DECLENCHEUR dans deux minutes. 
mysql>CREATE EVENT EXEMPLE_1_2
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 MINUTE
    DO INSERT INTO ARTICLE.DECLENCHEUR (INFORMATION_DECLENCHEUR) VALUES ('Exemple 1.2');

Query OK, 1 row affected (0.19 sec)

Le mot clef AT signifie que l'évènement est à exécution unique. Il est suivi par la date et l'heure de déclenchement. Après la date de déclenchement, l'évènement est automatiquement supprimé.

Exemple 1.3 - Lancer (une seule fois) une procédure stockée à 03h50 du 1er janvier 2010. 
mysql>CREATE EVENT EXEMPLE_1_3
    ON SCHEDULE AT '2010-01-01 03:50:00'
    DO CALL INSERTION('Exemple 1.3');

Query OK, 1 row affected (0.19 sec)

Remarque : Cet exemple n'a pas pris en compte le décalage horaire avec GMT ! 

mysql>SHOW EVENTS WHERE NAME='EXEMPLE_1_3'\G
*************************** 1. row ***************************
            Db: article
          Name: EXEMPLE_1_3
       Definer: UTILISATEUR@%
          Type: ONE TIME
    Execute at: 2006-06-11 01:50:00
Interval value: NULL
Interval field: NULL
        Starts: NULL
          Ends: NULL
        Status: ENABLED
1 row in set (0.00 sec)

Nous détaillerons les différentes commandes pour lister les évènements plus loin dans cet article.

Exemple 1.4.a - Insérer une ligne dans la table DECLENCHEUR chaque jour à 04h00 du matin. 
mysql>CREATE EVENT EXEMPLE_1_4_a
    ON SCHEDULE EVERY 1 DAY STARTS '2006-06-12 04:00:00'
    DO INSERT INTO ARTICLE.DECLENCHEUR (INFORMATION_DECLENCHEUR) VALUES ('Exemple 1.4.a');

Query OK, 1 row affected (0.05 sec)

Le mot clef STARTS permet d'indiquer quand l'évènement est déclenché pour la première fois. Il est donc suivi par la date de la première exécution.

Remarque : Attention à bien modifier la date de cet évènement. En effet, si vous laissez cette date révolue, alors vous obtiendrez le message suivant :
ERROR 1522 (HY000): Activation (AT) time is in the past.

Pour vous protéger de cela dans vos scripts d'initialisation, par exemple, préférez entrer la date de demain de façon dynamique comme dans l'exemple suivant.

Exemple 1.4.b - Insérer une ligne dans la table DECLENCHEUR chaque jour à 04h00 du matin à partir de demain matin 4 heures.
Dans un premier temps, recherchons la commande qui permet de récupérer dynamiquement la valeur de demain 04h00. Demain 04h00, c'est en fait la date (sans l'heure) du jour + un intervalle d'une journée + un intervalle de 4 heures. La requête est donc : 
mysql> SELECT CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 4 HOUR;
+-------------------------------------------------+
| CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 4 HOUR |
+-------------------------------------------------+
| 2006-06-10 04:00:00                             |
+-------------------------------------------------+
1 row in set (0.02 sec)

mysql>CREATE EVENT EXEMPLE_1_4_b
    ON SCHEDULE EVERY 1 DAY
    STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 4 HOUR
    DO INSERT INTO ARTICLE.DECLENCHEUR (INFORMATION_DECLENCHEUR)
    VALUES ('Exemple 1.4.b');

Query OK, 1 row affected (0.01 sec)
Exemple 1.5 - Pendant une minute, insérer une ligne dans la table DECLENCHEUR toutes les 8 secondes. 
mysql>CREATE EVENT EXEMPLE_1_5
    ON SCHEDULE  EVERY 8 SECOND
    ENDS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
    DO INSERT INTO ARTICLE.DECLENCHEUR (INFORMATION_DECLENCHEUR)
    VALUES ('Exemple 1.5');

Query OK, 1 row affected (0.01 sec)

Le mot clef ENDS permet d'indiquer quand l'évènement est déclenché pour la dernière fois. Il est donc suivi par la date de dernière exécution. Il pourra être automatiquement supprimé après la dernière exécution.

3.3 - Modification de la programmation des évènements

3.3.1 - Syntaxe

ALTER EVENT nom_evenement
    [ON SCHEDULE schedule]
    [RENAME TO nouveau_nom_evenement]
    [ON COMPLETION [NOT] PRESERVE]
    [COMMENT 'commentaire']
    [ENABLE | DISABLE]
    [DO requete_sql]

3.3.2 - Exemples

Exemple 2.1 - Renommer l'exemple 1.5. 
mysql>ALTER EVENT EXEMPLE_1_5
    RENAME TO NOUVEAU_EXEMPLE_1_5;

Query OK, 1 row affected (0.01 sec)
Exemple 2.1 - N'exécutez l'exemple 1.1 que toutes les 10 minutes. 
mysql>ALTER EVENT EXEMPLE_1_1
    ON SCHEDULE EVERY 10 MINUTE;

Query OK, 1 row affected (0.01 sec)

3.4 - Suppression d'évènements

3.4.1 - Syntaxe

DROP EVENT [IF EXISTS] nom_evenement

3.4.2 - Exemples

Exemple 3.1 - Suppression de l'exemple 1.1. 
mysql> DROP EVENT EXEMPLE_1_1;
1 rows affected (0.00 sec)
Variante si on n'est pas sûr de l'existence de l'évènement : 
mysql> DROP EVENT IF EXISTS EXEMPLE_1_1;
Query OK, 1 row affected, 1 warning (0.03 sec)

Remarque : Remarquez bien le code de retour, Aucune ligne n'a été affectée. Nous avions déjà effacé l'évènement. Par conséquent, l'évènement EXEMPLE_1_1 n'existe plus dans la base. MySQL retourne un avertissement.

3.5 - Activation d'un évènement

Activer un évènement l'autorise à s'exécuter. Attention néanmoins ! Si le gestionnaire d'évènement n'est pas activé, l'évènement ne sera jamais déclenché. Quel que soit son état !

3.5.1 - Syntaxe

ALTER EVENT nom_evenement ENABLE;

3.5.2 - Exemple

Exemple 4.1 - Activation de l'évènement EXEMPLE_1_4_b. 
mysql>ALTER EVENT EXEMPLE_1_4_b ENABLE;
Query OK, 1 row affected (0.00 sec)

3.6 - Désactivation d'un évènement

Chaque fois que l'évènement devrait s'exécuter, si il a été désactivé, le déclenchement n'aura pas lieu et le traitement ne démarrera pas.

3.6.1 - Syntaxe

ALTER EVENT nom_evenement DISABLE;

3.6.2 - Exemple

Exemple 5.1 - Désactivation de l'évènement EXEMPLE_1_4_b. 
mysql>ALTER EVENT EXEMPLE_1_4_b DISABLE;
Query OK, 1 row affected (0.00 sec)

3.7 - Connaître l'état d'activation d'un évènement

Pour plus d'informations, sur le schéma d'information des évènements rendez-vous au paragraphe 4.1.3.

Exemple 4.1 - Connaître l'état de l'évènement Exemple_1_2 
mysql>
SELECT EVENT_NAME, STATUS
  FROM INFORMATION_SCHEMA.EVENTS
 WHERE EVENT_NAME = 'EXEMPLE_1_2'
   AND EVENT_SCHEMA = 'article';
+-------------+---------+
| EVENT_NAME  | STATUS  |
+-------------+---------+
| EXEMPLE_1_2 | ENABLED |
+-------------+---------+
1 row in set (0.00 sec)
Exemple 4.2 - Connaître l'état de tous les évènements 
mysql>
SELECT EVENT_NAME, STATUS
 FROM INFORMATION_SCHEMA.EVENTS
 WHERE EVENT_SCHEMA = 'article';
 
+---------------------+---------+
| EVENT_NAME          | STATUS  |
+---------------------+---------+
| EXEMPLE_1_2         | ENABLED |
| EXEMPLE_1_3         | ENABLED |
| EXEMPLE_1_4_a       | ENABLED |
| EXEMPLE_1_4_b       | ENABLED |
| NOUVEAU_EXEMPLE_1_5 | ENABLED |
+---------------------+---------+
5 rows in set (0.01 sec)

4 - Suivi et analyse des évènements

4.1 - Listing des évènements

Il existe trois méthodes pour lister et récupérer toute information sur les évènements.

  • En récupérant les informations dans la table event du schéma mysql
  • Avec la procédure SHOW EVENTS
  • En utilisant le schéma d'information

4.1.1 - La table event du schéma mysql

La requête DESCRIBE mysql.event vous retournera les différentes colonnes de la table système event. Attention, vous devrez vous reconnecter en tant qu'utilisateur root pour pouvoir interroger cette table.

Ci-dessous, sont décrits les différents champs de la table mysql.event ainsi que leur utilité

  • db : Nom de la base de données à laquelle est rattachée l'évènement. Ce champ fait 64 caractères (longueur maximale du nom d'une base).
  • name : Nom de l'évènement. Ce champ fait 64 caractères (longueur maximale du nom d'un évènement).
  • body : Ensemble des requêtes et des procédures qui seront exécutées par l'évènement. Ce champ est un "longblob"
  • definer : Login de l'utilisateur ayant défini, créé, l'évènement. Ce champ fait 77 caractères.
  • execute_at : Date à laquelle l'évènement sera exécuté dans le cas d'un évènement à exécution unique. Si l'évènement s'exécute périodiquement, ce champ est NULL.
  • interval_value : Ce champ est un entier qui précise la valeur de l'intervalle d'exécution de l'évènement. Dans le cas d'un évènement unique ce champ est NULL.
  • interval_field : Ce champ est une énumération qui précise l'unité de l'intervalle d'exécution de l'évènement. Ce champ peut prendre l'une des valeurs :
    • YEAR : Années
    • QUARTER : Trimestres
    • MONTH : Mois
    • DAY : Jours
    • HOUR : Heures
    • MINUTE : Minutes
    • WEEK : Semaines
    • SECOND : Secondes
    • MICROSECOND : Secondes et microsecondes. Exemple : '3.121'
    • YEAR_MONTH : Années et mois. Exemple : '3:1'
    • DAY_HOUR : Jours et heures. Exemple : '2.23'
    • DAY_MINUTE : Minutes et minutes. Exemple : '17:12'
    • DAY_SECOND : Jours et secondes.
    • HOUR_MINUTE : Heures et minutes.
    • HOUR_SECOND : Heures et secondes.
    • MINUTE_SECOND : Minutes et secondes.
    • DAY_MICROSECOND : Jours et microsecondes.
    • HOUR_MICROSECOND : Heures et microsecondes
    • MINUTE_MICROSECOND : Minutes et microsecondes.
    • SECOND_MICROSECOND : Secondes et microsecondes.
  • created :Date de création (Format timestamp)
  • modified :Date de modification (Format timestamp)
  • last_executed :Date de dernière exécution (Format timestamp)
  • starts :Date à partir de laquelle l'évènement sera exécuté.
  • ends :Date de péremption à partir de laquelle l'évènement ne sera plus exécuté.
  • status :Etat d'activité de l'évènement. Le champ est une énumération qui peut prendre deux valeurs :
    • ENABLED : L'évènement est activé.
    • DISABLED : L'évènement est désactivé.
  • on_completion : Indique ce qu'il advient de l'évènement lorsqu'il atteint la date de péremption. Ce champ est une énumération qui peut prendre deux valeurs :
    • NOT PRESERVE : L'évènement sera automatiquement supprimé.
    • PRESERVE : L'évènement sera conservé (archive).
  • sql_mode :Mode SQL actif au moment de la création de l'évènement.
  • comment : Commentaire décrivant l'évènement. Ce champ est une chaîne de 64 caractères.

4.1.2 - La procédure SHOW EVENTS

Cette procédure retourne des informations sur les évènements de façon plus "cosmétique". 

mysql>SHOW EVENTS\G
*************************** 1. row ***************************
            Db: article
          Name: EXEMPLE_1_3
       Definer: UTILISATEUR@%
          Type: ONE TIME
    Execute at: 2010-01-01 02:50:00
Interval value: NULL
Interval field: NULL
        Starts: NULL
          Ends: NULL
        Status: ENABLED
1 row in set (0.00 sec)

En dehors des champs décrits dans le paragraphe précédent, se trouve un nouveau champ :

  • Type : Indique la nature de l'évènement. Ce champ peut prendre deux valeurs :
    • ONE TIME : L'évènement ne s'exécutera qu'une fois.
    • RECURRING : L'évènement est périodique.

Rappel : Attention, dans les versions actuelles, SHOW EVENT ne retourne que les évènements créés par l'utilisateur lançant cette interrogation. Même en se connectant avec le compte root, ces évènements n'apparaissent pas dans cette requête. 

mysql>SHOW EVENTS\G
Empty set (0.00 sec)

4.1.3 - La consultation des évènements depuis le schéma d'informations

mysql>SELECT * FROM INFORMATION_SCHEMA.EVENTS\G
*************************** 1. row ***************************
 EVENT_CATALOG: NULL
  EVENT_SCHEMA: article
    EVENT_NAME: EXEMPLE_1_3
       DEFINER: UTILISATEUR@%
    EVENT_BODY: CALL INSERTION('Exemple 1.3')
    EVENT_TYPE: ONE TIME
    EXECUTE_AT: 2010-01-01 02:50:00
INTERVAL_VALUE: NULL
INTERVAL_FIELD: NULL
      SQL_MODE:
        STARTS: NULL
          ENDS: NULL
        STATUS: ENABLED
 ON_COMPLETION: NOT PRESERVE
       CREATED: 2006-06-14 21:20:28
  LAST_ALTERED: 2006-06-14 21:20:28
 LAST_EXECUTED: NULL
 EVENT_COMMENT:
1 row in set (0.00 sec)

En dehors des champs décrits dans les deux paragraphes précédents, se trouve un nouveau champ :

  • EVENT_CATALOG : Chaque enregistrement pour ce champ aura toujours pour valeur : NULL.

4.2 - Journal d'erreur

Dans le journal d'erreur de MySQL, vous pouvez suivre l'exécution des évènements. Par défaut, le nom du journal d'erreur est nom_hote.err. Je démarre mon serveur, attends un instant et ouvre le journal d'erreur. 

060615 11:12:24 [Note] C:\mysql-5.1.11-beta-win32\bin\mysqld-nt.exe: ready for connections.
Version: '5.1.11-beta'  socket: ''  port: 3306  MySQL Community Server (GPL)
060615 11:12:24 [Note] SCHEDULER: Manager thread booting
060615 11:12:24 [Note] SCHEDULER: Loaded 6 events
060615 11:12:24 [Note] SCHEDULER: Manager thread started with id 1
060615 11:12:24 [ERROR] SCHEDULER: Found a disabled event article.NOUVEAU_EXEMPLE_1_5 in the queue
060615 11:12:24 [Note] SCHEDULER: [article.EXEMPLE_1_2 of UTILISATEUR@%] no more executions after this one
060615 11:12:24 [Note] SCHEDULER: [article.EXEMPLE_1_2 of UTILISATEUR@%] executing in thread 2
060615 11:12:24 [Note] SCHEDULER: [article.EXEMPLE_1_2 of UTILISATEUR@%] executed. RetCode=0

Dans cet extrait du journal, nous lisons les informations suivantes :

  • Le serveur démarre à 11h12m24s et est prêt.
  • Le "manager thread", c'est-à-dire le processus des évènements démarre.
  • Le serveur a chargé six évènements
  • Le processus des évènements à l'identifiant de connexion 1. Vous pouvez le vérifier en lançant (sous root) la commande SHOW PROCESSLIST;
  • L'évènement article.NOUVEAU_EXEMPLE_1_5 est désactivé, cela est traduit par un message d'erreur dans le journal
  • L'évènement article.EXEMPLE_1_2 devait s'exécuter dans la nuit, mais le serveur étant éteint, il se lance maintenant dans le processus numéro 2.Le code de retour est 0(ok)

5 - Évènements administratifs intéressants.

Dans ce chapitre, sont présentés un ensemble d'évènements qui peuvent automatiser certaines tâches de l'administrateur de base de données. Le grand avantage du programmateur d'évènements est qu'il nous rend "indépendant" du système d'exploitation.

5.1 - Exemples

Exemple 5.1 - Sauvegarde quotidienne d'une table sans interruption.

Pour cet exemple, nous utilisons le compte root. Sinon il faut donner le privilège FILE à l'utilisateur UTILISATEUR. Dans le répertoire des données de MySQL ( mysql/data/ par défaut) doit exister le sous-répertoire sauvegarde

mysql>CREATE EVENT EXEMPLE_BACKUP
    ON SCHEDULE EVERY 1 DAY
    STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 4 HOUR
    DO BACKUP TABLE DECLENCHEUR TO 'sauvegarde/';

Query OK, 1 row affected, 1 warning (0.00 sec)

L'avertissement est dû au fait que BACKUP sera déprécié. MySQL mettra en place un nouveau système de sauvegarde à chaud.

Exemple 5.2 - Restauration de la base de données à une certaine heure de la nuit.

Admettons que votre table DECLENCHEUR doive être restaurée. Vous ne souhaitez pas lancer la restauration immédiatement pour ne pas monopoliser trop de ressources pendant les heures de travail. Vous n'avez peut-être pas envie d'attendre la nuit devant votre ordinateur pour lancer le traitement. Grâce au programmateur d'évènements, même si vous n'avez pas accès au système d'exploitation de votre serveur, vous pouvez différer la restauration d'une table MyISAM. 

mysql>DELIMITER |
CREATE EVENT EXEMPLE_RESTORE
    ON SCHEDULE AT CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 5 HOUR
    DO BEGIN
        DROP TABLE ARTICLE.DECLENCHEUR;
        RESTORE TABLE ARTICLE.DECLENCHEUR FROM 'sauvegarde/';
    END |
Query OK, 1 row affected, 1 warning (0.00 sec)
DELIMITER ;

Néanmoins, comme la commande BACKUP, la commande RESTORE est dépréciée (obsolète) dans les nouvelles versions. Les développeurs de MySQL devraient mettre en place dans le futur un nouveau système de sauvegarde à chaud.

Exemple 5.3 - Analyse et optimisation de tables de la base de données.

Prenons une table dont les enregistrements sont de taille dynamique et qui est soumise régulièrement à des suppressions et des insertions d'enregistrement. Il est alors recommandé de régulièrement optimiser cette table. En effet, à chaque suppression, "des trous" apparaissent dans le fichier. Alors les insertions suivantes peuvent se faire dans "ces espaces". Néanmoins, cela génère une fragmentation des données dans le fichier. Il est alors recommandé, une fois par semaine à une fois par mois, d'optimiser la table. Cette opération va en quelque sorte défragmenter le fichier. Le programmateur d'évènement peut permettre de lancer régulièrement cette tâche. 

mysql>CREATE EVENT EXEMPLE_OPTIMIZE
    ON SCHEDULE EVERY 2 WEEK
	STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR
    DO OPTIMIZE TABLE ARTICLE.DECLENCHEUR;

Query OK, 1 row affected (0.00 sec)

5.2 - Précautions à prendre.

Soyez prudents avec ce genre de tâche lors du redémarrage de votre serveur ! En effet, si votre serveur redémarre après une nuit d'inactivité, alors les évènements qui auraient dû avoir lieu pendant la nuit vont se lancer au démarrage du serveur. Mais ces évènements sont lancés la nuit car ils sont justement gourmands en ressources systèmes ! Le redémarrage pourrait être alors assez long !

Pour éviter ce genre de tracas, démarrez toujours votre serveur avec en option le programmateur d'évènement en mode suspendu (mode 2). Ainsi après redémarrage du service, vous pourrez désactiver les évènements lourds. Puis, vous pourrez alors réactiver le processus de déclenchement (mode 1).

Mon conseil pour ce faire est le suivant :

  • Créez un évènement ACTIVE_EVENEMENT qui active les évènements gourmands. Cet évènement doit être désactivé par défaut (statut = DISABLED). De plus, après son utilisation, cet évènement doit être conservé (ON COMPLETION PRESERVE)
  • Pour le démarrage du serveur MySQL, créez un script shell qui démarre le serveur MySQL avec le processus de déclenchement des évènements suspendu. Puis ce script lance la console mysql avec un fichier sql. Ce fichier exécute les requêtes suivantes :
    • Désactivation des évènements "gourmands"
    • Activation complète du programmateur de tâche
    • Modification de notre évènement ACTIVE_EVENEMENT, de telle sorte qu'il s'active et qu'il s'exécute peu avant le démarrage théorique de notre premier évènement "gourmand".

6 - Conclusion sur le gestionnaire de MySQL

6.1 - Avantages du gestionnaire d'évènements de MySQL

Le service qui déclenche les évènements est en fait un processus qui tourne en interne comme on peut le voir avec la commande SHOW PROCESSLIST. Cela garantit leur déclenchement même quand le nombre maximum de connexions est atteint.

Dans la plupart des cas d'hébergement mutualisé, les webmestres n'ont pas accès à l'OS. Ils ne peuvent donc pas accéder aux commandes CRON ou AT. Le programmateur d'évènement est donc une solution. Maintenant, reste à savoir si les hébergeurs autoriseront leurs clients à utiliser cette fonctionnalité. En effet, il manque peut-être un élément de sécurité qui empêche de lancer des requêtes trop lourdes à chaque microseconde.

Contrairement à CRON, le programmateur possède une "option" de lancement à la seconde près.

6.2 - Manques du gestionnaire d'évènement de MySQL

Même si une procédure stockée peut pallier au problème, on peut regretter qu'un évènement ne puisse succéder à la fin d'un autre évènement.

Un évènement doit préciser le schéma de chaque table référencée. Par défaut, il n'utilise pas les tables de son propre schéma.

Il faut "pirater" la table système des évènements pour empêcher un "client" de lancer des processus lourds à chaque seconde

Le démarrage du service à la seconde près n'est pas très fiable. En effet, laissez l'exemple 1.1 tourner pendant un jour ou deux. Vous constaterez alors que les évènements ne se lancent pas à chaque minute précisément. Mais ils se lancent à chaque minute plus ou moins deux secondes.

mysql>SELECT * FROM DECLENCHEUR;
+----------------+---------------------+-------------------------+
| ID_DECLENCHEUR | INS_DECLENCHEUR     | INFORMATION_DECLENCHEUR |
+----------------+---------------------+-------------------------+
|            ... | ................... | ...........             |
|            201 | 2006-06-15 14:42:27 | Exemple 1.1             |
|            202 | 2006-06-15 14:43:27 | Exemple 1.1             |
|            203 | 2006-06-15 14:44:27 | Exemple 1.1             |
|            204 | 2006-06-15 14:45:28 | Exemple 1.1             |
|            205 | 2006-06-15 14:46:27 | Exemple 1.1             |
|            ... | ................... | ...........             |
+----------------+---------------------+-------------------------+
300 rows in set (0.00 sec)

7.1 - Ressources

Voici un ensemble de ressources qui vont vous permettre d'approfondir le sujet

Copyright © Alexandre TRANCHANT. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérêts. Cette page est déposée à la SACD.

Vos questions techniques : forum d'entraide SGBD & SQL - Publiez vos articles, tutoriels et cours
et rejoignez-nous dans l'équipe de rédaction du club d'entraide des développeurs francophones
Nous contacter - Hébergement - Participez - Copyright © 2000-2009 www.developpez.com - Legal informations.