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!
Oct/083
MySQL: Le Book
Un gars sur IRC me demandait l’autre jour quel était “Le book” de MySQL. Il n’est évidemment pas possible de devenir expert en la matière avec un seul livre. Après quelques échanges, j’ai vite compris qu’il cherchait à améliorer l’utilisation qu’il en fait en tant que développeur d’application Web.
Je n’ai pas eu l’occasion (comprendre le temps) de lire des milliers de livres. Par contre dans ceux que je connais, je lui ai suggéré deux qui répondront bien à ce qu’il veut: MySQL 5.0 Certification Study Guide et MySQL Stored Procedure Programming.
Le Certification Study Guide se divise en deux parties distinctes: la première est pour les développeurs alors que la deuxième s’adresse aux Administrateurs. Dans la première partie, on y voit tous les concepts essentiels pour les développeurs d’application: syntaxe, sous requête, jointure, datatype et index, routines, etc… Il fait un tour complet sur tous les sujets nécessaires pour passer l’examen de certification. C’est super, mais ça parle très peu d’optimisation et on ne fait que survoler certains sujets; le livre tente plutôt de donner tous les éléments nécessaires pour comprendre la matière et faire les bons choix pour arriver à un résultat optimisé. C’est pourquoi je lui ai également suggéré MySQL Stored Procedure Programming.
Ce deuxième livre focus principalement sur les procédures stockées, les fonctions et les triggers. Ces trois aspects jouent, à mon avis, un rôle important dans la performance d’une application et l’intégrité des données. Il est difficile de ne pas parler de syntaxe, de jointures, de datatypes et d’autres concepts clés lorsqu’on explique ce qu’est une procédure stockée. L’auteur ne néglige aucun détail. Il arrive brillamment à expliquer comment et pourquoi 2 petites requêtes peuvent être mieux qu’une seule grosse avec des jointures complexes dans certains cas, et pourquoi une seule peut meilleur que 2 petites dans d’autres. Il y a dans chaque chapitre un souci de la performance qui n’existe pas dans le Certification Study Guide.
Ces deux oeuvres forment à mon avis une base solide pour comprendre et utiliser intelligemment MySQL en tant que développeur. Je recommande de les lire dans l’ordre dans lequel je les décris. On fait le tour de tous les features importants et on réussit à aller chercher les connaissances nécessaires pour en faire une utilisation solide et efficace avec MySQL Certification Study Guide et on ajoute à ça des notions de performance et d’optimisation avec MySQL Stored Procedure Programming.
Bonne lecture!
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 !