MySQL: résoudre l'erreur "Incorrect datetime value" lors d'opérations sur les tables

par Matt | dimanche 8 septembre 2019 | Web/Tech

Depuis le passage du site sur le nouveau serveur, ORION, j'utilise MySQL 8 en lieu et place de MariaDB, histoire de tester les les gains de performance.

Or, avec la nouvelle configuration de MySQL par défaut, vous pouvez obtenir cette erreur lors de simples opération de maintenance comme l'optimisation des tables:

example.wp_comments: Table does not support optimize, doing recreate + analyze instead
example.wp_comments: Invalid default value for 'comment_date'
example.wp_comments: Operation failed
example.wp_et_social_stats: Incorrect datetime value: '0000-00-00 00:00:00' for column 'comment_date' at row 1
example.wp_et_social_stats: Invalid default value for 'comment_date'
example.wp_et_social_stats: Table does not support optimize, doing recreate + analyze instead
example.wp_et_social_stats: Invalid default value for 'sharing_date'
example.wp_et_social_stats: Operation failed

Cela est dû à un changement de configuration, notamment dans la directive sql_mode depuis MySQL 5.7.

Voyons-donc ce que contient la directive par défaut. Identifiez-vous sur le serveur MySQL:

mysql -u root -p

Puis vérifiez le contenu de la variable de configuration sql_mode:

show variables like 'sql_mode';

Résultat:

+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| 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.02 sec)

Ce qui pose problème, ce sont ces deux directives: NO_ZERO_IN_DATE et NO_ZERO_DATE.

Deux solutions s'offrent à nous : éditer la valeur directement depuis la console mysql ou alors depuis le fichier de configuration my.cnf.

Méthode 1 : éditer la valeur de sql_mode depuis la console MySQL

Si vous vous trouvez toujours dans la console mysql, vous pouvez lancer la reqête suivante, qui enlève les drapeaux NO_ZERO_IN_DATE et NO_ZERO_DATE à notre directive sql_mode:

SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Relancez ensuite le serveur:

service mysql restart

Méthode 2 : éditer la valeur de sql_mode depuis le fichier de configuration MySQL (my.cnf)

Nous allons donc éditer notre fichier de configuration MySQL:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Et ajouter (ou modifier) la variable de configuration sql_mode en l'amputant des valeurs NO_ZERO_IN_DATE et NO_ZERO_DATE:

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Enregistrez le fichier puis redémarrez le serveur mysql:

service mysql restart

Vous pouvez désormais lancer des requêtes de maintenance ou de modification de la structure des tables de la base de données sans problèmes.