dasini.net – Journal d'un expert MySQL

Le Département de la Justice américaine a décidé de poursuivre ses investigations sur l’acquisition de Sun Microsystems (Java, OpenOffice,MySQL) par Oracle.

Oracle attendait le feu vert du Département de Justice pour mettre définitivement la main sur Sun Microsystems. L’opération tourne autour de 7,4 milliards de dollars. Problème, le Département a décidé d’approfondir cette acquisition par le géant des bases de données et du progiciel. La question est de savoir si Oracle-Sun va-t-il mettre à mal la sacro-sainte concurrence sur le sol américain.

L’os d’Oracle avec le Département de la justice vient des licences autour de Java. Le langage est à la fois un langage de programmation et une plateforme de développement. Oracle éditant ces propres plateformes de développement, le Département de la Justice a peur de voir la concurrence se réduire à un duopole Oracle(Java) – Microsoft (.Net).

L’autre problème vient des projets OpenSource de Sun et de l’attitude très propriétaires d’Oracle. Sun Microsystems possède ou participe à de nombreux projets OpenSource tel que la suite bureautique OpenOffice.org (basé sur une version de Sun StarOffice), la base de données MySQL, le système d’exploitation OpenSolaris, le Java OpenJDK ou la plateforme de développement Eclipse. Le passage sous Oracle fait craindre la disparition de certains projets, remplacé par des solutions propriétaires. L’annonce de la fermeture de Virtual Iron, uns société spécialisé dans la virtualisation acquis par Oracle un mois de cela, n’a fait qu’exacerber les craintes, en particulier sur MySQL.

http://www.echosdunet.net/dossiers/dossier_3709_oracle+sun+sous+loupe+justice+americaine.html

(<- précédent)

Limitations et restrictions

Certaines commandes MySQL ne sont pas permises dans un événement.

Verrouiller une table est interdit: LOCK TABLES, UNLOCK TABLES.

Il n’est malheureusement pas possible de charger des données avec la commande LOAD DATA INFILE.

Les requêtes préparées (PREPARED STATEMENT) ne peuvent être créées dans un événement.

La récursivité.

Un événement ne peut être créé, modifié ou supprimé par un déclencheur, une procédure stockée ou un autre événement.

De plus, on ne peut pas créer, modifier ou supprimer un déclencheur ou une procédure stockée dans un événement.

La suppression d’une base de données (DROP DATABASE) entraine la suppression de tous les objets qu’elle contient, donc les évènements aussi.

Un événement désactivé (DISABLE, DISABLE ON SLAVE) est supprimé en cas d’arrêt du serveur.

Le DEFINER doit avoir les droits nécessaires pour l’exécution de l’évènement, cet utilisateur doit bien entendu exister. S’il est supprimé, renommé ou si ses privilèges ne sont plus adaptés, l’exécution de l’évènement échoue.

mysql> DROP USER daz@localhost;

Query OK, 0 rows affected (0.00 sec)

En éditant le journal des erreurs:

080527 23:09:05 [Note] Event Scheduler: [daz@localhost].[_event.second_event] started in thread 46.

080527 23:09:05 [ERROR] Event Scheduler: [daz@localhost].[_event.second_event] execution failed, failed to authenticate the user.

080527 23:09:05 [ERROR] Event Scheduler: [daz@localhost][_event.second_event] There is no ‘daz’@’localhost’ registered

080527 23:09:05 [Note] Event Scheduler: [daz@localhost].[_event.second_event] event execution failed.

Conclusion

Comme vous avez pu le constater tout au long de cet article, le programmateur d’évènements est bien plus qu’un simple gadget, un véritable outil qui s’ajoute à la palette de l’administrateur de base de données MySQL.

Malgré certaines limitations, comme l’impossibilité de lancer des commandes systèmes ou des scripts externes, ce planificateur facilitera la vie du DBA en lui donnant la possibilité d’automatiser certaines tâches de maintenance.

(Début de l’article)

(<- précédent)

Appel d’une procédure stockée

