{"id":595,"date":"2009-04-20T14:34:37","date_gmt":"2009-04-20T13:34:37","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=595"},"modified":"2019-07-09T16:39:39","modified_gmt":"2019-07-09T15:39:39","slug":"le-programmateur-devenements-event-scheduler-part-16-2","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2009\/04\/20\/le-programmateur-devenements-event-scheduler-part-16-2\/","title":{"rendered":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 1\/6)"},"content":{"rendered":"

Poursuivons l’exploration des fonctionnalit\u00e9s phares de MySQL 5.1, et penchons-nous sur le programmateur d’\u00e9v\u00e8nements (Event Scheduler) pr\u00e9sent depuis MySQL 5.1.6 . Cet article est r\u00e9dig\u00e9 avec la version 5.1.22 de MySQL.<\/span><\/p>\n

<\/span><\/p>\n

Qu’est-ce qu’un programmateur d’\u00e9v\u00e8nements ?<\/strong><\/em><\/h4>\n

Le programmateur d’\u00e9v\u00e9nements ou \u00ab event scheduler \u00bb offre la possibilit\u00e9, \u00e0 l’administrateur de base de donn\u00e9es, de d\u00e9clencher l’ex\u00e9cution de requ\u00eates programm\u00e9es directement dans le serveur MySQL. Ce planificateur de t\u00e2ches (CRON-like) interne permet donc d’automatiser tr\u00e8s simplement des t\u00e2ches \u00e0 des intervalles r\u00e9guliers, ou \u00e0 heure fixe, sans avoir besoin de configurer le syst\u00e8me sur lequel la base de donn\u00e9es fonctionne.<\/p>\n

Comment fonctionne le programmateur d’\u00e9v\u00e8nements ?<\/h4>\n

La premi\u00e8re chose \u00e0 faire est de v\u00e9rifier si le programmateur d’\u00e9v\u00e8nements est activ\u00e9. Pour ce faire regardez la valeur du param\u00e8tre event_scheduler<\/em>:<\/p>\n

