Problem
MySQL complains when you try to restore data from some SQL backups. This happens to me often when restoring database for Magento.
Analysis
The problem is with constraint checks. The database you try to restore probably makes use of foreign key constraints to ensure database integrity. Unfortunately SQL queries during database restore often violate some foreign keys, because restore happens without regard to the logical structure and integrity of the database.
Solution
You simply need to turn OFF constraint checks at the beginning of your SQL query. After restore is completed you need to turn back ON constraint checks.
To restore a .sql file backup without constraint checking, simply add the following statements at the beginning of your .sql file:
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION; SET NAMES utf8; SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;
At the end of the file, add the statements required to turn on constraint checking again:
SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT; SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS; SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION; SET SQL_NOTES=@OLD_SQL_NOTES;