1

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)
Improve this question

1 Answer 1

Reset to default
1

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).

Improve this answer
6
  • Yes, you are right. Field types are different. I tried to execute query with right field types. But it was unsuccessful. And another question: why magento tries to create table with foreing keys while live creates this table without them.
    – zhartaunik
    Commented Apr 29, 2015 at 14:12
  • I wasn't clear what you meant by that in your original post. Which table's FKs are you referring to?
    – laketuna
    Commented Apr 29, 2015 at 14:14
  • 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
    – zhartaunik
    Commented Apr 29, 2015 at 14:19
  • If I remove one of foreign_keys from query manualy - the query doesn't execute.
    – zhartaunik
    Commented Apr 29, 2015 at 14:57
  • Updated my answer.
    – laketuna
    Commented Apr 29, 2015 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.