Magento – MySQL error while importing database

databaseimportupgrade

I want to upgrade Magento. Therefor I first want to transfer the live production system to a development workspace. I am trying to import the production database into a clean database. But I can't get it imported correctly. I have tried several options: exporting via Magento admin, exporting via phpMyAdmin with different settings including (and excluding) the statements as shown at http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/restoring_a_backup_of_a_magento_database

I am using Magento 1.5.1.0, phpMyAdmin 4.0.8 and the latest version of BigDump to import.

This is the error I receive after a while:

Error at the line 8699: ADD CONSTRAINT FK_CATALOG_CATEGORY_ENTITY_DATETIME_STORE FOREIGN KEY (store_id) REFERENCES mage_core_store (store_id) ON DELETE CASCADE ON UPDATE CASCADE;
Query: ALTER TABLE mage_catalog_category_entity_datetime
ADD CONSTRAINT FK_CATALOG_CATEGORY_ENTITY_DATETIME_ATTRIBUTE FOREIGN KEY (attribute_id) REFERENCES mage_eav_attribute (attribute_id) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT FK_CATALOG_CATEGORY_ENTITY_DATETIME_ENTITY FOREIGN KEY (entity_id) REFERENCES mage_catalog_category_entity (entity_id) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT FK_CATALOG_CATEGORY_ENTITY_DATETIME_STORE FOREIGN KEY (store_id) REFERENCES mage_core_store (store_id) ON DELETE CASCADE ON UPDATE CASCADE
MySQL: Cannot add or update a child row: a foreign key constraint fails (goldenm101_mage4.#sql-182e_c1d79, CONSTRAINT FK_CATALOG_CATEGORY_ENTITY_DATETIME_STORE FOREIGN KEY (store_id) REFERENCES mage_core_store (store_id) ON DELETE CASCADE ON UPDATE CASC)'

Additional information: I don't have SSH access.

Error after the possible solution of mageUz:

Error at the line 1487637: ADD CONSTRAINT FK_catalogrule_product_website FOREIGN KEY (website_id) REFERENCES mage_core_website (website_id) ON DELETE CASCADE ON UPDATE CASCADE;
Query: — —
ALTER TABLE mage_catalogrule_product
ADD CONSTRAINT FK_catalogrule_product_customergroup FOREIGN KEY (customer_group_id) REFERENCES mage_customer_group (customer_group_id) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT FK_CATALOGRULE_PRODUCT_PRODUCT FOREIGN KEY (product_id) REFERENCES mage_catalog_product_entity (entity_id) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT FK_catalogrule_product_rule FOREIGN KEY (rule_id) REFERENCES mage_catalogrule (rule_id) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT FK_catalogrule_product_website FOREIGN KEY (website_id) REFERENCES mage_core_website (website_id) ON DELETE CASCADE ON UPDATE CASCADE
MySQL: Cannot add or update a child row: a foreign key constraint fails (goldenm101_mage4.#sql-182e_308afa, CONSTRAINT FK_catalogrule_product_customergroup FOREIGN KEY (customer_group_id) REFERENCES mage_customer_group (customer_group_id) ON DELETE CASC)

Best Answer

It is because your database has a foreign key constraint. Follow below steps before importing

1) Open the exported sql file of your database and add following sql query at the begining

SET FOREIGN_KEY_CHECKS = 0;

2) Goto the end of the file and add

SET FOREIGN_KEY_CHECKS = 1;

This should work. It will disable foreign key check while importing and then will reenable it.