Geek Answers Handbook

Mysql load from dump: ERROR 1452 (23000) on line 13: cannot add or update child row: foreign key constraint is not satisfied

I dumped the 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 I used the specific options above was because I needed:

  • Dump data ONLY
  • Do not create any DDL statements such as CREATE TABLE, etc.

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

I want to import data flushed using the command above into the CLEAN database.

I am trying to import discarded data (e.g. restore data) into the CLEAN database by entering 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)

13 :

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

sf_guard_group_permission:

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 - FK sf_guard_permission. , mysql (, ), .

, , INSERT () PRECEDE , .

, -, , INSERT mysqldump, , , R.I.

ORDER, mysqldump INSERT, .. , FK , , ?

INSERT, 10k , .

-, , mysqldump - ?

+5
1

, , :

SET FOREIGN_KEY_CHECKS = 0;

, ​​ , MySQL .

mysqldump, , --compact.

:

1) --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) , , :

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

More articles:


All Articles