0

I've been creating categories programmatically for a few years with no issues. Since the volume of categories has increased to a point where there is a significant performance degradation building menus, I need to enable flat categories, but am now getting an error when this option is turned on. I've truncated and reindexed the catalog_category_flat_store_1 table just to be safe, but I'm getting a foreign key failure off of catalog_category_entity.

Here's the error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`DBNAME`.`mage_catalog_category_flat_store_1`, CONSTRAINT `FK_MAGE_CAT_CTGR_FLAT_STORE_1_ENTT_ID_MAGE_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `mage_catalog_category), query was: INSERT INTO `mage_catalog_category_flat_store_1` (`store_id`, `display_mode`, `include_in_menu`, `is_active`, `is_anchor`, `landing_page`, `name`) VALUES (?, ?, ?, ?, ?, ?, ?)

And the code that is generating the error:

$parentCategory = Mage::getModel('catalog/category')->load($parent);  //load the parent category
$newCategory = Mage::getModel('catalog/category');
$newCategory->setStoreId( $parentCategory->getStoreID() );
$newCategory->setName(utf8_encode($name));
$newCategory->setIsActive(1);
$newCategory->setIsAnchor(0);
$newCategory->setDisplayMode('PAGE');
$newCategory->setPath($parentCategory->getPath() );
$newCategory->setLandingPage($GLOBALS['subcategoryBlock']); 
$newCategory->setData( "include_in_menu",0 );
try {
    $newCategory->save();
} catch (Exception $e) {
    $e->getMessage());
}

Any suggestions on how to resolve the issue? The exact same code works fine if flat categories is turned off.

  • Have you altered the log output? Assuming your table prefix is mage_ there is no table catalog_category. – Melvyn Jun 7 '15 at 13:58
4

You are probably doing this in the frontend or at least not in the admin.

The flat categories are not to be written to, other then the indexer, for exactly the reason you are now seeing. The flat tables are a different representation of the category EAV model, optimized for speed. But the authoritative table, still is catalog_category_entity.

So you should be saving there. This is done by either selecting the admin store in Mage initialization (Mage::run() or Mage::app()) or on a case-by-case basis by passing a flag to the new model:

$newCategory = Mage::getModel('catalog/category', array('disable_flat' => true));
  • I still don't understand why creating a category the way the OP was doing it results in an insert into the flat table instead of catalog_category_entity, but your suggestion of using the admin store fixed it for me. I did Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID); Without that it tries to insert into the flat table without an entity_id. I would think it would create the category and then insert into the flat table once it has the new primary key. Even setting indexes to manual didn't help. – regularmike Jul 27 '16 at 19:38
  • @regularmike Selecting the admin store is a shortcut: the flat tables are only enabled in the frontend. This effectively causes the category resource model to ignore the "Use flat tables" configuration setting. – Melvyn Sep 7 '16 at 10:00

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.