MySQL 8.0 sql_mode

La dernière version du serveur de base de données MySQL est la version 8.0. Par défaut, le mode sql sql_mode est très restrictif. Il empêche par exemple l’utilisation de la valeur 0000-00-00 dans les formats DATE. Seulement, avant la version 5.7, on ne pouvait définir de valeur par défaut à NOW(), et nombre d’anciennes tables se retrouvent avec de multiples champs de dates égaux à cette valeur lorsqu’ils n’ont pas été définis.

Logo Oracle MySQL, texte mysql_mode et NO_ZERO_DATE, en fond une vue aérienne de la côte du Cap d'Agde

La meilleure chose aurait été de définir les champs DATE à NULL si non définis, mais voilà c’est comme cela. Le mode mysql dit strict inclut maintenant une valeur qui empêche de d’utliser un enregistrement s’il contient une telle valeur dans ses champs.

Afficher le mode par défaut

Ceci provient du mode sql_mode par défaut ou défini dans un fichier de configuration mysql *.cnf.

On peut vérifier le mode actuel en se connectant en root dans le srveur mysql et en lançant une requête sur cette variable :

  • Le mode NO_ZERO_DATE ne permet pas l’utilisation de 0000-00-00 comme date valide.
  • Le mode NO_ZERO_IN_DATE n’autorise pas les dates dans lesquelles la partie année ou mois ou jour est égale à 0. Par exemple, les dates telles que 0000-08-02, 2020-00-01 ou 2020-01-00 ne sont pas autorisées, mais il n’affecte pas 0000-00-00. Pour contrôler si le serveur autorise 0000-00-00, il faut utiliser le mode NO_ZERO_DATE.

Donc le mode que l’on désire enlever est donc NO_ZERO_DATE.

Fichiers de configuration .cnf

Les fichiers de configuration mysql peuvent affecter cette valeur. On vérifie donc si la chaîne sql_mode est présente. Ces fichiers se trouve dans le dossier /etc/mysql et les sous dossiers, inclus dans le fichier mysql.cnf avec les lignes :

On recherche donc la chaîne sql_mode ou sql-mode (on peut écrire l’un ou l’autre) en allant dans le dossier /etc/mysql :

Il ne trouve rien. Si vous trouvez quelque chose, vous modifiez la valeur à ce que vous voulez. Je prends la valeur par défaut et j’enlève NO_ZERO_DATE : ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Si la propriété sql_mode n’existe pas, il faut la créer dans un bloc [mysqld]. Attention, cela affectera les réglages du serveur, et donc de toutes les bases, mais restera en cas de redémarrage du serveur. La commande grep permet de localiser le bloc.

Le bloc [mysqld] se trouve dans le fichier /etc/mysql/mysql.conf.d/mysqld.cnf.

Réglages personnalisés

On crée un fichier my-settings.cnf que l’on inclut dans /etc/mysql/mysql.cnf.

On écrit les lignes suivantes dans my-settings.cnf. Je mets en commentaire qu’il n’y a pas de valeur par défaut, mais la valeur obtenue avec SELECT @@GLOBAL.sql_mode :

Il n’y a pas de retour à la ligne après my_sql = ou #, c’est juste l’affichage. Je mets des commentaires pour savoir ce que j’ai fait et quand.

Et l’on inclut dans /etc/mysql/mysql.cnf en ajoutant la dernière ligne :

Une fois les changements faits, on redémarre le serveur mysql :

Affichage du mode personnalisé

On vérifie alors la variable sql_mode dans le serveur mysql :

Et hop, la valeur a changé, on va pouvoir utiliser nos anciennes tables avec des champs DATE affectés à la valeur 0000-00-00.

Soumettre un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables.