7

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)

Improve this question
1

4 Answers 4

Reset to default
11

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.

Improve this answer
1
  • 1
    Thanks for your comment though if that was the solution it should have already been working because I already added those statements (as shown by the link in my post), as well as a database export via the Magento admin adds it automatically. Commented Aug 8, 2014 at 14:00
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.

Improve this answer
4
  • Thanks for your comment though unfortunately I don't have SSH access. Commented Aug 8, 2014 at 14:01
  • Can you run a php script that can run a shell command? You could test this with a small test database and if that works you can try it with the magento database. Commented Aug 8, 2014 at 14:08
  • 1
    I checked my phpinfo.php and it says shell_exec is a disabled function. If that answers your question. Commented Aug 8, 2014 at 14:23
  • This worked flawlessly the first time I tried it. Thank you! Using phpMyAdmin Export I wasn't able to copy the database after many attempts and several hours. Commented Oct 2, 2015 at 23:43
2

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;
Improve this answer
4
  • I am not sure if I understand what you are trying to say. Do you mean: Export, edit the sql file and (only) add the 'Foreign key check'-line, without turning it back on at the end of the file. And if it's imported run that query? Commented Aug 8, 2014 at 14:06
  • @PetePayn, Run the query after successful import of your db dump Commented Aug 8, 2014 at 18:10
  • So I added the single line to an export via phpMyAdmin (using Vim) and used bigdump to import it into a new database. It was at about 94% and then I received another error: I've added the error to the main question. Commented Aug 9, 2014 at 15:26
  • Try to import/export only database structure without data. If it could be imported the structure you can try to import data. Commented Aug 11, 2014 at 9:09
0

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!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.