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

Recientemente me hice cargo de un antiguo proyecto que se creó hace 10 años. Utiliza MySQL 5.1.

Entre otras cosas, necesito cambiar el juego de caracteres predeterminado 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

Configuré mi propia Mac para trabajar en esto. Sin pensar demasiado en ello, ejecuté "brew install mysql" que instaló MySQL 5.7. Entonces tengo algunos conflictos de versiones.

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  

Recibo este error:

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

Pensé que podría solucionar 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?

Feb 23 '16 05:02 lorm

Cambiar el valor predeterminado para una columna con una ALTER TABLEdeclaración, por ejemplo

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

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

En cuanto a por qué encuentra el error, es probable que la sql_modeconfiguración de 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 realizó la "importación", las declaraciones SQL que hicieron el INSERT en esa tabla se ejecutaron en una sesión que permitía cero fechas.

Para ver la configuración de sql_mode:

SHOW VARIABLES LIKE 'sql_mode' ;

-o-

SELECT @@sql_mode ;

En cuanto a cómo "solucionar" el problema actual, para que no se produzca el error cuando ejecute la ALTER TABLEdeclaración.

Varias opciones:

1) cambie sql_modepara permitir cero fechas, eliminando NO_ZERO_DATEy NO_ZERO_IN_DATE. El cambio se puede aplicar en el archivo my.cnf, por lo que después de reiniciar MySQL Server, sql_modela variable se inicializará con la configuración en my.cnf.

Para un cambio temporal, podemos modificar la configuración con una única sesión, sin necesidad de 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 ;

2) cambie la createdcolumna para permitir valores NULL y actualice las filas existentes para cambiar las fechas cero a valores nulos

3) actualice las filas existentes para cambiar las fechas cero a una fecha válida

No necesitamos ejecutar declaraciones individuales para actualizar cada fila. Podemos actualizar todas las filas de una sola vez (suponiendo que sea una tabla de tamaño razonable. Para una tabla más grande, para evitar una enorme generación de retroceso/deshacer, podemos realizar la operación en fragmentos de tamaño razonable).

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

Si ya hemos cambiado la createdcolumna para permitir NULLvalores, podemos hacer algo como esto:

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

O podemos establecerlos en una fecha válida, por ejemplo, 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 de fecha y hora de la medianoche del 1 de enero de 1970 ( '1970-01-01 00:00:00') es una "fecha cero". Se evaluará como'0000-00-00 00:00:00'

Feb 22 '2016 23:02 spencer7593