Magento – Unable to reindex flat category

categoryflatindexing

Magento 1.8.x; Ultimo theme

Didn't find anything for this probably popular problem.

After reindex categories on localhost I have this:

2015-04-29T11:20:44+00:00 DEBUG (7): Exception message: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
Trace: #0 /var/www/local.dev/www/host/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/local.dev/www/host/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /var/www/local.dev/www/host/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /var/www/local.dev/www/host/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('CREATE TABLE `c...', Array)
#4 /var/www/local.dev/www/host/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('CREATE TABLE `c...', Array)
#5 /var/www/local.dev/www/host/lib/Varien/Db/Adapter/Pdo/Mysql.php(2117): Varien_Db_Adapter_Pdo_Mysql->query('CREATE TABLE `c...')
#6 /var/www/local.dev/www/host/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(638): Varien_Db_Adapter_Pdo_Mysql->createTable(Object(Varien_Db_Ddl_Table))
#7 /var/www/local.dev/www/host/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(1461): Mage_Catalog_Model_Resource_Category_Flat->_createTable('1')
#8 /var/www/local.dev/www/host/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php(1475): Mage_Catalog_Model_Resource_Category_Flat->_createTables()
#9 /var/www/local.dev/www/host/app/code/core/Mage/Catalog/Model/Category/Indexer/Flat.php(260): Mage_Catalog_Model_Resource_Category_Flat->reindexAll()
#10 /var/www/local.dev/www/host/app/code/core/Mage/Index/Model/Process.php(210): Mage_Catalog_Model_Category_Indexer_Flat->reindexAll()
#11 /var/www/local.dev/www/host/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#12 /var/www/local.dev/www/host/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(127): Mage_Index_Model_Process->reindexEverything()
#13 /var/www/local.dev/www/host/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#14 /var/www/local.dev/www/host/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#15 /var/www/local.dev/www/host/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#16 /var/www/local.dev/www/host/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#17 /var/www/local.dev/www/host/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#18 /var/www/local.dev/www/host/index.php(87): Mage::run('default', 'store')
#19 {main}

Here is SQL query:

