Les commandes MySQL liées aux EVENTS (scheduler/cron sous MySQL)
-- Vérifier que la fonctionnalité scheduler est activée, sans quoi vos events ne sont pas exécutés SHOW VARIABLES LIKE 'event_scheduler'; -- Activer le scheduler events SET GLOBAL event_scheduler = ON; # It is possible to set the Event Scheduler to DISABLED only at server startup. If event_scheduler is ON or OFF, you cannot set it to DISABLED at runtime. Also, if the Event Scheduler is set to DISABLED at startup, you cannot change the value of event_scheduler at runtime. -- Consulter la définition et exécutions des événements -- SHOW SCHEDULER STATUS; # uniquement MySQL 5.1.11, depuis MySQL 5.1.12, event scheduler status information can be obtained by running mysqladmin debug SHOW EVENTS; # à partir de MySQL 5.1.6. SELECT * FROM mysql.event; SELECT * FROM information_schema.EVENTS;
mysql> SELECT VERSION(); +-----------------------+ | VERSION() | +-----------------------+ | 5.1.58-1~dotdeb.0-log | +-----------------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.00 sec) mysql> SET GLOBAL event_scheduler = ON; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set (0.00 sec). mysql> SHOW EVENTS; ERROR 1046 (3D000): No database selected mysql> USE aaa_aaaa_bbbbbcccc; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW EVENTS; +--------------------+------+------------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation | +--------------------+------+------------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ | aaa_aaaa_bbbbbcccc | ttg | aaa_aaaa_bbbbb@% | SYSTEM | RECURRING | NULL | 5 | SECOND | 2012-03-08 13:43:23 | NULL | ENABLED | 0 | utf8 | utf8_general_ci | latin1_general_ci | +--------------------+------+------------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM mysql.event; +--------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+------+---------+---------------+----------+---------+------------+-----------+----------------------+----------------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | db | name | body | definer | execute_at | interval_value | interval_field | created | modified | last_executed | starts | ends | status | on_completion | sql_mode | comment | originator | time_zone | character_set_client | collation_connection | db_collation | body_utf8 | +--------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+------+---------+---------------+----------+---------+------------+-----------+----------------------+----------------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | aaa_aaaa_bbbbbcccc | ttg | BEGIN DECLARE done INT DEFAULT 0; DECLARE id_changed INT; DECLARE cur1 CURSOR FOR SELECT id FROM t_ttg; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO id_changed; DELETE FROM t_ttg WHERE t_ttg.id = id; UNTIL done END REPEAT; CLOSE cur1; END | aaa_aaaa_bbbbb@% | NULL | 5 | SECOND | 2012-03-08 13:43:23 | 2012-03-08 13:43:23 | 2012-03-09 08:30:13 | 2012-03-08 12:43:23 | NULL | ENABLED | DROP | | | 0 | SYSTEM | utf8 | utf8_general_ci | latin1_general_ci | BEGIN DECLARE done INT DEFAULT 0; DECLARE id_changed INT; DECLARE cur1 CURSOR FOR SELECT id FROM t_ttg; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO id_changed; DELETE FROM t_ttg WHERE t_ttg.id = id; UNTIL done END REPEAT; CLOSE cur1; END | +--------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+------+---------+---------------+----------+---------+------------+-----------+----------------------+----------------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM information_schema.EVENTS; +---------------+--------------------+------------+------------------+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+----------------+----------------+----------+---------------------+------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ | EVENT_CATALOG | EVENT_SCHEMA | EVENT_NAME | DEFINER | TIME_ZONE | EVENT_BODY | EVENT_DEFINITION | EVENT_TYPE | EXECUTE_AT | INTERVAL_VALUE | INTERVAL_FIELD | SQL_MODE | STARTS | ENDS | STATUS | ON_COMPLETION | CREATED | LAST_ALTERED | LAST_EXECUTED | EVENT_COMMENT | ORIGINATOR | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION | +---------------+--------------------+------------+------------------+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+----------------+----------------+----------+---------------------+------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ | NULL | aaa_aaaa_bbbbbcccc | ttg | aaa_aaaa_bbbbb@% | SYSTEM | SQL | BEGIN DECLARE done INT DEFAULT 0; DECLARE id_changed INT; DECLARE cur1 CURSOR FOR SELECT id FROM t_ttg; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO id_changed; DELETE FROM t_ttg WHERE t_ttg.id = id; UNTIL done END REPEAT; CLOSE cur1; END | RECURRING | NULL | 5 | SECOND | | 2012-03-08 13:43:23 | NULL | ENABLED | NOT PRESERVE | 2012-03-08 13:43:23 | 2012-03-08 13:43:23 | 2012-03-09 09:30:43 | | 0 | utf8 | utf8_general_ci | latin1_general_ci | +---------------+--------------------+------------+------------------+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+----------------+----------------+----------+---------------------+------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) mysql>
Attention au relancement de votre serveur MySQL
Par défaut il se lance en event_scheduler : OFF, donc si vous l'activez en cours de route "SET GLOBAL event_scheduler = ON;" et que vous rebootez, il sera désactivé.
Comment faire pour que les EVENTS soient activés dès le démarrage du serveur MySQL ? En configurant votre option event-scheduler = ON dans la section [mysqld]
# Ajout des EVENTS activés au boot event-scheduler = ON
Attention aux dumps avec mysqldump
Par défaut seul le dump des triggers est activé :
~# mysqldump --help| grep routine -R, --routines Dump stored routines (functions and procedures). routines FALSE ~# mysqldump --help| grep event -E, --events Dump events. events FALSE ~# mysqldump --help| grep trigger --triggers Dump triggers for each dumped table. triggers TRUE
Etant donné que vous utilisez désormais les EVENT MySQL il faudra rajouter dans vos dumps l'option (-E ou --events) à mysqldump.
Ressources
- Le programmateur d'évènements sous MySQL.
- MySQL 5.1 : La nouveauté du programmateur d'évènements (Event Scheduler)
- 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.
- Les commandes SELECT et SHOW peuvent être implémentées dans les évènements mais les résultats ne seront jamais affichées, car la sortie est redirigée vers /dev/null.
- 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.
- Using the Event Scheduler
- CREATE EVENT Syntax
- Event Scheduler Configuration
- Option event-scheduler définissant la variable event_scheduler