Erreur dans mysql lors de la définition de la valeur par défaut pour DATE ou DATETIME

J’exécute MySql Server 5.7.11 et cette phrase:

updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00' 

ne fonctionne pas . Donner l’erreur:

 ERROR 1067 (42000): Invalid default value for 'updated' 

Mais ce qui suit:

 updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00' 

ça marche

Le même cas pour DATE.

En sidenote , il est mentionné dans les documents mysql :

Le type DATE est utilisé pour les valeurs avec une partie date mais aucune partie temporelle. MySQL récupère et affiche les valeurs DATE au format AAAA-MM-JJ. La plage prise en charge est «1000-01-01» à «9999-12-31».

même s’ils disent aussi:

Les valeurs DATE, DATETIME ou TIMESTAMP non valides sont converties à la valeur «zéro» du type approprié («0000-00-00» ou «0000-00-00 00:00:00»).

Compte tenu de la deuxième citation de la documentation mysql, est-ce que quelqu’un peut me dire pourquoi il donne cette erreur?

L’erreur est due au mode SQL qui peut être en mode ssortingct selon la dernière documentation de MYSQL 5.7

Documentation MySQL 5.7 dit :

Le mode ssortingct détermine si le serveur autorise «0000-00-00» comme date valide: Si le mode ssortingct n’est pas activé, «0000-00-00» est autorisé et les insertions ne produisent aucun avertissement. Si le mode ssortingct est activé, «0000-00-00» n’est pas autorisé et les insertions génèrent une erreur, sauf si IGNORE est également indiqué. Pour INSERT IGNORE et UPDATE IGNORE, «0000-00-00» est autorisé et les insertions produisent un avertissement.

Pour vérifier le mode MYSQL

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

Désactivation du mode STRICT_TRANS_TABLES

Cependant, pour autoriser le format 0000-00-00 00:00:00 vous devez désactiver le mode STRICT_TRANS_TABLES dans le fichier de configuration mysql ou par la commande

Par commande

SET sql_mode = '';

ou

SET GLOBAL sql_mode = '';

L’utilisation du mot-clé GLOBAL nécessite super-préviliges et affecte les opérations que tous les clients connectent à partir de ce moment-là.

Si ci-dessus ne fonctionne pas, allez dans /etc/mysql/my.cnf (selon ubuntu) et STRICT_TRANS_TABLES en commentaire STRICT_TRANS_TABLES

De plus, si vous souhaitez définir définitivement le mode SQL au démarrage du serveur, incluez alors SET sql_mode='' dans my.cnf sous Linux ou MacOS. Pour Windows, cela doit être fait dans le fichier my.ini .

Remarque

Cependant, le mode ssortingct n’est pas activé par défaut dans MYSQL 5.6. Par conséquent, il ne produit pas l’erreur selon la documentation MYSQL 6 qui dit

MySQL vous permet de stocker une valeur «zéro» de «0000-00-00» comme «date fictive». Dans certains cas, cela est plus pratique que d’utiliser des valeurs NULL et utilise moins d’espace de données et d’index. Pour interdire ‘0000-00-00’, activez le mode SQL NO_ZERO_DATE.

METTRE À JOUR

En ce qui concerne le problème de bug comme dit par @ Dylan-Su:

Je ne pense pas que ce soit le bogue de la façon dont MYSQL est évolué au cours du temps en raison de laquelle certaines choses sont modifiées en fonction de l’amélioration du produit.

Cependant, j’ai un autre rapport de bogue sur la fonction NOW()

Le champ DateHeure n’accepte pas la valeur par défaut NOW ()

Une autre note utile [voir Initialisation automatique et mise à jour pour TIMESTAMP et DATETIME ]

Depuis MySQL 5.6.5, les colonnes TIMESTAMP et DATETIME peuvent être automatiquement initialisées et mises à jour avec la date et l’heure actuelles (c’est-à-dire l’horodatage actuel). Avant 5.6.5, cela n’est vrai que pour TIMESTAMP et pour au plus une colonne TIMESTAMP par table. Les notes suivantes décrivent d’abord l’initialisation et la mise à jour automatiques pour MySQL 5.6.5 et les versions ultérieures, puis les différences pour les versions précédant 5.6.5.

Mise à jour concernant NO_ZERO_DATE

