SQL Query While uploading sql file

Question

I was uploading localhost site to live server. During the process I was importing .sql file from localhost to PHPMyAdmin live server database. After the uploading process was complete I got an error of:

Error message

SQL query:

ALTER TABLE `core_email_queue_recipients`
ADD CONSTRAINT `FK_6F4948F3ABF97DE12127EF14B140802A`
FOREIGN KEY (`message_id`) REFERENCES `core_email_queue` (`message_id`)
ON DELETE CASCADE ON UPDATE CASCADE

MySQL said:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`buddydea_magento`.`#sql-2aae_84a13f`, CONSTRAINT `FK_6F4948F3ABF97DE12127EF14B140802A` FOREIGN KEY (`message_id`) REFERENCES `core_email_queue` (`message_id`) ON DELETE CASCADE ON UPDATE CASC)

I have looked for the solution but didn't find the right answer.

Please help me to remove this error.

• Rama Chandran M: did you have ssh access? run commad mysqldump and import database

Answer 1

Excute below query before upload

SET FOREIGN_KEY_CHECKS=0;

Once uploaded revert back

SET FOREIGN_KEY_CHECKS=1;

• Aditya Gupta: how should i do this.
• Vino: go to sql tab and run the query
• Aditya Gupta: I just have to go sql tab and run this command ?
• Vino: yes. just goo to the sql tab and past the SET FOREIGN_KEY_CHECKS=0; then click 'go' button
• Vino: Did you get my point?

Answer 2

I have found that if I export from phpmysql, even with "disable foreign key checks" set both at export and import, my import has been failing. Phpmysql is exporting in an order that makes no sense, creating the keys too early, or not destroying them when dropping the tables, and not ignoring the checks totally.

BUT THERE IS A WAY - if I export the database from within Magento's Backup tool - that file imports in phpmyadmin without errors.

Wish I'd tried that earlier