1

I've run into the reason why I am unable to import a backup of a magento store into my development environment and its quite an obvious one:

There are multiple instances of foreign key constraints being violated by the tables i'm importing.

I've spent some time cataloging which tables are affected, then re-exported the db without that table and imported again to dev. But after 4 different tables having the problem (and not always the same ones each time), I've decided it's probably time to fix the issue, not find workarounds.

So the question is, how do i fix the database up?

I've found the official repair tool on the Magento site, but it stresses that it should be run on a clone of the database less it causes issues. Or that you backup the database before running it. there's the problem. I can't reliably import that backup, so I can't create a clone, or take a backup and restore it in the event of an incident.

I'm stuck with which way to progress and after 2 weeks of evenings on this, I'm too close to it to see an obvious way to go. Can anyone offer me a direction?

  • Did you import only the data or also the table structures? You should be able to export and re-import the tables when you also create the tables from the source database. – Vladimir Kerkhoff Aug 29 '13 at 14:56
  • full export and import, including drop tables et al. I've even added set Foreign Keys = 0 but it still does the check, which i didn't think was right. – bytejunkie Aug 29 '13 at 19:58
  • I see nobody has asked you how you are doing the backup and restore - is it via phpmyadmin? If so you should backup via the Magento admin and import the database in your dev site via the command line. If you need to know how to do that I will add it as an answer. – McNab Sep 1 '13 at 7:31
  • im actually using a variety of methods. mostly mysqldump to get the backup and then phpmyadmin to import it. if i backup with the magento admin, it times the site out and we can't be taking the customers site down just for a backup. – bytejunkie Sep 1 '13 at 8:00
  • most recent method, using magend00-shell for the backup and phpmyadmin for the import results in the following error #1062 - Duplicate entry 'product/4/10-1-1' for key 'UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID' so a quick google around this subject leads me to this site albertomariarossi.it/… which does a really good job of explaining whats wrong and what the issue is. step 1 sku's - not a problem step 2 duplicate url keys - problem lies here, 232 dupes. can i drop the table and regenerate it? – bytejunkie Sep 1 '13 at 9:11
1

The thing you want is to fix the corrupt data that is in your current installation. The reason that you are getting Foreign key constraint errors is because the referenced item isn't available. Let's give an example:

You have a product with ID 5. The product is saved in catalog_product_entity. Now you assign the product to a category, the information for that is saved in catalog_category_product. In that table there is a column product_id which has a foreign key to the catalog_product_entity table

Relations for table: catalog_category_product

So when a line is added to this table the product MUST exist in the catalog_product_entity table. The advantage is that you protect the database from corruption, is does give you some nasty errors now and then, but at least it wont break your database... in theory.

In your case the database was set up incorrectly, exported with the wrong permissions sometime in the past or something like that, causing the constraint to be probably absent in your case. This causes problems because now you have all sorts of useless rows which just sit there.

Old Advise

The errors you are now getting are probably on the old rows. What I suggest you do is download a database management program which allows you to continue importing when an error occurs ignore the error an continue.

I have not tested not tested this method my self in this specific case, so thorough checking of the imported data is required. If the site is broken after the import then there are probably missing tables in the old database. In that case disable the foreign key check as described in the other comments and run the repair tool you mentioned yourself earlier.

Ps. Since it is about your database and all your data, it may not be unwise to have a magento developer who knows Magento have a look.

New Advise

I'll advise against my own case of importing the data, just had a similar problem today, I ran the following query multiple times (20 times in my case), this removes duplicates from the eav tables and solves a few problems.

DELETE `duplicated`.*
FROM  `catalog_product_entity_datetime` AS `orig` 
INNER JOIN `catalog_product_entity_datetime` AS `duplicated`
ON (`orig`.`entity_id`    = `duplicated`.`entity_id`
    AND `orig`.`store_id`     = `duplicated`.`store_id`
    AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
    AND `orig`.`value_id`     > `duplicated`.`value_id`);

