2

I'm trying to transfer Magento 1.8 CE to another server. Created a new database on the new server and trying to run SQL commands. I get the following error 

  -- Constraints for table `catalog_category_entity_datetime`
-- 
ALTER TABLE  `catalog_category_entity_datetime` ADD CONSTRAINT  `FK_CATALOG_CATEGORY_ENTITY_DATETIME_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (  `store_id` ) REFERENCES  `core_store` (  `store_id` ) ON DELETE CASCADE ON UPDATE CASCADE ,
ADD CONSTRAINT  `FK_CAT_CTGR_ENTT_DTIME_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (  `attribute_id` ) REFERENCES  `eav_attribute` (  `attribute_id` ) ON DELETE CASCADE ON UPDATE CASCADE ,
ADD CONSTRAINT  `FK_CAT_CTGR_ENTT_DTIME_ENTT_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (  `entity_id` ) REFERENCES  `catalog_category_entity` (  `entity_id` ) ON DELETE CASCADE ON UPDATE CASCADE ;


MySQL said: 

#1452 - Cannot add or update a child row: a foreign key constraint fails (`dhvision_dev`.`#sql-1838_18f5628`, CONSTRAINT `FK_CATALOG_CATEGORY_ENTITY_DATETIME_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCAD)

I'm not quite sure what's the problem is. Can someone provide any suggestions? Detailed explanation in steps is appreciated. Thanks

Yuriy Buha
  • 111
  • 1
  • 3
  • 4
  • possible duplicate of [Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails](http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa) – Mihai Dec 30 '13 at 18:22
  • While exporting in mySQL check the checkbox "Disable foreign key checks" It adds SET FOREIGN_KEY_CHECKS=0; at the beginning and SET FOREIGN_KEY_CHECKS=1; at the end of the sql file. If you still get the error, insert SET FOREIGN_KEY_CHECKS=0; right before the line that produces error. In my case it was the line "ALTER TABLE `catalog_category_entity_datetime` ADD CONSTRAINT..." More info about the issue is here http://www.magentocommerce.com/boards/viewthread/32194/ Thanks to all – Yuriy Buha Jan 06 '14 at 14:50

1 Answers1

2

If your new database is absolutely empty of tables and you're getting this on import, you have duplicate keyed rows in one of the tables you're trying to import which fails the foreign key check.

It is reccommended and would not hurt to run Magento's database repair tool on the database to correct this issue.

Download Magento Database Repair Tool Version 1.1 here:

http://www.magentocommerce.com/download

Their instructions on running it:

http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/db-repair-tool

The oft quoted magentocommerce.com forum recommendation by non-db admin types is to shut the foreign key checks off, hope you haven't accumulated bad karma, import the database and keep an eye out for future weirdness when it turns out there really was a duplicate foreign key.

In this case, it sounds like you've already imported the database and are finding after the fact that you have duplicated foreign keys which means the Magento database needs an integrity check.

Fiasco Labs
  • 6,457
  • 3
  • 32
  • 43
  • There's no option to clone db on 1and1 server. But I used your suggestion @Fiasco regarding foreign hey check off. I found more info here http://www.magentocommerce.com/boards/viewthread/32194/. – Yuriy Buha Jan 06 '14 at 14:34