A partir de MySQL à partir de 5.7.4, ce mode est obsolète. Pour la version précédente, vous devez commenter la ligne correspondante dans le fichier de configuration. Reportez-vous à la documentation MySQL 5.7 sur NO_ZERO_DATE

J’ai eu cette erreur avec WAMP 3.0.6 avec MySql 5.7.14.

Solution :

changez la ligne 70 (si votre fichier ini est intact) dans le fichier c:\wamp\bin\mysql\mysql5.7.14\my.ini de

 sql-mode= "STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER" 

à

 sql-mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER" 

et redémarrer tous les services.

Cela désactivera le mode ssortingct. Selon la documentation, «mode ssortingct» signifie un mode avec l’un ou l’autre ou les deux STRICT_TRANS_TABLES ou STRICT_ALL_TABLES activés. La documentation dit:

“Le mode SQL par défaut de MySQL 5.7 inclut les modes suivants: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER et NO_ENGINE_SUBSTITUTION.”

Problème de syntaxe de configuration

Sur certaines versions de MYSQL (testées 5.7. *) Sous les systèmes * nix, vous devez utiliser cette syntaxe:

 [mysqld] sql-mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_ENGINE_SUBSTITUTION" 

Cela ne marchera pas:

tiret pas de guillemets

 sql-mode=NO_ENGINE_SUBSTITUTION 

souligner aucune citation

 sql_mode=NO_ENGINE_SUBSTITUTION 

souligné et citations

 sql_mode="NO_ENGINE_SUBSTITUTION" 

Un examen plus complet des valeurs de configuration et du mode SQL:

Comment configurer des drapeaux permanents en mode Sql

Ajoutez simplement la ligne: sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

à l’intérieur du fichier: /etc/mysql/mysql.conf.d/mysqld.cnf

puis sudo service mysql restart

Cela fonctionne pour 5.7.8:

 mysql> create table t1(updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00'); Query OK, 0 rows affected (0.01 sec) mysql> show create table t1; +-------+-------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.8-rc | +-----------+ 1 row in set (0.00 sec) 

Vous pouvez créer un SQLFiddle pour recréer votre problème.

http://sqlfiddle.com/

Si cela fonctionne pour MySQL 5.6 et 5.7.8, mais échoue sur 5.7.11. Ce sera probablement un bogue de régression pour 5.7.11.

cette réponse est juste pour mysql 5.7:

le mieux n’est pas vraiment mis en blanc le sql_mode, au lieu d’utiliser dans php une variable de session avec:

 SET SESSION sql_mode= 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' 

donc au moins vous conservez les autres valeurs par défaut

C’est fou que la documentation de mysql ne soit pas claire, il faut supprimer ces valeurs de defeault dans sql_mode:

NO_ZERO_IN_DATE, NO_ZERO_DATE, je comprends, mais dans les futures versions cela sera arrêté.

STRICT_ALL_TABLES, avec ceci, avant que les parameters soient ignorés, vous devez donc le supprimer aussi.

enfin TRADITIONAL aussi, mais la documentation parle de ce paramètre: “donner une erreur au lieu d’un avertissement” lors de l’insertion d’une valeur incorrecte dans une colonne “, avec ce paramètre, les dates avec des valeurs nulles ne sont pas insérées, mais sans oui

mysql n’est pas vraiment organisé avec ces parameters et combinaisons.

Pour résoudre le problème avec MySQL Workbench (après application de la solution côté serveur):

Supprimez SQL_MODE dans TRADITIONAL dans le panneau des préférences.

entrer la description de l'image ici

Combinaisons d’options pour mysql Ver 14.14 Dissortingb 5.7.18, for Linux (x86_64) .

Ne jette pas:

STRICT_TRANS_TABLES + NO_ZERO_DATE

Jette:

STRICT_TRANS_TABLES + NO_ZERO_IN_DATE

Mes parameters dans /etc/mysql/my.cnf sur Ubuntu:

 [mysqld] sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 

Sélectionnez d’abord la session en cours sql_mode :

 SELECT @@SESSION.sql_mode; 

Ensuite, vous obtiendrez quelque chose comme cette valeur par défaut :

‘ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION’

puis définissez sql_mode sans 'NO_ZERO_DATE' :

 SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 

Si vous avez des subventions, vous pouvez le faire aussi pour GLOBAL :

 SELECT @@GLOBAL.sql_mode; SET GLOBAL sql_mode = '...';