MySQL : ERROR 1452 (23000) at line 11426: Cannot add or update a child row: a foreign key constraint fails

So i am trying to restore a database from one server to another. Something like this:

mysql -u username -p -D dbname < backup.sql

Somehow there is this error:

[root@ns46 mysql]# mysql -u haocheap -p -D haocheap < /tmp/ayam.sql Enter password: ERROR 1452 (23000) at line 11426: Cannot add or update a child row: a foreign key constraint fails (`haocheap/#sql-bb2_5ef8a`, CONSTRAINT `FK_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE) So where do i get the backup.sql file? i dump it from the old server's phpmyadmin export. using method "INSERT'.


Open the file, add this line at top:

SET AUTOCOMMIT=0;
SET FOREIGN_KEY_CHECKS=0;

and these lines at bottom:

SET FOREIGN_KEY_CHECKS=1;
COMMIT;
SET AUTOCOMMIT=1;

The theory is, the restore is restoring a database that need some foreign key constraints from another table which has NOT YET been restored. thus the "SET FOREIGN_KEY_CHECKS=0;" option.

Hope this help someone.

//alak

Comments

Popular posts from this blog

SAP Backdoors {placeholder Post]

Fiori Apps Keep Calling Internal Hostname / Internal FQDN