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)

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.

  • 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. – PeterPayn Aug 8 '14 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.

  • Thanks for your comment though unfortunately I don't have SSH access. – PeterPayn Aug 8 '14 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. – SPRBRN Aug 8 '14 at 14:08
  • 1
    I checked my phpinfo.php and it says shell_exec is a disabled function. If that answers your question. – PeterPayn Aug 8 '14 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. – Daniel Flippance Oct 2 '15 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;
  • 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? – PeterPayn Aug 8 '14 at 14:06
  • @PetePayn, Run the query after successful import of your db dump – mageUz Aug 8 '14 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. – PeterPayn Aug 9 '14 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. – mageUz Aug 11 '14 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.