Il est possible d’appeler des procédures stockées dans un événement.

mysql> CREATE EVENT `_event`.`appel_ps`

ON SCHEDULE AT CURRENT_TIMESTAMP

DO

/*Appel de la procédure stockée*/

CALL pro_stock.insert_profil(‘Olivier’, ‘DASINI’, ‘Orange’);

Archivage périodique des données

Beaucoup d’entreprises sont obligées de conserver d’énormes volumes de données historiques sur leurs serveurs de bases de données. Un moyen de réduire l’espace occupé est d’utiliser le moteur de stockage ARCHIVE. Ce moteur de table, compresse automatiquement les données qui n’occupent alors plus que 30% de leur taille originale.

mysql> CREATE TABLE _event.archive_event (

moment timestamp,

fois tinyint unsigned,

action char(50)

) ENGINE = ARCHIVE;

mysql> DELIMITER €€

mysql> CREATE EVENT _event.archive_base

ON SCHEDULE EVERY 1 DAY

DO

BEGIN

/*Insertion des données à archiver*/

INSERT INTO _event.archive_event

SELECT moment, fois, action

FROM _event.insert_event

WHERE moment < SUBDATE(now(), INTERVAL 1 DAY);

/*Suppressions des données archivées dans la table originale*/

DELETE FROM _event.insert_event WHERE moment < SUBDATE(now(), INTERVAL 1 DAY);

END €€

mysql> DELIMITER ;

(à suivre… event scheduler: Limitations et restrictions)

Le Forum PHP 2009 accueillera comme partenaire aux cotés de l’AFUP (Association Française des Utilisateurs PHP) l’association LeMug.fr (MySQL User Group).

Cet événement va se dérouler les 12 et 13 novembre 2009 à la cité des sciences à Paris.
Les appels à conférenciers et les pré-inscriptions sont ouvertes jusqu’à la fin du mois de juin.

Si vous désirez vous y rendre, vous pouvez vous inscrire.

Pour toutes les informations http://afup.org/pages/forumphp2009/

P.S. Je peux déjà vous annoncer (enfin on va faire durer un peu le suspense)  la présence d’une immense (M)ySQL star… 😉

(<- précédent)

Quelques exemples d’évènements

Insertions de données dans une table, toutes les 30 secondes

mysql> SELECT * FROM _event.insert_event ;

mysql> DELIMITER €€

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 @fois*/

SELECT if(max(fois),max(fois)+1,1) INTO @fois FROM _event.insert_event;

/*Insertion des données dans la table*/

INSERT INTO _event.insert_event (moment, fois, action) VALUES (now(), @fois, concat(‘Execution ‘, @fois,  » de l’event »));

END €€

mysql> DELIMITER ;

Créer une vue matérialisée

Le concept de vue matérialisée n’est pas implémenté dans MySQL. Une vue matérialisée est un « snapshot ». Ce type de vue permet de stocker le résultat d’une requête, contrairement aux vues classiques qui elles, ne stockent que la définition de la requête. Les vues matérialisées ne réexecutent donc pas la requête à chaque interrogation. Par contre la gestion de la fraîcheur des données est à la charge de la vue.

mysql> DELIMITER €€

mysql> CREATE EVENT `_event`.`vue_materialisee`

ON SCHEDULE EVERY 10 SECOND

STARTS ‘2008-03-11 16:32:34’

ON COMPLETION NOT PRESERVE ENABLE

DO

BEGIN

/*Effacement de la table _event.City_fra */

TRUNCATE TABLE _event.City_fra;

/*Remplassiage de la table _event.City_fra à partir de la table _event.City*/

INSERT INTO _event.City_fra

SELECT * FROM _event.City WHERE CountryCode=’FRA’

ORDER BY name;

END €€

mysql> DELIMITER ;

A noter que les commandes TRUNCATE et INSERT ne sont pas atomiques. Il est donc possible qu’une interrogation sur la table _event.City_fra ne renvoie pas de résultat.

(à suivre… event scheduler: Appel d’une procédure stockée)

(<- précédent)

Gérer les évènements

MySQL propose plusieurs méthodes pour visualiser les évènements. La méthode la plus pratique consiste à aller chercher l’information dans la table event de la base de données des méta-données information_schema:

mysql> SELECT * FROM information_schema.EVENTS;

Empty set (0.00 sec)

L’évènement premier_event créé précédemment n’y apparaît pas. C’est tout à fait normal, un événement à exécution unique n’est par défaut pas conservé par MySQL. Modifions notre événement pour qu’il puisse en rester une trace après son exécution:

mysql> CREATE EVENT _event.premier_event_persistant

ON SCHEDULE AT CURRENT_TIMESTAMP

ON COMPLETION PRESERVE

COMMENT ‘Insertion des données dans la table’

DO

INSERT INTO _event.insert_event ( moment, fois, action ) VALUES (now(), 0, ‘Mon premier event persistant’);

mysql> SELECT * FROM information_schema.EVENTS \G

*************************** 1. row ***************************

EVENT_CATALOG: NULL

EVENT_SCHEMA: _event

EVENT_NAME: premier_event_persistant

DEFINER: daz@localhost

TIME_ZONE: SYSTEM

EVENT_BODY: SQL

EVENT_DEFINITION: INSERT INTO _event.insert_event ( moment, fois, action ) VALUES (now(), 0, ‘Mon premier event persistant’)

EVENT_TYPE: ONE TIME

EXECUTE_AT: 2008-05-29 21:10:04

INTERVAL_VALUE: NULL

INTERVAL_FIELD: NULL

SQL_MODE:

STARTS: NULL

ENDS: NULL

STATUS: ENABLED

ON_COMPLETION: PRESERVE

CREATED: 2008-05-29 21:10:04

LAST_ALTERED: 2008-05-29 21:10:04

LAST_EXECUTED: NULL

EVENT_COMMENT: Insertion des données dans la table

ORIGINATOR: 3307

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: latin1_swedish_ci

MySQL propose également deux autres commandes pour avoir des informations sur les évènements:

SHOW EVENTS et SHOW CREATE EVENT:

mysql> SHOW EVENTS IN _event;

*************************** 1. row ***************************

Db: _event

Name: premier_event_persistant

Definer: daz@localhost

Time zone: SYSTEM

Type: ONE TIME

Execute at: 2008-05-29 21:10:04

Interval value: NULL

Interval field: NULL

Starts: NULL

Ends: NULL

Status: ENABLED

Originator: 3307

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

mysql> SHOW CREATE EVENT _event.premier_event_persistant;

*************************** 1. row ***************************

Event: premier_event_persistant

sql_mode:

time_zone: SYSTEM

Create Event: CREATE EVENT ‘premier_event_persistant’ ON SCHEDULE AT ‘2008-

05-29 21:10:04′ ON COMPLETION PRESERVE ENABLE COMMENT ‘Insertion des données dans la table DO INSERT INTO _event.insert_event ( moment,

fois, action ) VALUES (now(), 0, ‘Mon premier event persistant’)

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

A noter, qu’il est également possible de lire l’information dans la table event de la base de données mysql avec la requête suivante: SELECT * FROM event .

Vous avez aussi la possibilité de modifier la structure d’un événement, le renommer, ou encore changer son code avec la commande: ALTER EVENT

ALTER EVENT

[DEFINER = { user | CURRENT_USER }]

event_name

[ON SCHEDULE schedule]

[RENAME TO new_event_name]

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT ‘comment’]

[DO sql_statement]

On retrouve les clauses de la commande CREATE EVENT, que l’on peut donc modifier aisément avec ALTER EVENT. Le dernier utilisateur qui modifie un événement devient son DEFINER. Par exemple si un évènement à pour DEFINER daz@localhost. Une modification de cet événement, par exemple le désactiver (DISABLE), avec l’utilisateur root@localhost mettra le DEFINER de l’évènement à la valeur root@localhost.

