{"id":606,"date":"2009-04-30T10:23:52","date_gmt":"2009-04-30T09:23:52","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=606"},"modified":"2009-05-06T18:04:23","modified_gmt":"2009-05-06T17:04:23","slug":"le-programmateur-devenements-event-scheduler-part-26","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2009\/04\/30\/le-programmateur-devenements-event-scheduler-part-26\/","title":{"rendered":"Le programmateur d&rsquo;\u00e9v\u00e9nements ( Event Scheduler ) (part 2\/6)"},"content":{"rendered":"<p><!-- \t \t --><\/p>\n<address>(<a title=\"Le programmateur d\u2019\u00e9v\u00e9nements ( Event Scheduler ) (part 1\/6)\" href=\"http:\/\/dasini.net\/blog\/2009\/04\/20\/le-programmateur-devenements-event-scheduler-part-16-2\/\">&lt;- pr\u00e9c\u00e9dent<\/a>)<\/address>\n<p><!-- \t \t --><\/p>\n<h4>Cr\u00e9er un \u00e9v\u00e9nement<\/h4>\n<p>Pour cr\u00e9er notre premier \u00e9v\u00e9nement, un coup d&rsquo;oeil dans la documentation de MySQL nous donne les informations suivantes:<\/p>\n<pre>CREATE<\/pre>\n<pre>[DEFINER = { user | CURRENT_USER }]<\/pre>\n<pre>EVENT<\/pre>\n<pre>[IF NOT EXISTS]<\/pre>\n<pre><em>nom_\u00e9v\u00e8nement<\/em><\/pre>\n<pre>ON SCHEDULE <em>moment<\/em><\/pre>\n<pre>[ON COMPLETION [NOT] PRESERVE]<\/pre>\n<pre>[ENABLE | DISABLE | DISABLE ON SLAVE]<\/pre>\n<pre>[COMMENT '<em>commentaire<\/em>']<\/pre>\n<pre>DO <em>requ\u00eates_sql<\/em>;<\/pre>\n<pre><em>moment<\/em>:<\/pre>\n<pre>AT timestamp [+ INTERVAL intervalle] ...<\/pre>\n<pre>| EVERY interval<\/pre>\n<pre>[STARTS timestamp [+ INTERVAL intervalle] ...]<\/pre>\n<pre>[ENDS timestamp [+ INTERVAL intervalle] ...]<\/pre>\n<p>Les clauses entre crochets sont optionnelles, pour nous faciliter la t\u00e2che, ignorons les pour le moment. Comme tous les objets de la base de donn\u00e9es, notre \u00e9v\u00e9nement a un nom:  <em>nom_\u00e9v\u00e8nement <\/em><em>.<\/em> Il se lance \u00e0 un moment: <em>moment<\/em>, pour  ex\u00e9cuter des requ\u00eates:  <em>requ\u00eates_sql<\/em>.<\/p>\n<p>Le lancement de l&rsquo;\u00e9v\u00e8nement est soit ponctuel (<strong><em>AT<\/em><\/strong>), soit r\u00e9current (<strong><em>EVERY<\/em><\/strong>).<\/p>\n<p>Arm\u00e9 de ces quelques \u00e9l\u00e9ments, empressons-nous de cr\u00e9er notre premier \u00e9v\u00e9nement.<\/p>\n<p>Commen\u00e7ons par cr\u00e9er et nous connecter \u00e0 la base de donn\u00e9es dans laquelle se d\u00e9rouleront nos tests, la base de donn\u00e9es <em>_event<\/em>, et ensuite cr\u00e9ons la table <em>insert_event<\/em>, qui accueillera les donn\u00e9es ins\u00e9r\u00e9es gr\u00e2ce \u00e0 notre \u00e9v\u00e9nement.<\/p>\n<address>mysql&gt; CREATE DATABASE _event;<\/address>\n<address>mysql&gt; USE _event;<\/address>\n<address>mysql&gt; CREATE TABLE _event.insert_event (<\/address>\n<address>moment timestamp,<\/address>\n<address>fois tinyint unsigned,<\/address>\n<address>action char(50)<\/address>\n<address>)  ENGINE = MyISAM;<\/address>\n<address>enfin, cr\u00e9ons notre \u00e9v\u00e9nement:<\/address>\n<address> <\/address>\n<address>mysql&gt; CREATE EVENT _event.premier_event<\/address>\n<address>ON SCHEDULE AT CURRENT_TIMESTAMP<\/address>\n<address>COMMENT &lsquo;<em>Insertion des donn\u00e9es dans la table<\/em>&lsquo;<\/address>\n<address>DO<\/address>\n<address>INSERT INTO _event.insert_event ( moment, fois, action ) VALUES (now(), 0, &lsquo;Mon premier event&rsquo;);<\/address>\n<p>Quelques explications s&rsquo;imposent. L&rsquo;\u00e9v\u00e8nement <em>premier_event<\/em> s&rsquo;ex\u00e9cute d\u00e8s sa cr\u00e9ation, car le moment choisi est <em>CURRENT_TIMESTAMP<\/em>,de plus il ne s&rsquo;ex\u00e9cute qu&rsquo;un fois \u00e0 cause de la clause <em>AT<\/em>. Le comportement programm\u00e9 est ici tr\u00e8s simple, il s&rsquo;agit simplement d&rsquo;ins\u00e9rer un enregistrement dans la table <em>insert_event<\/em>. Pour le v\u00e9rifier il suffit de taper la requ\u00eate suivante:<\/p>\n<pre>mysql&gt; SELECT  *  FROM  insert_event;<\/pre>\n<pre>+---------------------+------+-------------------+<\/pre>\n<pre>| moment              | fois | action            |<\/pre>\n<pre>+---------------------+------+-------------------+<\/pre>\n<pre>| 2008-05-29 20:46:25 |    0 | Mon premier event |<\/pre>\n<pre>+---------------------+------+-------------------+<\/pre>\n<p>Notre \u00e9v\u00e9nement \u00e0 \u00e9galement laiss\u00e9 des traces dans le fichier de journalisation des erreurs (error log):<\/p>\n<address><em>080529 20:46:25 [Note] Event Scheduler: scheduler thread started with id 2<\/em><\/address>\n<address><em>080529 20:46:25 [Note] Event Scheduler: Last execution of _event.premier_event. Dropping.<\/em><\/address>\n<address><em>080529 20:46:25 [Note] Event Scheduler: [root@localhost].[_event.premier_event] started in thread 3.<\/em><\/address>\n<address><em>080529 20:46:25 [Note] Event Scheduler: Dropping _event.premier_event<\/em><\/address>\n<address><em>080529 20:46:25 [Note] Event Scheduler: [root@localhost].[_event.premier_event] executed successfully in thread 3.<\/em><\/address>\n<p>Ce fichier d&rsquo;erreurs nous apprend essentiellement deux choses: premi\u00e8rement que l&rsquo;\u00e9v\u00e8nement s&rsquo;est d\u00e9roul\u00e9 correctement (c&rsquo;est pour ainsi dire une bonne nouvelle) et  deuxi\u00e8mement que l&rsquo;\u00e9v\u00e8nement \u00e0 \u00e9t\u00e9 effac\u00e9. En effet, un \u00e9v\u00e9nement qui ne s&rsquo;ex\u00e9cute qu&rsquo;une seule fois n&rsquo;est par d\u00e9faut pas pr\u00e9serv\u00e9 (<em>ON COMPLETION NOT PRESERVE<\/em>) apr\u00e8s son ex\u00e9cution.<\/p>\n<p>Pour nous remettre de toutes ces \u00e9motions, explicitons les clauses optionnelles de la syntaxe du CREATE EVENT:<\/p>\n<p><strong>DEFINER<\/strong>: le cr\u00e9ateur de l&rsquo;\u00e9v\u00e8nement.<\/p>\n<p><strong>IF NOT EXIST<\/strong>S: clause permettant de ne pas g\u00e9n\u00e9rer de messages d&rsquo;erreurs si un \u00e9v\u00e8nement du m\u00eame nom existe au moment de la cr\u00e9ation.<\/p>\n<p><strong>ON COMPLETION [NOT] PRESERVE<\/strong>:  permet de rendre un \u00e9v\u00e9nement \u00e0 ex\u00e9cution unique, persistant dans la table syst\u00e8me <em>mysql.event<\/em> apr\u00e8s son ex\u00e9cution.<\/p>\n<p><strong>ENABLE | DISABLE | DISABLE ON SLAVE<\/strong>: active \/ d\u00e9sactive un \u00e9v\u00e9nement. Attention un \u00e9v\u00e9nement d\u00e9sactiv\u00e9 est effac\u00e9 au red\u00e9marrage du serveur MySQL.<\/p>\n<p><strong>COMMENT<\/strong> :  Ajouter un commentaire. (ce qui est, du reste, une excellente id\u00e9e)<\/p>\n<p><em>(<a title=\"Le programmateur d\u2019\u00e9v\u00e9nements ( Event Scheduler ) (part 3\/6)\" href=\"http:\/\/dasini.net\/blog\/2009\/05\/06\/le-programmateur-devenements-event-scheduler-part-36\/\">\u00e0 suivre&#8230; event scheduler: G\u00e9rer les \u00e9v\u00e8nements<\/a>)<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Cr\u00e9er un \u00e9v\u00e9nement<\/p>\n<p>Pour cr\u00e9er notre premier \u00e9v\u00e9nement, un coup d&rsquo;oeil dans la documentation de MySQL nous donne les informations suivantes:<\/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-606","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-9M","jetpack-related-posts":[{"id":634,"url":"https:\/\/dasini.net\/blog\/2009\/06\/24\/le-programmateur-devenements-event-scheduler-part-56\/","url_meta":{"origin":606,"position":0},"title":"Le programmateur d&rsquo;\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 &quot;MySQL&quot;","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":606,"position":1},"title":"Le programmateur d&rsquo;\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 &quot;MySQL&quot;","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":606,"position":2},"title":"Le programmateur d&rsquo;\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 &quot;MySQL&quot;","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":606,"position":3},"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 &quot;MySQL&quot;","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":606,"position":4},"title":"Le programmateur d&rsquo;\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 &quot;MySQL&quot;","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":606,"position":5},"title":"Les nouveaut\u00e9s de MySQL 5.1 &#8212; (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 &quot;MySQL&quot;","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\/606","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=606"}],"version-history":[{"count":7,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/606\/revisions"}],"predecessor-version":[{"id":609,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/606\/revisions\/609"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}