Error: Incorrect date value: '0000-00-00' for column. Mysql Strict Mode

 

¿Como corregir Error: Incorrect date value: '0000-00-00' for column en MySQL con strict_mode activado?

Últimamente MySQL está dando quebraderos de cabeza. Yo me lo estoy encontrando a diario. La empresa de alojamiento ha actualizado por seguridad la versión de MySQL a la versión 8.0.26, activando por defecto el modo strict_mode, entre otros . Este último cambio es el que realmente está dando tantos problemas.

La configuración por defecto en MySQL 8 es: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION

Las alarmas empiezan a saltar cuando añadimos o intentamos modificar un dato de la tabla cuando nos han cambiado el servidor con el modo strict_mode activado. Os encontraréis con el siguiente error:

Error: Incorrect date value: '0000-00-00' for column nombre_columna

El modo strict_mode obliga a utilizar un modo de consultas estándar, por lo que ya no se permite realizar ciertas consultas, entre ellas, inicializar una fecha a '0000-00-00'.

Bueno, pues parecería sencillo, cambio el valor por defecto y ya está, no?, pues no:

• No se puede modificar el valor por defecto si tenemos datos erróneos en la tabla. Detecta que en la base de datos hay fechas inválidas, por lo que no dejará hacerlo
• Tampoco podemos cambiar los valores con una sentencia tan sencilla como
  UPDATE tabla SET campo_fecha = NULL WHERE campo_fecha = '0000-00-00'. Por qué? Pues porque nos vuelve a decir que no podemos utilizar este formato de fechas

Entonces... ¿como lo hago?

Debemos utilizar otra sentencia sql parecida a la anterior:

UPDATE tabla SET campo_fecha = NULL WHERE CAST(campo_fecha AS CHAR(10)) = '0000-00-00';

Entonces sí que nos dejará hacerlo. A partir de aquí, si tenéis más de un campo con fecha por defecto '0000-00-00' y que queréis cambiar a NULL, os recomiendo realizar una sola sentencia para esos campos, ya que si solo modificamos uno de ellos, fallará la consulta al encontrar fechas inválidas en otros campos.

Un ejemplo sería así, en una sola sentencia:

ALTER TABLE tabla CHANGE campo_fecha1 campo_fecha1 DATE NULL DEFAULT NULL, CHANGE campo_fecha2 campo_fecha2 DATE NULL DEFAULT NULL;

También nos podremos encontrar con que la nueva versión no admite ciertos campos y deberemos preparar esos campos para que puedan admitir NULL. Si un campo es obligatorio y no se lo enviamos a la consulta, el sistema nos indicará que el campo X no dispone de valor por defecto. O le asignamos un valor por defecto, o lo prepararmos para que pueda ser NULL.

Las consultas GROUP BY también pueden verse afectadas debido a la configuración sql_mode=only_full_group_by. Esta configuración obliga a las consultas a que todos los campos indicados en el GROUP BY, estén en el SELECT. Además, no permite incluir campos en el SELECT, en los HAVING o en los ORDER BY que no estén en la cláusula GROUP BY

Por cierto. Lo mismo pasa con MariaDB. Espero que sirva para alguien. A mi me costó varias horas encontrar la solución.