CREATE TABLE `catalog_category_flat_store_1` (
  `entity_id` int UNSIGNED NOT NULL COMMENT 'entity_id' ,
  `parent_id` int UNSIGNED NOT NULL default '0' COMMENT 'parent_id' ,
  `created_at` timestamp NULL default NULL COMMENT 'created_at' ,
  `updated_at` timestamp NULL default NULL COMMENT 'updated_at' ,
  `path` varchar(255) NOT NULL default '' COMMENT 'path' ,
  `position` int NOT NULL default '0' COMMENT 'position' ,
  `level` int NOT NULL default '0' COMMENT 'level' ,
  `children_count` int NOT NULL default '0' COMMENT 'children_count' ,
  `store_id` smallint UNSIGNED NOT NULL default '0' COMMENT 'Store Id' ,
  `all_children` text NULL default NULL COMMENT 'All Children' ,
  `available_sort_by` text NULL default NULL COMMENT 'Available Product Listing Sort by' ,
  `children` text NULL default NULL COMMENT 'Children' ,
  `custom_apply_to_products` int NULL default NULL COMMENT 'Apply To Products' ,
  `custom_design` varchar(255) NULL default NULL COMMENT 'Custom Design' ,
  `custom_design_from` datetime NULL default NULL COMMENT 'Active From' ,
  `custom_design_to` datetime NULL default NULL COMMENT 'Active To' ,
  `custom_layout_update` text NULL default NULL COMMENT 'Custom Layout Update' ,
  `custom_use_parent_settings` int NULL default NULL COMMENT 'Use Parent Category Settings' ,
  `default_sort_by` varchar(255) NULL default NULL COMMENT 'Default Product Listing Sort by' ,
  `description` text NULL default NULL COMMENT 'Description' ,
  `display_mode` varchar(255) NULL default NULL COMMENT 'Display Mode' ,
  `filter_price_range` decimal(12,4) NULL default NULL COMMENT 'Layered Navigation Price Step' ,
  `image` varchar(255) NULL default NULL COMMENT 'Image' ,
  `include_in_menu` int NULL default NULL COMMENT 'Include in Navigation Menu' ,
  `is_active` int NULL default NULL COMMENT 'Is Active' ,
  `is_anchor` int NULL default NULL COMMENT 'Is Anchor' ,
  `landing_page` int NULL default NULL COMMENT 'CMS Block' ,
  `meta_description` text NULL default NULL COMMENT 'Meta Description' ,
  `meta_keywords` text NULL default NULL COMMENT 'Meta Keywords' ,
  `meta_title` varchar(255) NULL default NULL COMMENT 'Page Title' ,
  `name` varchar(255) NULL default NULL COMMENT 'Name' ,
  `page_layout` varchar(255) NULL default NULL COMMENT 'Page Layout' ,
  `path_in_store` text NULL default NULL COMMENT 'Path In Store' ,
  `thumbnail` varchar(255) NULL default NULL COMMENT 'Thumbnail Image' ,
  `umm_cat_block_bottom` text NULL default NULL COMMENT 'Block Bottom' ,
  `umm_cat_block_proportions` varchar(255) NULL default NULL COMMENT 'Proportions: Subcategories / Block Right' ,
  `umm_cat_block_right` text NULL default NULL COMMENT 'Block Right' ,
  `umm_cat_block_top` text NULL default NULL COMMENT 'Block Top' ,
  `umm_cat_label` varchar(255) NULL default NULL COMMENT 'Category Label' ,
  `url_key` varchar(255) NULL default NULL COMMENT 'URL Key' ,
  `url_path` varchar(255) NULL default NULL COMMENT 'Url Path' ,
  PRIMARY KEY  (`entity_id`),
  INDEX `IDX_CATALOG_CATEGORY_FLAT_STORE_1_STORE_ID` (`store_id`),
  INDEX `IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH` (`path`),
  INDEX `IDX_CATALOG_CATEGORY_FLAT_STORE_1_LEVEL` (`level`),
  CONSTRAINT `FK_CAT_CTGR_FLAT_STORE_1_ENTT_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CATALOG_CATEGORY_FLAT_STORE_1_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT='Catalog Category Flat (Store 1)' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

As you understand, its also impossible to create such table manualy

enter image description here

I'm unable to change value of Catalog Category Flat. Even if try hardcode.

upd.1

On live in system / configuration flat category is enabled

And on Live table is without foreign keys? Why magento decided to create them on localhost???

In other words

both catalog_category_entity (entity_id) and core_store (store_id) local magento indexer trying to create (on localhost). But on live server after reindex if I call SHOW CREATE TABLE catalog_category_flat_store_1 I don't see any foreign_keys

upd.2

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2015-04-29 16:05:28 7f7a3191b700 Error in foreign key constraint of table magento_host/catalog_category_flat_store_1:
FOREIGN KEY (`entity_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `FK_CATALOG_CATEGORY_FLAT_STORE_1_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
   ) COMMENT='Catalog Category Flat (Store 1)' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci:
Cannot resolve table name close to:
(`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `FK_CATALOG_CATEGORY_FLAT_STORE_1_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
   ) COMMENT='Catalog Category Flat (Store 1)' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

I tried to create table manually and replaced original SQL query with fields from tables

  `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
  ...
  `store_id` smallint(5) unsigned NOT NULL COMMENT 'Store Id',

instead of

  `entity_id` int UNSIGNED NOT NULL COMMENT 'entity_id' ,
  ...
  `store_id` smallint UNSIGNED NOT NULL default '0' COMMENT 'Store Id' ,

upd.3

I diff 2 files (file from custom magento and from default magento). Mage_Catalog_Model_Resource_Category_Flat has no any changes.

I tried to get

Mage::getConfig()->getNode()->global->models->catalog_resource

and didn't find rewrite node there.

I looked at default magento tables and here what I have found:

Custom core_store also have no FOREIGN KEYs while native magento have them. Also native magento catalog_category_flat_store_1 have

`entity_id` int(10)
`store_id` smallint(5)

Best Answer

From looking at the MySQL error code 1215, it looks like you have mismatching column definition in your flat table. When you establish a FK constraint, the columns must be identical.

Your FK FK_CATALOG_CATEGORY_FLAT_STORE_1_STORE_ID_CORE_STORE_STORE_ID uses core_store.store_id as the reference. I checked CE 1.9, and it's supposed to be of smallint(5) unsigned NOT NULL. However, I'm seeing your flat table is trying to create store_id with smallint UNSIGNED NOT NULL default, which gives you an error.

I'm not familiar with how the flat table gets created, but there's definitely something wrong with the column definitions. I suggest you check your core_store.store_id and make sure that is the same as what the flat table is trying to create.

Update:

If you're missing FKs on the flat table, it's not correct, as it's supposed to be there. You may have some customization that removed things incorrectly.

Check out Mage_Catalog_Model_Resource_Category_Flat::_getStaticColumns, and see if you see store_id get 'type' => array(Varien_Db_Ddl_Table::TYPE_SMALLINT, 5).

Related Solutions

Magento – Catalog Flat Category reindex issue

General info
In the DDL creation of tables Magento does not allow (for some reason still unknown to me) the creation of columns without a comment.
Specific info
The flat category tables are creating by adding one column for each column in the catalog_category_entity table and one column for each category attribute.
All columns from catalog_category_entity are retrieved by this method Mage_Catalog_Model_Resource_Category_Flat::_getStaticColumns but I doubt the problem is in there, because, when retrieving these columns, the comment for the column is the same as the column name. ('comment' => $column['COLUMN_NAME'])
So most probably the issue is with one of the attributes.
The attribute columns are generated in Mage_Catalog_Model_Resource_Category_Flat::_getEavColumns() and the comment for the column is the frontend_label of the attribute.
In conclusion most probably you have (at least) one category attribute that has a null value for the frontend label.
You can identify the attribute(s) by running this query on your database:

SELECT e.* 
FROM  
    eav_attribute e
LEFT JOIN  eav_entity_type et 
   ON e.entity_type_id = et.entity_type_id
WHERE 
   et.entity_type_code =  'catalog_category' AND 
   e.frontend_label IS NULL

If you find one, just add a frontend label (or make it an empty string) and it should work. The only constraint is that it shouldn't be NULL.

[EDIT]
You can debug this issue but adding this piece of code: echo "

"; print_r($columnEntry);exit; 
inside the method Varien_Db_Adapter_Pdo_Mysql::createTable() right before the exception is thrown.

if (empty($columnEntry['COMMENT'])) {
    echo "<pre>"; print_r($columnEntry);exit; //add this heare
    throw new Zend_Db_Exception("Cannot create table without columns comments");
}

Now, instead of the exception you should see how the $columnEntry looks like and get an idea on what is the problematic attribute.

Magento – Magento 1.8 reindex all

The catalog_product_index_eav table has a foreign key contraint named

FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID

Looking at this table's definition

CREATE TABLE `catalog_product_index_eav` (
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `value` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Value',
  PRIMARY KEY (`entity_id`,`attribute_id`,`store_id`,`value`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID` (`entity_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE` (`value`),
  CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product EAV Index Table';

we can see the foreign key definition is

CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` 
FOREIGN KEY (`entity_id`) 
REFERENCES `catalog_product_entity` (`entity_id`) 
ON DELETE CASCADE ON UPDATE CASCADE

This means for every entity_id row in catalog_product_index_eav, there needs to be an identical, corresponding entity_id value in catalog_product_entity.

The root of your problem is for some reason (either a rouge extension, errors caused by randomly typing in SQL from the internet, or performing data updates with disabled indexes), Magento's indexing attempts to update data in catalog_product_index_eav that violates this rule. The next step is identifying what Magento's doing so you can fix the data.

If we look at your call stack, this looks like a good place to start debugging

#10 app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): 
Mage_Index_Model_Resource_Abstract->syncData()

Jumping to that source file, we see the following bit of code

public function syncData()
{
    $this->beginTransaction();
    try {
        /**
         * Can't use truncate because of transaction
         */
        $this->_getWriteAdapter()->delete($this->getMainTable());
        $this->insertFromTable($this->getIdxTable(), $this->getMainTable(), false);
        $this->commit();
    } catch (Exception $e) {
        $this->rollBack();
        throw $e;
    }
    return $this;
}

As part of its indexing process, Magento tries to sync data from an "index table" (getIdxTable), to a "source" (getMainTable) table.

public function insertFromTable($sourceTable, $destTable, $readToIndex = true)
{
    //...
}

For this particular index, the index table is catalog_product_index_eav_idx, and the source table is catalog_product_index_eav.

Note: Be careful with your terminology around here, things are confusingly named. The "Source" table is the table we're copying to. (I believe it's called the source table because it's the "source" a normal Magento system will query from when it needs information)

So, Magento is trying to sync a row from catalog_product_index_eav_idx to the table catalog_product_index_eav. However, this causes the previously mentioned foreign key error. This leads us to two possible conclusions

The catalog_product_index_eav_idx has entity_id rows that do not exist in catalog_product_entity.
The catalog_product_index_eav table has (through previous manipulation with index checks turned off) entity_id rows that do not exist in catalog_product_entity.

So, your mission here is to figure out which entity_id rows in catalog_product_index_eav and catalog_product_index_eav_idx don't exist in catalog_product_entity, and manually delete said rows (from catalog_product_index_eav and catalog_product_index_eav_idx).

If it were me, and my catalog_product_entity table wasn't too large, I'd start with the following queries (these are untested, as I don't have any Magento tables with the above invalid data states)

SELECT * 
FROM catalog_product_index_eav_idx 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

SELECT * FROM catalog_product_index_eav 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

Good luck!

Best Answer

Related Solutions

Magento – Catalog Flat Category reindex issue

Magento – Magento 1.8 reindex all

Related Topic