{"id":614,"date":"2009-05-06T17:58:41","date_gmt":"2009-05-06T16:58:41","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=614"},"modified":"2009-06-16T10:59:36","modified_gmt":"2009-06-16T09:59:36","slug":"le-programmateur-devenements-event-scheduler-part-36","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2009\/05\/06\/le-programmateur-devenements-event-scheduler-part-36\/","title":{"rendered":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 3\/6)"},"content":{"rendered":"

<\/p>\n

(<- pr\u00e9c\u00e9dent<\/a>)<\/address>\n

<\/p>\n

G\u00e9rer les \u00e9v\u00e8nements<\/h4>\n

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<\/em> de la base de donn\u00e9es des m\u00e9ta-donn\u00e9es information_schema<\/em>:<\/p>\n

mysql> SELECT * FROM information_schema.EVENTS;<\/address>\n
Empty set (0.00 sec)<\/address>\n

L’\u00e9v\u00e8nement premier_event<\/em> cr\u00e9\u00e9 pr\u00e9c\u00e9demment n’y appara\u00eet pas. C’est tout \u00e0 fait normal, un \u00e9v\u00e9nement \u00e0 ex\u00e9cution unique n’est par d\u00e9faut pas conserv\u00e9 par MySQL. Modifions notre \u00e9v\u00e9nement pour qu’il puisse en rester une trace apr\u00e8s son ex\u00e9cution:
\n<\/p>\n

mysql> CREATE EVENT _event.premier_event_persistant<\/address>\n
ON SCHEDULE AT CURRENT_TIMESTAMP<\/address>\n
ON COMPLETION PRESERVE<\/address>\n
COMMENT ‘Insertion des donn\u00e9es dans la table’<\/address>\n
DO<\/address>\n
INSERT INTO _event.insert_event ( moment, fois, action ) VALUES (now(), 0, ‘Mon premier event persistant’);<\/address>\n
<\/address>\n
<\/address>\n
mysql> SELECT * FROM information_schema.EVENTS \\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
EVENT_CATALOG: NULL<\/address>\n
EVENT_SCHEMA: _event<\/address>\n
EVENT_NAME: premier_event_persistant<\/address>\n
DEFINER: daz@localhost<\/address>\n
TIME_ZONE: SYSTEM<\/address>\n
EVENT_BODY: SQL<\/address>\n
EVENT_DEFINITION: INSERT INTO _event.insert_event ( moment, fois, action ) VALUES (now(), 0, ‘Mon premier event persistant’)<\/address>\n
EVENT_TYPE: ONE TIME<\/address>\n
EXECUTE_AT: 2008-05-29 21:10:04<\/address>\n
INTERVAL_VALUE: NULL<\/address>\n
INTERVAL_FIELD: NULL<\/address>\n
SQL_MODE:<\/address>\n
STARTS: NULL<\/address>\n
ENDS: NULL<\/address>\n
STATUS: ENABLED<\/address>\n
ON_COMPLETION: PRESERVE<\/address>\n
CREATED: 2008-05-29 21:10:04<\/address>\n
LAST_ALTERED: 2008-05-29 21:10:04<\/address>\n
LAST_EXECUTED: NULL<\/address>\n
EVENT_COMMENT: Insertion des donn\u00e9es dans la table<\/address>\n
ORIGINATOR: 3307<\/address>\n
CHARACTER_SET_CLIENT: utf8<\/address>\n
COLLATION_CONNECTION: utf8_general_ci<\/address>\n
DATABASE_COLLATION: latin1_swedish_ci<\/address>\n


\nMySQL propose \u00e9galement deux autres commandes pour avoir des informations sur les \u00e9v\u00e8nements:<\/p>\n

SHOW EVENTS et SHOW CREATE EVENT:
\n<\/p>\n

mysql> SHOW EVENTS IN _event;<\/address>\n
*************************** 1. row ***************************<\/address>\n
Db: _event<\/address>\n
Name: premier_event_persistant<\/address>\n
Definer: daz@localhost<\/address>\n
Time zone: SYSTEM<\/address>\n
Type: ONE TIME<\/address>\n
Execute at: 2008-05-29 21:10:04<\/address>\n
Interval value: NULL<\/address>\n
Interval field: NULL<\/address>\n
Starts: NULL<\/address>\n
Ends: NULL<\/address>\n
Status: ENABLED<\/address>\n
Originator: 3307<\/address>\n
character_set_client: utf8<\/address>\n
collation_connection: utf8_general_ci<\/address>\n
Database Collation: latin1_swedish_ci<\/address>\n
<\/address>\n
<\/address>\n
<\/address>\n
mysql> SHOW CREATE EVENT _event.premier_event_persistant;<\/address>\n
*************************** 1. row ***************************<\/address>\n
Event: premier_event_persistant<\/address>\n
sql_mode:<\/address>\n
time_zone: SYSTEM<\/address>\n
Create Event: CREATE EVENT ‘premier_event_persistant’ ON SCHEDULE AT ‘2008-<\/address>\n
05-29 21:10:04′ ON COMPLETION PRESERVE ENABLE COMMENT ‘Insertion des donn\u00e9es dans la table DO INSERT INTO _event.insert_event ( moment,<\/address>\n
fois, action ) VALUES (now(), 0, ‘Mon premier event persistant’)<\/address>\n
character_set_client: utf8<\/address>\n
collation_connection: utf8_general_ci<\/address>\n
Database Collation: latin1_swedish_ci<\/address>\n


