Error on copying database to new server

[garethjones]

Hi,

iam in the process of copying my site to a new host and they are doing the migration but have hit a problem,basically they get this error message :

ERROR 1452 (23000) at line 57480: Cannot add or update a child row: a foreign key constraint fails (`mywebsite_mg1`.`#sql-166e_7e26`, CONSTRAINT `FK_OZSS_CAT_EAV_ATTR_ATTR_ID_OZSS_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `mg_eav_attribute` (`attribute_id`) ON DELETE CASCADE O)

and they have stopped work as they don't currently support foreign key constraint errors, i asked my current host to fix and they wont deal with it, its a new install of magento with 1 plugin (fishpig wordpress) and a template

i don't know how to fix this any advice would be greatly appreciated i.e who to contact - fish pig or template creators or how to fix the issue

[Stuart M]

You need to perform both the database export and import making sure you have foreign keys disabled.

The below copy and paste commands should fix it for you on both ends.

set FOREIGN_KEY_CHECKS = 0;
-- perform the export OR import --
set FOREIGN_KEY_CHECKS = 1;

[mambli]

If you are using phpmyadmin there is an option in export section, where you can disable checking of foreign key. 

[Pronto]

Actually I never turn off foreign keys and I've never had issues with it. What I do is I use the good old mysqldump utility to make dump:

mysqldump -h hostname -u username -ppassword database > mydumpfile.sql

and once I copied the generated file to the new server I do:

mysql -h hostname -u username -ppassword database < mydumpfile.sql

Works like charm.