Le programmateur d'évènements sous MySQL

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.

I-A-1. Utilité du programmateur d'évènements▲

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

À 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.
À 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)

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.

II-B-1. Activation, désactivation et suspension depuis le fichier de configuration▲

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

2.2.3 Activation et suspension par requête SQL▲

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

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

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

II-C-1. Privilèges nécessaires▲

II-C-2. Attribution des privilèges▲

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

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

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.

III-B-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}

III-B-2. Exemple de programmation d'évènements▲

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)

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)

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)

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)

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)

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)

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)

III-C-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]

III-C-2. Exemples▲

mysql>ALTER EVENT EXEMPLE_1_5
    RENAME TO NOUVEAU_EXEMPLE_1_5;
 
Query OK, 1 row affected (0.01 sec)

mysql>ALTER EVENT EXEMPLE_1_1
    ON SCHEDULE EVERY 10 MINUTE;
 
Query OK, 1 row affected (0.01 sec)

III-D-1. Syntaxe▲

DROP EVENT [IF EXISTS] nom_evenement

III-D-2. Exemples▲

mysql> DROP EVENT EXEMPLE_1_1;
1 rows affected (0.00 sec)

mysql> DROP EVENT IF EXISTS EXEMPLE_1_1;
Query OK, 1 row affected, 1 warning (0.03 sec)

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 !

III-E-1. Syntaxe▲

ALTER EVENT nom_evenement ENABLE;

III-E-2. Exemple▲

mysql>ALTER EVENT EXEMPLE_1_4_b ENABLE;
Query OK, 1 row affected (0.00 sec)

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

III-F-1. Syntaxe▲

ALTER EVENT nom_evenement DISABLE;

III-F-2. Exemple▲

mysql>ALTER EVENT EXEMPLE_1_4_b DISABLE;
Query OK, 1 row affected (0.00 sec)

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)

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.

IV-A-1. La table event du schéma mysql▲

IV-A-2. La procédure SHOW EVENTS▲

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)

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

IV-A-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)

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

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)

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 ».

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.

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)