MySQL Valor de fecha incorrecto: '0000-00-00 00:00:00'

Recientemente me he hecho cargo de un viejo proyecto que fue creado hace 10 años. Utiliza MySQL 5.1.

Entre otras cosas, necesito cambiar el juego de caracteres por defecto de latin1 a utf8.

Como ejemplo, tengo tablas como esta:

  CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `first_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `last_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `username` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `email` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `pass` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `active` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'Y',
    `created` datetime NOT NULL,
    `last_login` datetime DEFAULT NULL,
    `author` varchar(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'N',
    `locked_at` datetime DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `ripple_token` varchar(36) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `ripple_token_expires` datetime DEFAULT '2014-10-31 08:03:55',
    `authentication_token` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `index_users_on_reset_password_token` (`reset_password_token`),
    UNIQUE KEY `index_users_on_confirmation_token` (`confirmation_token`),
    UNIQUE KEY `index_users_on_unlock_token` (`unlock_token`),
    KEY `users_active` (`active`),
    KEY `users_username` (`username`),
    KEY `index_users_on_email` (`email`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1677 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

Para trabajar en esto me he montado mi propio Mac. Sin pensarlo demasiado, ejecuté "brew install mysql" que instaló MySQL 5.7. Así que tengo algunos conflictos de versión.

Descargué una copia de esta base de datos y la importé.

Si intento ejecutar una consulta como esta

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL

Obtengo este error:

  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

Pensé que podría arreglar esto con:

  ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-01 00:00:00';
  Query OK, 0 rows affected (0.06 sec)
  Records: 0  Duplicates: 0  Warnings: 0

pero me sale:

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL ;
  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

¿Tengo que actualizar todos los valores?

Programación
mysql
22º febrero 2016 в 10:23
15 vistas

No fui capaz de hacerlo:

UPDATE users SET created = NULL WHERE created = '0000-00-00 00:00:00'

(en MySQL 5.7.13).

Seguía recibiendo el error Incorrect datetime value: '0000-00-00 00:00:00'.

Extrañamente, esto funcionaba: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. No tengo ni idea de por qué falla lo primero y funciona lo segundo... ¿quizás un error de MySQL?

En cualquier caso, esta consulta UPDATE funcionó:

UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00'
Comentarios (8)

Cambiar el valor por defecto de una columna con una sentencia ALTER TABLE, por ejemplo

 ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-02'

... no cambia ningún valor que ya esté almacenado. El valor "por defecto" se aplica a las filas que se insertan y para las que no se proporciona un valor para la columna.

En cuanto a por qué se encuentra con el error, es probable que la configuración de sql_mode para su sesión incluya NO_ZERO_DATE.

Referencia: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

Cuando hizo la "importación", las sentencias SQL que hicieron el INSERT en esa tabla se ejecutaron en una sesión que permitía fechas cero.

Para ver la configuración de sql_mode:

SHOW VARIABLES LIKE 'sql_mode' ;

-o-

SELECT @@sql_mode ;

En cuanto a cómo "arreglar" el problema actual, para que el error no sea lanzado cuando se ejecuta la sentencia ALTER TABLE.

Hay varias opciones:

  1. cambiar el sql_mode para permitir fechas cero, eliminando NO_ZERO_DATE y NO_ZERO_IN_DATE. El cambio puede ser aplicado en el archivo my.cnf, de modo que después de un reinicio del Servidor MySQL, la variable sql_mode será inicializada a la configuración en my.cnf.

Para un cambio temporal, podemos modificar la configuración con una sola sesión, sin requerir un cambio global.

-- save current setting of sql_mode
SET @old_sql_mode := @@sql_mode ;

-- derive a new value by removing NO_ZERO_DATE and NO_ZERO_IN_DATE
SET @new_sql_mode := @old_sql_mode ;
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_DATE,'  ,','));
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_IN_DATE,',','));
SET @@sql_mode := @new_sql_mode ;

-- perform the operation that errors due to "zero dates"

-- when we are done with required operations, we can revert back
-- to the original sql_mode setting, from the value we saved
SET @@sql_mode := @old_sql_mode ;

  1. cambiar la columna created para permitir valores NULL, y actualizar las filas existentes para cambiar las fechas cero por valores nulos

  2. actualizar las filas existentes para cambiar las fechas cero a una fecha válida

No necesitamos ejecutar sentencias individuales para actualizar cada fila. Podemos actualizar todas las filas de una sola vez (suponiendo que sea una tabla de tamaño razonable. En el caso de una tabla más grande, para evitar la generación de un rollback/undo enorme, podemos realizar la operación en trozos de tamaño razonable).

En la pregunta, el valor AUTO_INCREMENT mostrado para la definición de la tabla nos asegura que el número de filas no es excesivo.

Si ya hemos cambiado la columna created para permitir valores NULL, podemos hacer algo así:

UPDATE  `users` SET `created` = NULL WHERE `created` = '0000-00-00 00:00:00'

O bien, podemos establecer una fecha válida, por ejemplo, el 2 de enero de 1970

UPDATE  `users` SET `created` = '1970-01-02' WHERE `created` = '0000-00-00 00:00:00'

(Tenga en cuenta que un valor datetime de la medianoche del 1 de enero de 1970 ('1970-01-01 00:00') es una "fecha cero". Se evaluará como '0000-00-00 00:00'.

Yoan Tournade
Respuesta editada 13º julio 2017 в 2:01
Comentarios (5)
Solución

Mi sugerencia si es el caso de que la tabla está vacía o no es muy muy grande es exportar las sentencias de creación como un archivo .sql, reescribirlas como quieras. También haga lo mismo si tiene datos existentes, es decir, exporte las sentencias de inserción (recomiendo hacerlo en un archivo separado como las sentencias de creación). Por último, suelte la tabla y ejecute primero la sentencia create y luego la insert.

Puedes utilizar para ello el comando mysqldump, incluido en tu instalación de MySQL o también puedes instalar MySQL Workbench, que es una herramienta gráfica gratuita que incluye también esta opción de forma muy personalizable sin tener que buscar opciones de comando específicas.

Comentarios (3)