\nA noter, qu’il est \u00e9galement possible de lire l’information dans la table event<\/em> de la base de donn\u00e9es mysql<\/em> avec la requ\u00eate suivante: SELECT * FROM event .<\/p>\n

Vous avez aussi la possibilit\u00e9 de modifier la structure d’un \u00e9v\u00e9nement, le renommer, ou encore changer son code avec la commande: ALTER EVENT<\/em>
\n<\/p>\n

ALTER EVENT<\/address>\n
[DEFINER = { user | CURRENT_USER }]<\/address>\n
event_name<\/address>\n
[ON SCHEDULE schedule]<\/address>\n
[RENAME TO new_event_name]<\/address>\n
[ON COMPLETION [NOT] PRESERVE]<\/address>\n
[ENABLE | DISABLE | DISABLE ON SLAVE]<\/address>\n
[COMMENT ‘comment’]<\/address>\n
[DO sql_statement]<\/address>\n


\nOn retrouve les clauses de la commande CREATE EVENT<\/em>, que l’on peut donc modifier ais\u00e9ment avec ALTER EVENT<\/em>. Le dernier utilisateur qui modifie un \u00e9v\u00e9nement devient son DEFINER. Par exemple si un \u00e9v\u00e8nement \u00e0 pour DEFINER<\/em> daz@localhost<\/strong>. Une modification de cet \u00e9v\u00e9nement, par exemple le d\u00e9sactiver (DISABLE<\/em>), avec l’utilisateur root@localhost<\/strong> mettra le DEFINER<\/em> de l’\u00e9v\u00e8nement \u00e0 la valeur root@localhost<\/em>.<\/p>\n

D\u00e9sactiver un \u00e9v\u00e8nement<\/span>
\n<\/p>\n

mysql> ALTER EVENT _event.vue_materialisee DISABLE;<\/address>\n


\nIl est bien entendu possible d’effacer un \u00e9v\u00e9nement. Ceci se fait avec la commande DROP EVENT<\/em>.
\n<\/p>\n

DROP EVENT [IF EXISTS] nom_\u00e9v\u00e8nement<\/address>\n


\nEffacer un \u00e9v\u00e8nement<\/span>
\n<\/p>\n

mysql> DROP EVENT _event.vue_materialisee;<\/address>\n


\n(\u00e0 suivre… event scheduler: Quelques exemples d’\u00e9v\u00e8nements<\/a>)<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

G\u00e9rer les \u00e9v\u00e8nements<\/p>\n

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:
\nmysql> SELECT * FROM information_schema.EVENTS;<\/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":[104,65,122],"class_list":["post-614","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-event","tag-event-scheduler","tag-programmateur-devenements"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-9U","jetpack-related-posts":[{"id":606,"url":"https:\/\/dasini.net\/blog\/2009\/04\/30\/le-programmateur-devenements-event-scheduler-part-26\/","url_meta":{"origin":614,"position":0},"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":641,"url":"https:\/\/dasini.net\/blog\/2009\/06\/29\/le-programmateur-d%e2%80%99evenements-event-scheduler-part-66\/","url_meta":{"origin":614,"position":1},"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":595,"url":"https:\/\/dasini.net\/blog\/2009\/04\/20\/le-programmateur-devenements-event-scheduler-part-16-2\/","url_meta":{"origin":614,"position":2},"title":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 1\/6)","author":"Olivier DASINI","date":"20 avril 2009","format":false,"excerpt":"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. Qu'est-ce qu'un programmateur d'\u00e9v\u00e8nements ? Le programmateur d'\u00e9v\u00e9nements ou \u00ab event scheduler \u00bb offre la possibilit\u00e9, \u00e0 l'administrateur de\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":[]},{"id":622,"url":"https:\/\/dasini.net\/blog\/2009\/06\/16\/le-programmateur-devenements-event-scheduler-part-46\/","url_meta":{"origin":614,"position":3},"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":[]},{"id":634,"url":"https:\/\/dasini.net\/blog\/2009\/06\/24\/le-programmateur-devenements-event-scheduler-part-56\/","url_meta":{"origin":614,"position":4},"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":275,"url":"https:\/\/dasini.net\/blog\/2008\/12\/03\/les-nouveautes-de-mysql-51-part-25\/","url_meta":{"origin":614,"position":5},"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":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/614","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=614"}],"version-history":[{"count":6,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/614\/revisions"}],"predecessor-version":[{"id":619,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/614\/revisions\/619"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}