DELETE `duplicated`.*
FROM  `catalog_product_entity_decimal` AS `orig` 
INNER JOIN `catalog_product_entity_decimal` AS `duplicated`
ON (`orig`.`entity_id`    = `duplicated`.`entity_id`
    AND `orig`.`store_id`     = `duplicated`.`store_id`
    AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
    AND `orig`.`value_id`     > `duplicated`.`value_id`);

DELETE `duplicated`.*
FROM  `catalog_product_entity_int` AS `orig` 
INNER JOIN `catalog_product_entity_int` AS `duplicated`
ON (`orig`.`entity_id`    = `duplicated`.`entity_id`
    AND `orig`.`store_id`     = `duplicated`.`store_id`
    AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
    AND `orig`.`value_id`     > `duplicated`.`value_id`);

DELETE `duplicated`.*
FROM  `catalog_product_entity_text` AS `orig` 
INNER JOIN `catalog_product_entity_text` AS `duplicated`
ON (`orig`.`entity_id`    = `duplicated`.`entity_id`
    AND `orig`.`store_id`     = `duplicated`.`store_id`
    AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
    AND `orig`.`value_id`     > `duplicated`.`value_id`);

DELETE `duplicated`.*
FROM  `catalog_product_entity_varchar` AS `orig` 
INNER JOIN `catalog_product_entity_varchar` AS `duplicated`
ON (`orig`.`entity_id`    = `duplicated`.`entity_id`
    AND `orig`.`store_id`     = `duplicated`.`store_id`
    AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
    AND `orig`.`value_id`     > `duplicated`.`value_id`);

DELETE `duplicated`.*
FROM  `catalog_category_entity_datetime` AS `orig` 
INNER JOIN `catalog_category_entity_datetime` AS `duplicated`
ON (`orig`.`entity_id`    = `duplicated`.`entity_id`
    AND `orig`.`store_id`     = `duplicated`.`store_id`
    AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
    AND `orig`.`value_id`     > `duplicated`.`value_id`);

DELETE `duplicated`.*
FROM  `catalog_category_entity_decimal` AS `orig` 
INNER JOIN `catalog_category_entity_decimal` AS `duplicated`
ON (`orig`.`entity_id`    = `duplicated`.`entity_id`
    AND `orig`.`store_id`     = `duplicated`.`store_id`
    AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
    AND `orig`.`value_id`     > `duplicated`.`value_id`);

DELETE `duplicated`.*
FROM  `catalog_category_entity_int` AS `orig` 
INNER JOIN `catalog_category_entity_int` AS `duplicated`
ON (`orig`.`entity_id`    = `duplicated`.`entity_id`
    AND `orig`.`store_id`     = `duplicated`.`store_id`
    AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
    AND `orig`.`value_id`     > `duplicated`.`value_id`);

DELETE `duplicated`.*
FROM  `catalog_category_entity_text` AS `orig` 
INNER JOIN `catalog_category_entity_text` AS `duplicated`
ON (`orig`.`entity_id`    = `duplicated`.`entity_id`
    AND `orig`.`store_id`     = `duplicated`.`store_id`
    AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
    AND `orig`.`value_id`     > `duplicated`.`value_id`);

DELETE `duplicated`.*
FROM  `catalog_category_entity_varchar` AS `orig` 
INNER JOIN `catalog_category_entity_varchar` AS `duplicated`
ON (`orig`.`entity_id`    = `duplicated`.`entity_id`
    AND `orig`.`store_id`     = `duplicated`.`store_id`
    AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
    AND `orig`.`value_id`     > `duplicated`.`value_id`);

DELETE `duplicated`.* FROM `core_url_rewrite` AS `orig` 
INNER JOIN `core_url_rewrite` AS `duplicated` 
    ON (
         `orig`.`id_path` = `duplicated`.`id_path` 
         AND `orig`.`is_system` = `duplicated`.`is_system` 
         AND `orig`.`store_id` = `duplicated`.`store_id`
         AND `orig`.`url_rewrite_id` > `duplicated`.`url_rewrite_id`
   )


