invalid - Error en mysql al establecer el valor predeterminado para DATE o DATETIME

mysql error invalid default value for

Question

Estoy ejecutando MySql Server 5.7.11 y esta oración:

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

No funciona Dando el error:

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

Pero lo siguiente:

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

solo funciona

El mismo caso para DATE.

Como nota al margen , se menciona en los documentos de mysql :

El tipo DATE se usa para valores con una parte de fecha pero sin parte de tiempo. MySQL recupera y muestra los valores de FECHA en formato ''AAAA-MM-DD''. El rango admitido es ''1000-01-01'' a ''9999-12-31''.

incluso si también dicen:

Los valores DATE, DATETIME o TIMESTAMP no válidos se convierten al valor "cero" del tipo apropiado (''0000-00-00'' o ''0000-00-00 00:00:00'').

Teniendo también en cuenta la segunda cita de la documentación de mysql, ¿alguien podría decirme por qué está dando ese error?

Answer 1

Problema de sintaxis de configuración

En algunas versiones de MYSQL (probado 5.7. *) En sistemas * nix, debe usar esta sintaxis:

[mysqld]

sql-mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_ENGINE_SUBSTITUTION"

Estos no funcionarán:

no hay comillas

sql-mode=NO_ENGINE_SUBSTITUTION

subrayar sin comillas

sql_mode=NO_ENGINE_SUBSTITUTION

subrayado y citas

sql_mode="NO_ENGINE_SUBSTITUTION"

Una revisión más completa de los valores de configuración y el modo sql:

Cómo configurar banderas permanentes de Modo SQL

Answer 2

Combinaciones de opciones para mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) .

No tira:

STRICT_TRANS_TABLES + NO_ZERO_DATE

Lanza

STRICT_TRANS_TABLES + NO_ZERO_IN_DATE

Mi configuración en /etc/mysql/my.cnf en Ubuntu:

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

Answer 3

El error se debe al modo sql, que puede ser un modo estricto según la última documentación de MYSQL 5.7

La documentación de MySQL 5.7 dice :

El modo estricto afecta si el servidor permite ''0000-00-00'' como una fecha válida: si el modo estricto no está habilitado, se permite ''0000-00-00'' y las inserciones no producen ninguna advertencia. Si el modo estricto está habilitado, ''0000-00-00'' no está permitido y las inserciones producen un error, a menos que también se proporcione IGNORE. Para INSERT IGNORE y UPDATE IGNORE, ''0000-00-00'' está permitido y las inserciones producen una advertencia.

Para verificar el modo MYSQL

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

Deshabilitar el modo STRICT_TRANS_TABLES

Sin embargo, para permitir el formato 0000-00-00 00:00:00 , debe deshabilitar el modo STRICT_TRANS_TABLES en el archivo de configuración mysql o por comando

Por orden

SET sql_mode = '''';

o

SET GLOBAL sql_mode = '''';

El uso de la palabra clave GLOBAL requiere superprevios y afecta las operaciones que todos los clientes se conectan a partir de ese momento

si lo anterior no funciona, vaya a /etc/mysql/my.cnf (según ubuntu) y comente STRICT_TRANS_TABLES

