Oct/090
SQL_MODE bonne ou mauvaise idée ?
MySQL est connu pour être très flexible avec la validation des données. Les conversions silencieuses ne sont pas des pratiques courantes parmi les autres SGBD. Au lieu de lancer des erreurs, MySQL lance des warnings, ce que la majorité des applications ne gèrent pas. (Est-ce que votre application fait un SHOW WARNINGS; à chaque requête?)
Néanmoins, la variable SQL_MODE permet de contrôler ce comportement. Plusieurs niveaux de validation peuvent donc être assignés, partant d’une validation quasi absente (par défaut) à une validation très stricte. Ce qui peut paraître comme une bonne affaire me parait plutôt comme une très mauvaise idée.
Le problème avec le SQL_MODE c’est que par défaut, la valeur est vide (oui oui!). Il n’y a pas de mode prédéfinie ce qui donne un comportement très souple. Plusieurs personnes ne savent pas que cette variable existe et construisent une application qui repose malheureusement sur cette souplesse. Lorsque votre application finie par en dépendre, c’est-à-dire qu’elle se comporte “normalement” avec cette absence de validation de données, vous risquez gros.
On peut insérer des dates impossibles comme le 31 février ou 0000-00-00 , des divisions par 0 dans des opérations mathématiques et des valeurs qui dépassent largement la limite possible des champs. Votre application fait peut-être tout ça, sans même que vous le sachiez! Elle ne produit aucune erreur puisque du côté de MySQL, il n’y a que des Warnings.
Imaginez le scénario: vous désirez loguer des transactions bancaires, mais un bug idiot fait que toutes les dates des transactions sont insérées avec un string quelconque, 1-janvier-2009 par exemple, ce que MySQL converti en 0000-00-00. Le champ pour stocker l’IP de la personne qui fait la transaction est un SMALLINT, tous vos IP atteignent la valeur maximum et sont convertis en 32767.
Un bon conseil: si vous débutez un nouveau projet, assurez-vous de mettre un SQL_MODE strict pour vous éviter des problèmes tôt ou tard ! Essayez-le sur une application existante pour voir comme elle réagit!
Le SQL_MODE peut être modifié par session ou globalement pour l’ensemble des usagés. Parmi les plus stricts, on retrouve
strict_trans_table: Si une valeur n’a pas pu être insérée dans une table transactionnelle sans modification, la commande est annulée. Pour une table non-transactionnelle, la commande est annulée si cela survient dans une ligne unique ou dans la première ligne d’une insertion multiple
traditional: MySQL se comporte comme un système SQL “traditionnel”. Une description simple est que ce mode “émet une erreur et non pas une alerte” lors de l’insertion d’une valeur incorrecte dans une colonne. Note : si vous utilisez un moteur de table non-transactionnel, les commandes INSERT/UPDATE s’arrêteront dès que l’erreur est repérée.
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 !
Jun/080
Différence entre MySQL 4 et MySQL 5
J’ai fais beaucoup de tests pour m’assurer qu’une base de donnée en production sur MySQL 4 allait avoir le même comportement sur MySQL 5. Voici donc ma conclusion et les recommandations en conséquence.
La première différence que j’ai trouvé concerne les strings qu’on insère dans un champ char/varchar. Si on insère une string qui possède des espaces au début ou à la fin, MySQL4 les trim automatiquement. Ce n’est pas le cas de MySQL5. Il faut donc être spécialement attentif à ce qu’on insère et trimer tout les valeurs avant (c’est ce qu’il faudrait toujours faire de toute façon). Supposons qu’il se glisse accidentellement 2 espaces dans un champs, et qu’il y a 2 000 000 d’enregistrements dans la table. Il y aura donc 3.81 Mo de données et 3.81 Mo d’index (si le champs est indexé) pour stocker des espaces inutiles. Ça peut sembler trivial mais 3.81 Mo d’index, c’est énorme.
Une autre différence concerne l’arrondissement des valeurs décimales, et là il faut être extrêmement vigilant car les données ne seront plus pareil! Il y a 2 comportements différents dans l’arrondissement des décimales, mais la source du problème est la même: on tente d’insérer des valeurs plus grande que permet les champs dans la table.
Le meilleur moyen de vous montrer les différences est un exemple: supposons que nous avons une table avec un champ DECIMAL(10,5).
-
INSERT INTO numeric_test VALUES (35.555555);
-
INSERT INTO numeric_test VALUES (35.555556);
-
/*J'insère les même valeurs, mais entre quote.
-
MySQL permet (malheureusement) d'insérer des valeurs numériques "quotés" */
-
INSERT INTO numeric_test VALUES ('35.555554');
-
INSERT INTO numeric_test VALUES ('35.555555');
-
INSERT INTO numeric_test VALUES ('35.555556');
|
MySQL 4:
|
MySQL 5
|
Comme on peut le voir, MySQL 4 arrondis inférieurement lorsque la décimal excédentaire est un 5. MySQL 5 lui arrondis supérieurement. MySQL4 truncate la décimal excédentaire lorsqu’on insère les valeur entre quote alors que MySQL5 arrondis comme s’il y en avait pas.
Pour palier ce problème, il faut toujours s’assurer qu’on insère des valeurs qui respectent le format maximal des champs des tables SQL. Comme ça si le comportement change encore dans d’autre version de MySQL, nous n’aurons pas ce problème.
Il m’arrive parfois de constater du code comme celui-ci dans certain script:
-
<?php
-
$sql = “INSERT INTO table VALUES (”.($quantity*price).”)”;
-
?>
C’est vraiment pas cool, car $quantity * $price peut donner un résultat comme 3.45678578248. Le “business logic” dépend entièrement de MySQL et vous n’aurez aucun contrôle dessus.
Il semble aussi que dans MySQL 5, les requêtes qui font des SELECT FROM plusieurs tables suivi de JOIN sont très mal supportées comparativement à MySQL 4. En fait, je devrais plutôt dire qu’ils sont maintenant supportés de manière plus logique. Ce genre de jointure est logiquement un peu tordu et crée des erreurs SQL en MySQL 5.
Je vous montre la requête qui m’a mis la puce à l’oreille.
-
SELECT COUNT(DISTINCT client.noclient) AS cnt
-
FROM client,
-
produit,
-
produitgroupelien
-
LEFT JOIN clientstatus
-
ON (
-
clientstatus.nogroupe = client.nogroupe AND
-
clientstatus.marqueur IN (1, 3)
-
)
-
LEFT JOIN produitstatus
-
ON (
-
produitstatus.nogroupe = produit.nogroupe AND
-
produitstatus.marqueur IN (1, 3)
-
)
-
WHERE produitgroupelien.groupe = produit.groupe` AND
-
clientstatus.status >= 300 AND
-
clientstatus.status <= 200 AND
-
produitstatus.status = 500;
-
—–
-
Erreur no: 1054
-
Unknown COLUMN 'client.nogroupe' IN 'on clause'
L’erreur est trompeuse, car client.nogroupe existe bel et bien. Pour que la requête s’exécute sans erreur, l’ordre dans lequel sont déclaré les tables dans le FROM et les LEFT JOIN devient extrêmement important. Cependant, je ne vous encourage pas a trouver l’ordre “logique” mais tout simplement d’utiliser des jointures pour tous les tables. L’ensemble de la requête sera beaucoup plus simple a comprendre et les champs de liaison facile à repérer.
Apr/080
MySQL Query Cache: l’importance de la constance
Chaque fois qu’une requête SELECT est envoyée au serveur, MySQL conserve la requête et le résultat dans une cache (MySQL Query Cache). Ainsi, si la même requête est redemandée, le serveur peut retourner le résultat sans physiquement exécuter la requête. Cette cache joue un rôle très important dans la performance de la base de données.
Cependant, la cache possède quelques petites lacunes. Pour que le résultat d’une requête soit retourné par la cache, la syntaxe des requêtes doit être TRÈS EXACTEMENT PAREIL (bytes pour bytes). Ceci inclus le upper et lower case.
Donc, ces 3 requêtes seront considérées différentes:
-
SELECT * FROM client WHERE prenom = 'Pat';
-
SELECT * FROM client WHERE prenom = "Pat";
-
SELECT * FROM client WHERE prenom = 'Pat';
Adopter un standard syntaxique devient un avantage qui joue un rôle dans l’optimisation de la base de données. Cependant, demeurer constant lorsque les requêtes sont écrites dans un code PHP (par exemple) n’est pas nécessairement facile car la syntaxe PHP utilisé pour formuler la requête peut influencer la requête elle-même.
-
$sql = 'SELECT Name, District
-
FROM city
-
WHERE Population > 30000
-
AND CountryCode != \'AFG\'
-
ORDER BY Name
-
LIMIT 10';
Il est déconseillé d’utiliser ce genre de syntaxe PHP car plusieurs espaces (ou TAB) s’insèrent à chaque lignes. Si la même requête doit être utilisée dans un autre script qui possède une indentation différente, il ne sera probablement pas possible d’utiliser celle en cache.
Utilisez plutôt une syntaxe PHP qui permet de conserver qu’un seul espace entre chaque instruction:
-
$sql = 'SELECT Name, District'.
-
' FROM city'.
-
' WHERE Population > 30000'.
-
' AND CountryCode != \'AFG\''.
-
' ORDER BY Name'.
-
' LIMIT 10';
Les requêtes créés avec la deuxième forme conservent toujours qu’un seul espace entre chaque instruction et demeure lisible peu importe l’indentation du code. Si toutes les requêtes sont créées avec cette notation, les chances qu’elles se trouvent déjà dans la Query Cache sont meilleures.