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
) REFERENCESmage_core_store
(store_id
) ON DELETE CASCADE ON UPDATE CASCADE;
Query: ALTER TABLEmage_catalog_category_entity_datetime
ADD CONSTRAINTFK_CATALOG_CATEGORY_ENTITY_DATETIME_ATTRIBUTE
FOREIGN KEY (attribute_id
) REFERENCESmage_eav_attribute
(attribute_id
) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINTFK_CATALOG_CATEGORY_ENTITY_DATETIME_ENTITY
FOREIGN KEY (entity_id
) REFERENCESmage_catalog_category_entity
(entity_id
) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINTFK_CATALOG_CATEGORY_ENTITY_DATETIME_STORE
FOREIGN KEY (store_id
) REFERENCESmage_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
, CONSTRAINTFK_CATALOG_CATEGORY_ENTITY_DATETIME_STORE
FOREIGN KEY (store_id
) REFERENCESmage_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
) REFERENCESmage_core_website
(website_id
) ON DELETE CASCADE ON UPDATE CASCADE;
Query: — —
ALTER TABLEmage_catalogrule_product
ADD CONSTRAINTFK_catalogrule_product_customergroup
FOREIGN KEY (customer_group_id
) REFERENCESmage_customer_group
(customer_group_id
) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINTFK_CATALOGRULE_PRODUCT_PRODUCT
FOREIGN KEY (product_id
) REFERENCESmage_catalog_product_entity
(entity_id
) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINTFK_catalogrule_product_rule
FOREIGN KEY (rule_id
) REFERENCESmage_catalogrule
(rule_id
) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINTFK_catalogrule_product_website
FOREIGN KEY (website_id
) REFERENCESmage_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
, CONSTRAINTFK_catalogrule_product_customergroup
FOREIGN KEY (customer_group_id
) REFERENCESmage_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.