Además, si desea establecer permanentemente el modo sql al iniciar el servidor, incluya SET sql_mode='''' en my.cnf en Linux o MacOS. Para Windows, esto debe hacerse en el archivo my.ini .

Nota

Sin embargo, el modo estricto no está habilitado de manera predeterminada en MYSQL 5.6. Por lo tanto, no produce el error según la documentación MYSQL 6 que dice

MySQL le permite almacenar un valor "cero" de "0000-00-00" como una "fecha ficticia". En algunos casos, esto es más conveniente que usar valores NULL, y utiliza menos datos y espacio de índice. Para no permitir ''0000-00-00'', habilite el modo SQL NO_ZERO_DATE.

ACTUALIZAR

Con respecto al error, como dijo @ Dylan-Su:

No creo que este sea el error, la forma en que MYSQL evolucionó con el tiempo debido a que algunas cosas se cambian en función de una mejora adicional del producto.

Sin embargo, tengo otro informe de error relacionado con la función NOW()

El campo de fecha y hora no acepta el AHORA predeterminado ()

Otra nota útil [consulte Inicialización y actualización automáticas para TIMESTAMP y DATETIME ]

A partir de MySQL 5.6.5, las columnas TIMESTAMP y DATETIME se pueden inicializar y actualizar automáticamente a la fecha y hora actuales (es decir, la marca de tiempo actual). Antes de 5.6.5, esto es cierto solo para TIMESTAMP y, como máximo, una columna TIMESTAMP por tabla. Las siguientes notas describen primero la inicialización automática y la actualización para MySQL 5.6.5 y posteriores, luego las diferencias para las versiones anteriores a 5.6.5.

Actualización sobre NO_ZERO_DATE

A partir de MySQL a partir de 5.7.4, este modo está en desuso. Para la versión anterior, debe comentar la línea respectiva en el archivo de configuración. Consulte la documentación de MySQL 5.7 el NO_ZERO_DATE

Answer 4

Esta respuesta es solo para MySQL 5.7:

Lo mejor no está realmente en blanco en sql_mode, sino que usa en PHP una variable de sesión con:

SET SESSION sql_mode= ''ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION''

Así que al menos mantienes los otros valores predeterminados.

Es una locura que la documentación de mysql no esté clara, debe eliminar estos valores de defeault en sql_mode:

NO_ZERO_IN_DATE, NO_ZERO_DATE, entiendo, pero en las versiones futuras esto se descontinuará.

STRICT_ALL_TABLES, con esto, antes de que se ignoren los parámetros, por lo que también debe eliminarlo.

Finalmente, también es TRADICIONAL, pero la documentación habla sobre este parámetro: "dar un error en lugar de una advertencia" al insertar un valor incorrecto en una columna ", con este parámetro, las fechas con valores cero no se insertan, pero sin sí.

MySQL no está realmente organizado con estos parámetros y combinaciones.

Answer 5

Funciona para 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)

Puede crear un SQLFiddle para recrear su problema.

http://sqlfiddle.com/

Si funciona para MySQL 5.6 y 5.7.8, pero falla en 5.7.11. Entonces probablemente sea un error de regresión para 5.7.11.

Answer 6

Me metí en una situación en la que los datos se mezclaron entre NULL y 0000-00-00 para un campo de fecha. Pero no sabía cómo actualizar ''0000-00-00'' a NULL, porque

 update my_table set my_date_field=NULL where my_date_field=''0000-00-00''

Ya no está permitido. Mi solución fue bastante simple:

update my_table set my_date_field=NULL where my_date_field<''1000-01-01''

porque todos los valores incorrectos de my_date_field (ya sean fechas correctas o no) eran anteriores a esta fecha.

Answer 7

Para resolver el problema con MySQL Workbench (después de aplicar la solución en el lado del servidor):

Elimine SQL_MODE a TRADITIONAL en el panel de preferencias.

Answer 8

Primero seleccione la sesión actual sql_mode :

SELECT @@SESSION.sql_mode;

Entonces obtendrá algo como ese valor predeterminado :

''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''

y luego establezca sql_mode sin ''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 tiene subvenciones, puede hacerlo también para GLOBAL :

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

Answer 9

Simplemente agregue la línea: sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

archivo interno: /etc/mysql/mysql.conf.d/mysqld.cnf

luego sudo service mysql restart

Answer 10

Tuve este error con WAMP 3.0.6 con MySql 5.7.14.

Solución :

cambie la línea 70 (si su archivo ini está intacto) en el archivo 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"

a

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

y reinicie todos los servicios.

Esto deshabilitará el modo estricto. Según la documentación, "modo estricto" significa un modo con uno o ambos STRICT_TRANS_TABLES o STRICT_ALL_TABLES habilitados. La documentation dice:

"El modo SQL predeterminado en MySQL 5.7 incluye estos modos: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER y NO_ENGINE_SUBSTITSTITSTITSTITSTITSTITSTITSU.

Answer 11

set global sql_mode = ''STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'';