mysql> SHOW VARIABLES LIKE 'event_scheduler';\n+-----------------+-------+\n| Variable_name   | Value |\n+-----------------+-------+\n| event_scheduler | OFF   |\n+-----------------+-------+<\/pre>\n
Par d\u00e9faut le programmateur n’est pas activ\u00e9, event_scheduler vaut donc OFF. Activez le avec la commande SET GLOBAL (le droit SUPER est n\u00e9cessaire).<\/p>\n
mysql> SET GLOBAL event_scheduler = 1;<\/address>\n
mysql> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';\n+-----------------+-------+\n| Variable_name   | Value |\n+-----------------+-------+\n| event_scheduler | ON    |\n+-----------------+-------+<\/pre>\n
Un fois activ\u00e9, le programmateur d’\u00e9v\u00e8nements cr\u00e9\u00e9 un processus l\u00e9ger (thread) qui tourne en t\u00e2che de fond. Ce processus est charg\u00e9 d’ex\u00e9cuter les \u00e9v\u00e9nements lorsque le moment est venu. On peut le voir avec la commande SHOW PROCESSLIT<\/em>. Cependant, il faut avoir le privil\u00e8ge SUPER<\/em> pour pouvoir la lancer.<\/p>\n
mysql> SHOW PROCESSLIST \\G<\/address>\n
*************************** 2. row ***************************<\/address>\n
Id: 4<\/address>\n
User: event_scheduler<\/address>\n
Host: localhost<\/address>\n
db: NULL<\/address>\n
Command: Daemon<\/address>\n
Time: 0<\/address>\n
State: Waiting for next activation<\/address>\n
Info: NULL<\/address>\n
(\u00e0 suivre… event scheduler: Cr\u00e9er un \u00e9v\u00e8nement<\/a>)<\/em><\/p>\n
\n","protected":false},"excerpt":{"rendered":"
Poursuivons l’exploration des fonctionnalit\u00e9s phares de MySQL 5.1, et penchons-nous sur le programmateur d’\u00e9v\u00e8nements (Event Scheduler) pr\u00e9sent depuis MySQL 5.1.6 . Cet article est r\u00e9dig\u00e9 avec la version 5.1.22 de MySQL.<\/p>\n
Qu’est-ce qu’un programmateur d’\u00e9v\u00e8nements ?<\/p>\n
Le programmateur d’\u00e9v\u00e9nements ou \u00ab event scheduler \u00bb offre la possibilit\u00e9, \u00e0 l’administrateur de base de donn\u00e9es, de d\u00e9clencher l’ex\u00e9cution de requ\u00eates programm\u00e9es directement dans le serveur MySQL.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[8],"tags":[65,122],"class_list":["post-595","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-event-scheduler","tag-programmateur-devenements"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-9B","jetpack-related-posts":[{"id":641,"url":"https:\/\/dasini.net\/blog\/2009\/06\/29\/le-programmateur-d%e2%80%99evenements-event-scheduler-part-66\/","url_meta":{"origin":595,"position":0},"title":"Le programmateur d\u2019\u00e9v\u00e9nements ( Event Scheduler ) (part 6\/6)","author":"Olivier DASINI","date":"29 juin 2009","format":false,"excerpt":"Limitations et restrictions Certaines commandes MySQL ne sont pas permises dans un \u00e9v\u00e9nement. Verrouiller une table est interdit: LOCK TABLES, UNLOCK TABLES. Il n'est malheureusement pas possible de charger des donn\u00e9es avec la commande LOAD DATA INFILE. Les requ\u00eates pr\u00e9par\u00e9es (PREPARED STATEMENT) ne peuvent \u00eatre cr\u00e9\u00e9es dans un \u00e9v\u00e9nement.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":606,"url":"https:\/\/dasini.net\/blog\/2009\/04\/30\/le-programmateur-devenements-event-scheduler-part-26\/","url_meta":{"origin":595,"position":1},"title":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 2\/6)","author":"Olivier DASINI","date":"30 avril 2009","format":false,"excerpt":"Cr\u00e9er un \u00e9v\u00e9nement Pour cr\u00e9er notre premier \u00e9v\u00e9nement, un coup d'oeil dans la documentation de MySQL nous donne les informations suivantes:","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":275,"url":"https:\/\/dasini.net\/blog\/2008\/12\/03\/les-nouveautes-de-mysql-51-part-25\/","url_meta":{"origin":595,"position":2},"title":"Les nouveaut\u00e9s de MySQL 5.1 — (part 2\/5)","author":"Olivier DASINI","date":"3 d\u00e9cembre 2008","format":false,"excerpt":"Pouvoir automatiser ses t\u00e2ches de mani\u00e8re fiable et simple est le r\u00eave de tout administrateur de base de donn\u00e9es. Le programmateur d'\u00e9v\u00e8nements (Event Scheduler) est un planificateur de t\u00e2ches (CRON-like) embarqu\u00e9 dans MySQL 5.1.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":634,"url":"https:\/\/dasini.net\/blog\/2009\/06\/24\/le-programmateur-devenements-event-scheduler-part-56\/","url_meta":{"origin":595,"position":3},"title":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 5\/6)","author":"Olivier DASINI","date":"24 juin 2009","format":false,"excerpt":"Appel d'une proc\u00e9dure stock\u00e9e Il est possible d'appeler des proc\u00e9dures stock\u00e9es dans un \u00e9v\u00e9nement. mysql> CREATE EVENT `_event`.`appel_ps` ON SCHEDULE AT CURRENT_TIMESTAMP DO \/*Appel de la proc\u00e9dure stock\u00e9e*\/ CALL pro_stock.insert_profil('Olivier', 'DASINI', 'Orange');","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":614,"url":"https:\/\/dasini.net\/blog\/2009\/05\/06\/le-programmateur-devenements-event-scheduler-part-36\/","url_meta":{"origin":595,"position":4},"title":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 3\/6)","author":"Olivier DASINI","date":"6 mai 2009","format":false,"excerpt":"G\u00e9rer les \u00e9v\u00e8nements MySQL propose plusieurs m\u00e9thodes pour visualiser les \u00e9v\u00e8nements. La m\u00e9thode la plus pratique consiste \u00e0 aller chercher l'information dans la table event de la base de donn\u00e9es des m\u00e9ta-donn\u00e9es information_schema: mysql> SELECT * FROM information_schema.EVENTS;","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":622,"url":"https:\/\/dasini.net\/blog\/2009\/06\/16\/le-programmateur-devenements-event-scheduler-part-46\/","url_meta":{"origin":595,"position":5},"title":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 4\/6)","author":"Olivier DASINI","date":"16 juin 2009","format":false,"excerpt":"Insertions de donn\u00e9es dans une table, toutes les 30 secondes mysql> SELECT * FROM _event.insert_event ; mysql> DELIMITER \u20ac\u20ac mysql> CREATE EVENT `_event`.`second_event` ON SCHEDULE EVERY 30 SECOND DO BEGIN \/*selectionne la val maximale de la colonne fois. Si elle vaut NULL alors retourne la valeur 0 dans la variable\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/595","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=595"}],"version-history":[{"count":6,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/595\/revisions"}],"predecessor-version":[{"id":3280,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/595\/revisions\/3280"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=595"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=595"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=595"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}