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.
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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
xavier@server:/etc/mysql$ sudo mysql -u root -pLeMotDePasseRoot [sudo] password for xavierbs: mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 80474 Server version: 8.0.21 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT @@GLOBAL.sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
- Le mode
NO_ZERO_DATEne permet pas l’utilisation de0000-00-00comme date valide. - Le mode
NO_ZERO_IN_DATEn’autorise pas les dates dans lesquelles la partie année ou mois ou jour est égale à 0. Par exemple, les dates telles que0000-08-02,2020-00-01ou2020-01-00ne sont pas autorisées, mais il n’affecte pas0000-00-00. Pour contrôler si le serveur autorise0000-00-00, il faut utiliser le modeNO_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 :
|
1 2 |
!includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ |
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 :
|
1 2 3 |
xavier@server:~$ cd /etc/mysql xavier@server:/etc/mysql$ sudo grep -rn "sql[-_]mode" . xavier@server:/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.
|
1 2 |
xavier@server:/etc/mysql$ sudo grep -rn "\[mysqld\]" . ./mysql.conf.d/mysqld.cnf:29:[mysqld] |
Le bloc [mysqld] se trouve dans le fichier /etc/mysql/mysql.conf.d/mysqld.cnf.
|
1 2 3 4 5 6 7 8 9 10 11 |
xavier@server:/etc/mysql$ cat mysql.conf.d/mysqld.cnf # ... # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log xavier@server:/etc/mysql$ |
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 :
|
1 2 3 4 5 |
[mysqld] # Change sql_mode. Default is nothing but SELECT @@GLOBAL.sql_mode has returned # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
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 :
|
25 26 27 28 29 30 31 32 33 34 35 36 |
# ... # The MySQL Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ !include /etc/mysql/my-settings.cnf |
Une fois les changements faits, on redémarre le serveur mysql :
|
1 |
xavier@server:/etc/mysql$ sudo systemctl restart mysql |
Affichage du mode personnalisé
On vérifie alors la variable sql_mode dans le serveur mysql :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
xavier@server:/etc/mysql$ sudo mysql -u root -pLeMotDePasseRoot [sudo] password for xavierbs: mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 80474 Server version: 8.0.21 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT @@GLOBAL.sql_mode; +-----------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +-----------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
Et hop, la valeur a changé, on va pouvoir utiliser nos anciennes tables avec des champs DATE affectés à la valeur 0000-00-00.