Désactiver un évènement

mysql> ALTER EVENT _event.vue_materialisee DISABLE;

Il est bien entendu possible d’effacer un événement. Ceci se fait avec la commande DROP EVENT.

DROP EVENT [IF EXISTS] nom_évènement

Effacer un évènement

mysql> DROP EVENT _event.vue_materialisee;

(à suivre… event scheduler: Quelques exemples d’évènements)

(<- précédent)

Créer un événement

Pour créer notre premier événement, un coup d’oeil dans la documentation de MySQL nous donne les informations suivantes:

CREATE

[DEFINER = { user | CURRENT_USER }]

EVENT

[IF NOT EXISTS]

nom_évènement

ON SCHEDULE moment

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT 'commentaire']

DO requêtes_sql;

moment:

AT timestamp [+ INTERVAL intervalle] ...

| EVERY interval

[STARTS timestamp [+ INTERVAL intervalle] ...]

[ENDS timestamp [+ INTERVAL intervalle] ...]

Les clauses entre crochets sont optionnelles, pour nous faciliter la tâche, ignorons les pour le moment. Comme tous les objets de la base de données, notre événement a un nom: nom_évènement . Il se lance à un moment: moment, pour exécuter des requêtes: requêtes_sql.

Le lancement de l’évènement est soit ponctuel (AT), soit récurrent (EVERY).

Armé de ces quelques éléments, empressons-nous de créer notre premier événement.

Commençons par créer et nous connecter à la base de données dans laquelle se dérouleront nos tests, la base de données _event, et ensuite créons la table insert_event, qui accueillera les données insérées grâce à notre événement.

mysql> CREATE DATABASE _event;

mysql> USE _event;

mysql> CREATE TABLE _event.insert_event (

moment timestamp,

fois tinyint unsigned,

action char(50)

) ENGINE = MyISAM;

enfin, créons notre événement:

mysql> CREATE EVENT _event.premier_event

ON SCHEDULE AT CURRENT_TIMESTAMP

COMMENT ‘Insertion des données dans la table‘

DO

INSERT INTO _event.insert_event ( moment, fois, action ) VALUES (now(), 0, ‘Mon premier event’);

Quelques explications s’imposent. L’évènement premier_event s’exécute dès sa création, car le moment choisi est CURRENT_TIMESTAMP,de plus il ne s’exécute qu’un fois à cause de la clause AT. Le comportement programmé est ici très simple, il s’agit simplement d’insérer un enregistrement dans la table insert_event. Pour le vérifier il suffit de taper la requête suivante:

mysql> SELECT  *  FROM  insert_event;

+---------------------+------+-------------------+

| moment              | fois | action            |

+---------------------+------+-------------------+

| 2008-05-29 20:46:25 |    0 | Mon premier event |

+---------------------+------+-------------------+

Notre événement à également laissé des traces dans le fichier de journalisation des erreurs (error log):

080529 20:46:25 [Note] Event Scheduler: scheduler thread started with id 2

080529 20:46:25 [Note] Event Scheduler: Last execution of _event.premier_event. Dropping.

080529 20:46:25 [Note] Event Scheduler: [root@localhost].[_event.premier_event] started in thread 3.

080529 20:46:25 [Note] Event Scheduler: Dropping _event.premier_event

080529 20:46:25 [Note] Event Scheduler: [root@localhost].[_event.premier_event] executed successfully in thread 3.

Ce fichier d’erreurs nous apprend essentiellement deux choses: premièrement que l’évènement s’est déroulé correctement (c’est pour ainsi dire une bonne nouvelle) et deuxièmement que l’évènement à été effacé. En effet, un événement qui ne s’exécute qu’une seule fois n’est par défaut pas préservé (ON COMPLETION NOT PRESERVE) après son exécution.

Pour nous remettre de toutes ces émotions, explicitons les clauses optionnelles de la syntaxe du CREATE EVENT:

DEFINER: le créateur de l’évènement.

