Dec/081
Event Scheduler
Je ne sais pas si je suis le seul à penser ça, mais je trouve qu’il y a peu de ressources (ou alors, je ne les connais pas) distribuant des exemples de procédures stockées, de fonctions, de triggers ou d’events scheduler. Pour pallier ce manque, je viens de publier une page avec le code d’un Event.
Je compte faire une page (et non un post, pour pas que ça tombe archiver avec le temps) avec des exemples de chaque type de routine, avec leurs particularités. Mon Event est donc le premier exemple d’une petite série à venir. Je n’explique pas en détail chaque ligne; je montre les possibilités et j’invite les gens intéressés à consulter la documentation pour mieux apprendre sur chaques features.
Mon exemple se trouve donc ici : http://www.noidea.ca/mysql-event-scheduler/
Nov/082
Tout ce qu’il faut savoir sur les routines
La syntaxe SQL pour créer une procédure stockée ou une fonction est simple. Cependant, il y a plusieurs choses à savoir pour créer et utiliser une routine. Tout d’abord, il faut connaitre les trois nouveaux privilèges liés aux routines:
- Create routine pour créer des routines (procédure stockée ou fonction)
- Alter routine pour modifier ou supprimer une routine. Ce privilège est automatiquement donné au créateur de la routine.
- Execute pour les exécuter. Ce privilège est également donné automatiquement au créateur.
Si l’option automatic_sp_privilèges est à 0, Alter routine et Execute ne seront pas automatiquement attribués au créateur. Il faut aussi savoir que ces 3 privilèges ne sont pas les seuls éléments de sécurité. Avec les procédures stockées, la caractéristique SQL SECURITY peut être utilisée pour définir si c’est les privilèges du créateur ou de celui qui l’exécute qui doivent être utilisés. La valeur par défaut est definer.
Le privilège SUPER doit être donné aux utilisateurs qui crée et exécute des routines sur des serveurs ayant le binary log activé, en plus des privilèges Create routine et alter routine. Vous conviendrez que donner un tel privilège n’est pas toujours souhaitable, donc MySQL s’est doté de l’option log_bin_trust_routine_creators (désactivée par défaut) pour pallier ce “problème”.
On pourrait se demander pourquoi cette option est désactivée par défaut. La réponse est simple: par souci de sécurité. Lorsque le binlog est activé, la création de routine doit absolument avoir DETERMINISTIC, NO SQL ou READ SQL DATA dans sa déclaration.
- Les caractéristiques DETERMINISTIC et NOT DETERMINISTIC indiquent si la routine produit toujours le même résultat pour la même entrée. Pour la déclarer DETERMINISTIC, vous devez le spécifier explicitement. Le défaut est NOT DETERMINISTIC ce qui signifie que la routine peut retourner des résultats différents pour la même entrée.
- Les caractéristiques CONTAINS SQL, NO SQL, READS SQL DATA et MODIFIES SQL DATA indiquent si la routine sélectionne ou modifie des données. Le défaut est CONTAINS SQL si aucune caractéristique n’est spécifiée.
L’option log_bin_trust_routine_creators permet d’outre passer cette sécurité. Si elle est activée, le créateur d’une fonction peut déclarer qu’elle est not deterministic. C’est pourquoi il est conseillé d’activer cette option seulement si vous avez une confiance entière aux usagers qui peuvent créer des routines. Une telle routine peut causer d’importants problèmes:
- Rendre les Slaves différents du master, puisqu’il n’y a aucune garantie que le résultat sera le même
- Les données restaurées peuvent être différentes des données originales
Comme vous devez vous douter, les routines sont stockées directement dans le serveur. Pour se faire, MySQL utilise 3 nouvelles tables: information_schema.routine, mysql.proc et mysql.proc_priv. Si vous upgradez un serveur à partir d’une version inférieure à MySQL 5, il faut s’assurer que ces tables sont présentes. Vous pouvez utiliser le script mysql_upgrade pour les ajouter.
Pour conclure, vous devez aussi savoir que le code qui constitue une routine est stocké différemment du code utilisé pour la créer. Les commentaires sont supprimés, c’est pourquoi il est conseillé de toujours conserver une copie du code de votre routine dans un fichier texte à l’extérieur du serveur.
Bien sur, il y a beaucoup d’autres choses à savoir, mais je crois que ça fait le tour de ce qui est essentiel!
Sep/082
Performance avec les procédures stockées
Les procédures stockées ont fait leur apparition avec MySQL 5.0. Une procédure stockée est un ensemble de plusieurs requêtes basées sur le standard sql:2003, regroupées ensemble et stockées dans la base de données. On leur attribut plusieurs avantages, notamment:
- Elles réduisent le trafic réseau: on peut exécuter plusieurs requêtes avec un seul échange entre le client et le serveur.
- Elles offrent un contrôle de sécurité: un user peut exécuter une procédure qui fait des requêtes sur une ou des tables auxquelles il n’a pas accès. Ces requêtes peuvent être en lecture ou en écriture.
- Elles assurent le respect de logiques particulières ou d’intégrité.
J’avais un cas où une application devait faire très régulièrement (jusqu’à plusieurs fois par secondes) les 4 mêmes requêtes: SELECT.. UPDATE.. SELECT.. UPDATE. En déplaçant ces 4 requêtes dans une procédure stockée, j’ai sauvé 3 échanges entre le client et le serveur, quelques boucles dans le code et l’instanciation de quelques objets PHP. Au total, le même processus est devenu 46% plus rapide.
L’utilisation de procédure n’est pas sans risque. En effet, la souplesse de MySQL peut rendre la création et l’exécution d’une procédure valide, alors qu’elle ne le devrait pas. C’est pourquoi il est recommandé de créer la procédure en strict mode. Ainsi, vous serez averti si une de vos opérations repose sur un comportement variable.
Il y a plusieurs niveaux de sécurité reliés aux procédures. Tout d’abord, MySQL 5.0 possède de nouveaux privilèges permettant à un utilisateur de:
- Créer des routines*
- Modifier des routines
- Exécuter des routines
*Les procédures stockées, les fonctions et les “triggers” sont inclus dans la définition d’une routine.
Il est généralement déconseillé de permettre aux utilisateurs de créer ou modifier des routines. Puisqu’elles sont exécutées sur le serveur, il est possible de faire planter tout le système si un utilisateur crée accidentellement une boucle infinie. Ces privilèges devraient être réservés à un DBA ou à un utilisateur expérimenté en qui vous avez confiance.
De plus, l’exécution d’une procédure doit obéir aux privilèges du definer ou de l’invoker. Lors de sa création, il est possible d’indiquer si l’exécution de chaque statement doit respecter les privilèges du créateur ou de l’exécuteur. Le défaut est definer, ce qui peut être potentiellement dangereux si la procédure est créée en étant root. À l’inverse, ça peut être très pratique pour permettre de lire ou écrire de manière restreinte dans une table dans laquelle un utilisateur n’a pas forcement accès.
Les procédures possèdent un tas d’avantages: rapidité, sécurité et consistance. On peut même les utiliser pour réduire des erreurs liées aux transactions (locktimeout, deadlock, etc..) et prévenir les injections SQL. Elles possèdent cependant des désavantages: les requêtes exécutées ne peuvent être cachées. Puisqu’elle sont préalablement parsées et stockées sur le serveur, il n’est pas possible de faire appel au mécanisme de caching lors de leur exécution. C’est un aspect à considérer lors d’optimisation d’un processus.
En conclusion, lorsqu’elles sont correctement utilisées, les procédures stockées peuvent ajouter beaucoup de rapidité à un processus. Plusieurs programmeurs qui n’ont jamais utilisé d’autre SGBD que MySQL ne sont peut-être pas familliers avec les procédures. Je recommande à tous de prendre un instant pour y jeter un coup d’oeil !