MySQL error while importing database

Question

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)

Answer 1

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.

Answer 2

Do you have ssh access? If so try the following commands (where you should replace magentodb with the name of your database):

mysqldump -u root -p magentodb > magentodb.sql
mysqldump -u root -p magentodb | gzip -v > magentodb.sql.gz

The second command zips the database immediately, which might be convenient when you have to download it. If you don't have mysql root access, use another account.

Unzip the database on the test machine and run the following command:

mysql -u root -p test_magentodb < magentodb.sql

This is my preferred method. Our database is 2GB in size, and the other methods simply fail for such a large database.

Answer 3

It seems mage_core_store table was imported little different, I have also faced such problem several times when I imported/migrated magento db. first rows of some tables (core_store, core_website, core_store_group, customer_group) will imported with different primary keys rather than original.

Solutions:

Use SET FOREIGN_KEY_CHECKS = 0; and import dump.

Run the query:

SET FOREIGN_KEY_CHECKS=0;
UPDATE `core_store` SET store_id = 0 WHERE code='admin';
UPDATE `core_store_group` SET group_id = 0 WHERE name='Default';
UPDATE `core_website` SET website_id = 0 WHERE code='admin';
UPDATE `customer_group` SET customer_group_id = 0 WHERE customer_group_code='NOT LOGGED IN';
SET FOREIGN_KEY_CHECKS=1;

Answer 4

I also faced the same issue for importing the database in server. And tried all answers above. This tutorial fixed my issue and made me able to import my DB in phpmyadmin. Tutorial

Hope this one helped!