mysql load from dump: ERROR 1452 (23000) at line 13: Cannot add or update a child row: a foreign key constraint fails

Question

I dumped a mysql database using mysqldump with the following command:

mysqldump --no-create-db --no-create-info --compact --single-transaction --complete-insert --extended-insert=FALSE --flush-logs -uroot -ppassword databasename > databasename.db.data.dump.sql

The reason why I used the particular options above was that I needed to:

1. Dump ONLY the data
2. Not create any DDL statements like CREATE TABLE etc

I have a script that generates a CLEAN database (i.e. a database with the data structures - tables, indices etc, but NO data).

I want to import the data dumped by using the command above, into the CLEAN database.

I attempt to import the dumped data (i.e. restore the data) into the CLEAN database by typing the following command:

mysql -h hostname -u user --password=password databasename < filename

When I run this command, I get the following error:

ERROR 1452 (23000) at line 13: Cannot add or update a child row: a foreign key constraint fails (`tpwsdb`.`sf_guard_group_permission`, CONSTRAINT `sf_guard_group_permission_FK_2` FOREIGN KEY (`permission_id`) REFERENCES `sf_guard_permission` (`id`) ON DELETE CASCADE)

Line 13 in the dumped data file is:

INSERT INTO `sf_guard_group_permission` (`group_id`, `permission_id`) VALUES (1,1);

the schema for sf_guard_group_permission is:

mysql> describe sf_guard_group_permission;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| group_id      | int(11) | NO   | PRI | NULL    |       |
| permission_id | int(11) | NO   | PRI | NULL    |       |
+---------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Permission_id is an FK into a table sf_guard_permission. From the error, it seems that mysql is attempting to populate a table with dependencies (a child table so to speak), BEFORE populating the parent table.

Upon checking the dumped file contents, I can see that indeed, the INSERT statements for the dependent (child) table PRECEDE those for the parent table - which obviously violates the Referential Integrity constraints on the table.

This error then seems to be caused by the order in which the INSERT statements are created by mysqldump, which surprisingly, seems to pay no heed to R.I.

Is there anyway that I can force the ORDER in which mysqldump creates INSERT statements - i.e. so that tables that are FKs in other tables, appear before the tables that reference them?

I would have manually changed the order the INSERT statements appear, but the file contains almost 10k lines, and I don't relish the prospect of doing that.

Someone must have come across this issue before whilst using mysqldump - what is the way to solve this issue?

Answer 1

Any time you are importing a dump file into an empty data that has foreign keys, you should set the following variable:

SET FOREIGN_KEY_CHECKS = 0;

That way if a child row is inserted before the parent row, MySQL will not throw an error.

That variable is set by default in mysqldump, but you disabeld it by using the --compact flag.

You have two options to fix this:

1) run the dump again without --compact:

mysqldump --no-create-db --no-create-info --single-transaction --complete-insert --extended-insert=FALSE --flush-logs -uroot -ppassword databasename > databasename.db.data.dump.sql

2) Import the dump you have, but disable the foreign key checks first:

mysql -h hostname -u user --password=password databasename
SET FOREIGN_KEY_CHECKS = 0;
\. databasename.db.data.dump.sql