Hi,
I just ran through an upgrade of non custo installation of Magento 1.9.1 (only theme+products+extensions) to 1.9.2.
The upgrade process has gone smoothly, website is working, extensions migrated, admin is fine, db upgrade went ok except for Catalog_category_flat tables.
For them reindex fails with the following error (got the details using php indexer.php --reindex catalog_category_flat) :
General error: 1005 Can't create table `new_mysql`.`catalog_category_flat_store_1` (errno: 150 "Foreign key constraint is incorrectly formed")
Reindex process tries to apply following table sql script (for store 1)
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 COMMENT 'position' ,
`level` int NOT NULL default '0' COMMENT 'level' ,
`children_count` int NOT NULL 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' ,
`umm_cat_label` varchar(255) NULL default NULL COMMENT 'Category Label' ,
`umm_cat_target` varchar(255) NULL default NULL COMMENT 'Custom URL' ,
`umm_dd_blocks` text NULL default NULL COMMENT 'Category Blocks' ,
`umm_dd_columns` int NULL default NULL COMMENT 'Number of Columns With Subcategories' ,
`umm_dd_proportions` varchar(255) NULL default NULL COMMENT 'Drop-down Content Proportions' ,
`umm_dd_type` varchar(255) NULL default NULL COMMENT 'Submenu Type' ,
`umm_dd_width` varchar(255) NULL default NULL COMMENT 'Drop-down Width' ,
`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
1) Why is the process trying to create this table using INNODB engine, when 95% of my tables are using MyISAM ?
2) Can this be the problem ? If not where is the issue, as my Magento is standard regarding all tables involved.
Thanks for your help.