DELETE `duplicated`.* FROM `core_url_rewrite` AS `orig` 
INNER JOIN `core_url_rewrite` AS `duplicated` 
    ON (
         `orig`.`request_path` = `duplicated`.`request_path` 
         AND `orig`.`store_id` = `duplicated`.`store_id`
         AND `orig`.`url_rewrite_id` > `duplicated`.`url_rewrite_id`
   )
  • do you know of a database management program which allows continued importing when an error occurs? – bytejunkie Sep 1 '13 at 7:59
  • On the Mac I use Sequel Pro, on Windows I'm not exactly sure which program to use, maybe this gives you an idea: stackoverflow.com/questions/6817551/… – Paul Hachmang Sep 1 '13 at 13:54
  • im on a mac, so i'll look that app up. thanks. – bytejunkie Sep 1 '13 at 19:50
  • It looks like you have deleted some store ID or store. The error you mentioned above will happen if store ID does not exist in 'core_store' table. – Tejas Shah Sep 2 '13 at 4:44
  • @PaulHachmang thanks for the tip on Sequel Pro, finally managed to get the data imported. I've got a new issue but im opening a question about that. still hope to come back to this and award an answer when i've got the dev site up and running. – bytejunkie Sep 9 '13 at 19:19
1

so i found out in the end that the table with the duplicates, is actually indexed and there is a way of rectifying this in the admin.

so the table was core_url_rewrite and the admin section of

System >> Index Management >> Catalog URL Rewrites has the option to Reindex Data

This was the fix for me

0

If you're pulling the live site data to the dev site and you're restoring the complete database dump to your development database, the easiest way to totally avoid foreign key checks for me has been very, very simple.

Simply drop the database, recreate it and import the dump into a totally empty database.

Most of your problems are because all the tables exist and are usually being removed one by one and then restored, since everything exists, you end up with a smashingly beautiful mess of foreign key check failures.

Empty database = no foreign keys to clash.

NOTE: This works if your live database is not corrupted... If it is, you have other issues to deal with that are larger than a failed dev import.

-1

If you have take a full back up of database then to restore a .sql file backup without constraint checking, simply add the following statements at the beginning of your .sql file:

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

At the end of the file, add the statements required to turn on constraint checking again:

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;

With these modifications, you should be able to restore your database from a .sql file backup created with any tool.

  • thats pretty much what I need, but it hasn't worked. #1452 - Cannot add or update a child row: a foreign key constraint fails (rccarshop.catalogsearch_query, CONSTRAINT FK_CATALOGSEARCH_QUERY_STORE_ID_CORE_STORE_STORE_ID FOREIGN KEY (store_id) REFERENCES core_store (store_id) ON DELETE CASCADE ON UPDATE CA) any ideas whats going on? – bytejunkie Aug 30 '13 at 18:35
  • 2
    Although this is the general advise, it is bad advise to suggest that you remove the foreign key checks. The database checks are there to maintain the integrity of the database, the solution is to solve the errors in the database. – Paul Hachmang Oct 11 '13 at 12:41
-1

I am not sure whether this is the right way or not,but what i do is, I disable the foreign key check in the beginning of the file and then again enable it at the end.

It works for me and haven't faced a problem till now. But I am not sure about the potential risk. You can try it.

What you need to do is,Open your sql file and add this line at the beginning

SET FOREIGN_KEY_CHECKS = 0;

And at the end for file insert

SET FOREIGN_KEY_CHECKS = 1;
  • i still dont get to run a successful import with that, it fails on a foreign key constraint. – bytejunkie Aug 31 '13 at 8:00
  • what error is it giving exactly ?? can you please describe in detail and also tell what exactly you have done ?? – Dexter Aug 31 '13 at 8:17
  • error is as shown on previous comment – bytejunkie Sep 1 '13 at 7:57

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.