Magento – how to fix database inconsistency for magento 1.7.0.2

backupdatabasemagento-1.7MySQL

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?

Best Answer

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`
   )
Related Topic