IF NOT EXISTS: clause permettant de ne pas générer de messages d’erreurs si un évènement du même nom existe au moment de la création.

ON COMPLETION [NOT] PRESERVE: permet de rendre un événement à exécution unique, persistant dans la table système mysql.event après son exécution.

ENABLE | DISABLE | DISABLE ON SLAVE: active / désactive un événement. Attention un événement désactivé est effacé au redémarrage du serveur MySQL.

COMMENT : Ajouter un commentaire. (ce qui est, du reste, une excellente idée)

(à suivre… event scheduler: Gérer les évènements)

Poursuivons l’exploration des fonctionnalités phares de MySQL 5.1, et penchons-nous sur le programmateur d’évènements (Event Scheduler) présent depuis MySQL 5.1.6 . Cet article est rédigé avec la version 5.1.22 de MySQL.

Qu’est-ce qu’un programmateur d’évènements ?

Le programmateur d’événements ou « event scheduler » offre la possibilité, à l’administrateur de base de données, de déclencher l’exécution de requêtes programmées directement dans le serveur MySQL. Ce planificateur de tâches (CRON-like) interne permet donc d’automatiser très simplement des tâches à des intervalles réguliers, ou à heure fixe, sans avoir besoin de configurer le système sur lequel la base de données fonctionne.

Comment fonctionne le programmateur d’évènements ?

La première chose à faire est de vérifier si le programmateur d’évènements est activé. Pour ce faire regardez la valeur du paramètre event_scheduler:

mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

Par défaut le programmateur n’est pas activé, event_scheduler vaut donc OFF. Activez le avec la commande SET GLOBAL (le droit SUPER est nécessaire).

mysql> SET GLOBAL event_scheduler = 1;

mysql> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

Un fois activé, le programmateur d’évènements créé un processus léger (thread) qui tourne en tâche de fond. Ce processus est chargé d’exécuter les événements lorsque le moment est venu. On peut le voir avec la commande SHOW PROCESSLIT. Cependant, il faut avoir le privilège SUPER pour pouvoir la lancer.

mysql> SHOW PROCESSLIST \G

*************************** 2. row ***************************

Id: 4

User: event_scheduler

Host: localhost

db: NULL

Command: Daemon

Time: 0

State: Waiting for next activation

Info: NULL

(à suivre… event scheduler: Créer un évènement)

Un conférence de Serge Frezefond, directeur technique SUN / MySQL France, au salon Solutions Linux 2009.
Serge nous parle des meilleurs pratiques MySQL sur environnement Linux

http://dasini.net/blog/presentations/?#MySQL_Bests_practices_on_Linux

télécharger la présentation (PDF)

MySQL possède un grand nombre de fonctions pour gérer les dates et le temps. Il est possible de récupérer le numéro calendaire d’une semaine avec la fonction week. Dimanche 5 avril nous étions semaine 14. La fonction week nous le confirme:

mysql> SELECT week('2009-04-05');
+--------------------+
| week('2009-04-05') |
+--------------------+
|                 14 |
+--------------------+

Lundi 6 avril, nouvelle semaine, on passe donc à la semaine 15:

mysql> SELECT week('2009-04-06');
+--------------------+
| week('2009-04-06') |
+--------------------+
|                 14 |
+--------------------+

Souci !!! Le résultat n’est pas celui espéré (14 au lieu de 15). En fait la fonction week prend un deuxième paramètre optionnel, qui permet de « régler la précision » (compter sur 53 ou 54 semaines, commencer la semaine un dimanche ou un lundi…) bref elle est complète et complexe…

Le réglage usuel est de mettre le deuxième paramètre à 3 ! ou alors d’utiliser la fonction WEEKOFYEAR.

mysql> SELECT weekofyear('2009-04-05');
+--------------------------+
| weekofyear('2009-04-05') |
+--------------------------+
|                       14 |
+--------------------------+

mysql> SELECT weekofyear('2009-04-06');
+--------------------------+
| weekofyear('2009-04-06') |
+--------------------------+
|                       15 |
+--------------------------+