Un bout de DBA MySQL, En long et en large

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.