Mysql restore con error 1217 y error 1292

No hay nada más odioso que tener que hacer una recuperación de un fichero .SQL, y encontrarte con problemas de compatibilidad y errores que en parte están derivados de una mala praxis a la hora de programar. En nuestro caso, nos encontramos con una BD con valores incorrectos para campos tipo datatime (error 1292) y problemas con las claves foráneas, (error 1217) también propiciadas pro los cambios en las últimas versiones de MySQL y MariaDb.

Cada vez son más las cosas que alejan la compatibilidad entre MariaDB y MySQL, de tal manera, que la elección de una de ellas, y el conocimiento del funcionamiento de ambos modelos se hace más necesario cuando realizamos un desarrollo basado en MySQL/MariaDB.

ERROR 1217 (23000) at line XX: Cannot delete or update a parent row: a foreign key constraint fails

Se trataría de desactivar la comprobación de las claves foráneas.

mysql> SHOW VARIABLES LIKE 'FOREIGN_KEY_CHECKS' ;
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0,00 sec)
mysql> SET FOREIGN_KEY_CHECKS=0;

Cuando terminemos nuestra restauración del backup deberíamos volver al valor original

mysql> SET FOREIGN_KEY_CHECKS=1;

ERROR 1292 (22007) at line XXX: Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘create_at’ at row XX

En este caso, se trata de desactivar el problema derivado de que en nuestro backup hay filas que tienen un valor no valido para una columna de tipo datetime, y MySQL no pasa al estar activado la verificación de este tipo de columnas.

mysql> SHOW VARIABLES LIKE 'sql_mode' ;
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0,01 sec)
mysql> SET GLOBAL sql_mode = ‘';

Solución modificando el fichero de backup mysql .sql

Para evitar errores, olvidos, y a una vez conocemos la naturaleza del problema y podemos incluso investigar más profundamente sobre el tema, os propongo modificar el fichero dump para añadir las instruccione adecuadas

/* Añadir al principio */
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
-- 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 ;

Y añadimos al final (la mejor manera para mi es añadiendo con echo en el shell

# dump.sql es el nombre de vuestro fichero de backup mysqldump o phpmyadmin
echo 'SET @@sql_mode := @old_sql_mode; ' >> dump.sql
echo ’SET @@FOREIGN_KEY_CHECKS := @OLD_FOREIGN_KEY_CHECKS; ‘>> dump.sql

Es altamente aconsejable no limitarse a restaurar el mysql, sino dedicar un tiempo a corregir los defectos estructurales de la base de datos afectada, ya que a la larga nos volverá a pasar factura.