The upgrade error is probably corrected by updating most tables to use the InnoDB engine (instead of MyISAM). The syntax to update tables to the InnoDB engine is as follows:
ALTER TABLE table_name ENGINE = InnoDB;
I noticed that a fresh install had no problem reindexing everything, but there were odd errors creating tables after an upgrade. The errors are found by turning on the exceptions log: Admin -> System -> Configuration -> Developer -> Log Settings -> Enabled = Yes.
This outputs to: /var/log/exception.log
Most errors had something to do with cryptic foreign key constraints. Changing most tables to InnoDB seems to solve this problem.
I’m including the code that I ran on sql.
ALTER TABLE admin_assert ENGINE = InnoDB;
ALTER TABLE admin_role ENGINE = InnoDB;
ALTER TABLE admin_rule ENGINE = InnoDB;
ALTER TABLE admin_user ENGINE = InnoDB;
ALTER TABLE api_assert ENGINE = InnoDB;
ALTER TABLE api_role ENGINE = InnoDB;
ALTER TABLE api_rule ENGINE = InnoDB;
ALTER TABLE api_user ENGINE = InnoDB;
ALTER TABLE catalogindex_eav ENGINE = InnoDB;
ALTER TABLE catalogindex_minimal_price ENGINE = InnoDB;
ALTER TABLE catalogindex_price ENGINE = InnoDB;
ALTER TABLE cataloginventory_stock_item ENGINE = InnoDB;
ALTER TABLE catalogrule ENGINE = InnoDB;
ALTER TABLE catalogrule_product ENGINE = InnoDB;
ALTER TABLE catalogrule_product_price ENGINE = InnoDB;
ALTER TABLE catalogsearch_query ENGINE = InnoDB;
ALTER TABLE catalog_category_entity ENGINE = InnoDB;
ALTER TABLE catalog_category_entity_datetime ENGINE = InnoDB;
ALTER TABLE catalog_category_entity_decimal ENGINE = InnoDB;
ALTER TABLE catalog_category_entity_int ENGINE = InnoDB;
ALTER TABLE catalog_category_entity_text ENGINE = InnoDB;
ALTER TABLE catalog_category_entity_varchar ENGINE = InnoDB;
ALTER TABLE catalog_category_product ENGINE = InnoDB;
ALTER TABLE catalog_category_product_index ENGINE = InnoDB;
ALTER TABLE catalog_compare_item ENGINE = InnoDB;
ALTER TABLE catalog_product_bundle_option ENGINE = InnoDB;
ALTER TABLE catalog_product_bundle_option_value ENGINE = InnoDB;
ALTER TABLE catalog_product_bundle_selection ENGINE = InnoDB;
ALTER TABLE catalog_product_enabled_index ENGINE = InnoDB;
ALTER TABLE catalog_product_entity_datetime ENGINE = InnoDB;
ALTER TABLE catalog_product_entity_decimal ENGINE = InnoDB;
ALTER TABLE catalog_product_entity_gallery ENGINE = InnoDB;
ALTER TABLE catalog_product_entity_int ENGINE = InnoDB;
ALTER TABLE catalog_product_entity_media_gallery ENGINE = InnoDB;
ALTER TABLE catalog_product_entity_media_gallery_value ENGINE = InnoDB;
ALTER TABLE catalog_product_entity_text ENGINE = InnoDB;
ALTER TABLE catalog_product_entity_tier_price ENGINE = InnoDB;
ALTER TABLE catalog_product_entity_varchar ENGINE = InnoDB;
ALTER TABLE catalog_product_link ENGINE = InnoDB;
ALTER TABLE catalog_product_link_attribute ENGINE = InnoDB;
ALTER TABLE catalog_product_link_attribute_decimal ENGINE = InnoDB;
ALTER TABLE catalog_product_link_attribute_int ENGINE = InnoDB;
ALTER TABLE catalog_product_link_attribute_varchar ENGINE = InnoDB;
ALTER TABLE catalog_product_link_type ENGINE = InnoDB;
ALTER TABLE catalog_product_option ENGINE = InnoDB;
ALTER TABLE catalog_product_option_price ENGINE = InnoDB;
ALTER TABLE catalog_product_option_title ENGINE = InnoDB;
ALTER TABLE catalog_product_option_type_price ENGINE = InnoDB;
ALTER TABLE catalog_product_option_type_title ENGINE = InnoDB;
ALTER TABLE catalog_product_option_type_value ENGINE = InnoDB;
ALTER TABLE catalog_product_super_attribute ENGINE = InnoDB;
ALTER TABLE catalog_product_super_attribute_label ENGINE = InnoDB;
ALTER TABLE catalog_product_super_attribute_pricing ENGINE = InnoDB;
ALTER TABLE catalog_product_super_link ENGINE = InnoDB;
ALTER TABLE catalog_product_website ENGINE = InnoDB;
ALTER TABLE checkout_agreement ENGINE = InnoDB;
ALTER TABLE checkout_agreement_store ENGINE = InnoDB;
ALTER TABLE cms_block ENGINE = InnoDB;
ALTER TABLE cms_block_store ENGINE = InnoDB;
ALTER TABLE cms_page ENGINE = InnoDB;
ALTER TABLE cms_page_store ENGINE = InnoDB;
ALTER TABLE core_config_data ENGINE = InnoDB;
ALTER TABLE core_email_template ENGINE = InnoDB;
ALTER TABLE core_flag ENGINE = InnoDB;
ALTER TABLE core_layout_link ENGINE = InnoDB;
ALTER TABLE core_layout_update ENGINE = InnoDB;
ALTER TABLE core_resource ENGINE = InnoDB;
ALTER TABLE core_session ENGINE = InnoDB;
ALTER TABLE core_store ENGINE = InnoDB;
ALTER TABLE core_store_group ENGINE = InnoDB;
ALTER TABLE core_translate ENGINE = InnoDB;
ALTER TABLE core_url_rewrite ENGINE = InnoDB;
ALTER TABLE cron_schedule ENGINE = InnoDB;
ALTER TABLE customer_address_entity ENGINE = InnoDB;
ALTER TABLE customer_address_entity_datetime ENGINE = InnoDB;
ALTER TABLE customer_address_entity_decimal ENGINE = InnoDB;
ALTER TABLE customer_address_entity_int ENGINE = InnoDB;
ALTER TABLE customer_address_entity_text ENGINE = InnoDB;
ALTER TABLE customer_address_entity_varchar ENGINE = InnoDB;
ALTER TABLE customer_entity ENGINE = InnoDB;
ALTER TABLE customer_entity_datetime ENGINE = InnoDB;
ALTER TABLE customer_entity_decimal ENGINE = InnoDB;
ALTER TABLE customer_entity_int ENGINE = InnoDB;
ALTER TABLE customer_entity_text ENGINE = InnoDB;
ALTER TABLE customer_entity_varchar ENGINE = InnoDB;
ALTER TABLE customer_group ENGINE = InnoDB;
ALTER TABLE dataflow_batch ENGINE = InnoDB;
ALTER TABLE dataflow_batch_export ENGINE = InnoDB;
ALTER TABLE dataflow_batch_import ENGINE = InnoDB;
ALTER TABLE dataflow_import_data ENGINE = InnoDB;
ALTER TABLE dataflow_profile ENGINE = InnoDB;
ALTER TABLE dataflow_profile_history ENGINE = InnoDB;
ALTER TABLE dataflow_session ENGINE = InnoDB;
ALTER TABLE design_change ENGINE = InnoDB;
ALTER TABLE directory_country ENGINE = InnoDB;
ALTER TABLE directory_country_region ENGINE = InnoDB;
ALTER TABLE directory_country_region_name ENGINE = InnoDB;
ALTER TABLE directory_currency_rate ENGINE = InnoDB;
ALTER TABLE eav_attribute_group ENGINE = InnoDB;
ALTER TABLE eav_attribute_option ENGINE = InnoDB;
ALTER TABLE eav_attribute_option_value ENGINE = InnoDB;
ALTER TABLE eav_attribute_set ENGINE = InnoDB;
ALTER TABLE eav_entity ENGINE = InnoDB;
ALTER TABLE eav_entity_attribute ENGINE = InnoDB;
ALTER TABLE eav_entity_datetime ENGINE = InnoDB;
ALTER TABLE eav_entity_decimal ENGINE = InnoDB;
ALTER TABLE eav_entity_int ENGINE = InnoDB;
ALTER TABLE eav_entity_text ENGINE = InnoDB;
ALTER TABLE eav_entity_type ENGINE = InnoDB;
ALTER TABLE eav_entity_varchar ENGINE = InnoDB;
ALTER TABLE gift_message ENGINE = InnoDB;
ALTER TABLE googlebase_attributes ENGINE = InnoDB;
ALTER TABLE googlebase_items ENGINE = InnoDB;
ALTER TABLE googlebase_types ENGINE = InnoDB;
ALTER TABLE googlecheckout_api_debug ENGINE = InnoDB;
ALTER TABLE googleoptimizer_code ENGINE = InnoDB;
ALTER TABLE newsletter_problem ENGINE = InnoDB;
ALTER TABLE newsletter_queue ENGINE = InnoDB;
ALTER TABLE newsletter_queue_link ENGINE = InnoDB;
ALTER TABLE newsletter_queue_store_link ENGINE = InnoDB;
ALTER TABLE newsletter_subscriber ENGINE = InnoDB;
ALTER TABLE newsletter_template ENGINE = InnoDB;
ALTER TABLE paygate_authorizenet_debug ENGINE = InnoDB;
ALTER TABLE paypaluk_api_debug ENGINE = InnoDB;
ALTER TABLE paypal_api_debug ENGINE = InnoDB;
ALTER TABLE poll ENGINE = InnoDB;
ALTER TABLE poll_answer ENGINE = InnoDB;
ALTER TABLE poll_store ENGINE = InnoDB;
ALTER TABLE poll_vote ENGINE = InnoDB;
ALTER TABLE product_alert_price ENGINE = InnoDB;
ALTER TABLE rating ENGINE = InnoDB;
ALTER TABLE rating_entity ENGINE = InnoDB;
ALTER TABLE rating_option ENGINE = InnoDB;
ALTER TABLE rating_option_vote ENGINE = InnoDB;
ALTER TABLE rating_option_vote_aggregated ENGINE = InnoDB;
ALTER TABLE rating_store ENGINE = InnoDB;
ALTER TABLE rating_title ENGINE = InnoDB;
ALTER TABLE report_event ENGINE = InnoDB;
ALTER TABLE report_event_types ENGINE = InnoDB;
ALTER TABLE review ENGINE = InnoDB;
ALTER TABLE review_detail ENGINE = InnoDB;
ALTER TABLE review_entity ENGINE = InnoDB;
ALTER TABLE review_entity_summary ENGINE = InnoDB;
ALTER TABLE review_status ENGINE = InnoDB;
ALTER TABLE review_store ENGINE = InnoDB;
ALTER TABLE salesrule ENGINE = InnoDB;
ALTER TABLE salesrule_customer ENGINE = InnoDB;
ALTER TABLE sales_flat_order_item ENGINE = InnoDB;
ALTER TABLE sales_flat_quote ENGINE = InnoDB;
ALTER TABLE sales_flat_quote_address ENGINE = InnoDB;
ALTER TABLE sales_flat_quote_address_item ENGINE = InnoDB;
ALTER TABLE sales_flat_quote_item ENGINE = InnoDB;
ALTER TABLE sales_flat_quote_item_option ENGINE = InnoDB;
ALTER TABLE sales_flat_quote_payment ENGINE = InnoDB;
ALTER TABLE sales_flat_quote_shipping_rate ENGINE = InnoDB;
ALTER TABLE sales_order ENGINE = InnoDB;
ALTER TABLE sales_order_datetime ENGINE = InnoDB;
ALTER TABLE sales_order_decimal ENGINE = InnoDB;
ALTER TABLE sales_order_entity ENGINE = InnoDB;
ALTER TABLE sales_order_entity_datetime ENGINE = InnoDB;
ALTER TABLE sales_order_entity_decimal ENGINE = InnoDB;
ALTER TABLE sales_order_entity_int ENGINE = InnoDB;
ALTER TABLE sales_order_entity_text ENGINE = InnoDB;
ALTER TABLE sales_order_entity_varchar ENGINE = InnoDB;
ALTER TABLE sales_order_int ENGINE = InnoDB;
ALTER TABLE sales_order_tax ENGINE = InnoDB;
ALTER TABLE sales_order_text ENGINE = InnoDB;
ALTER TABLE sales_order_varchar ENGINE = InnoDB;
In the end, the only tables that are using the MyISAM engine are as follows:
ALTER TABLE catalogsearch_fulltext ENGINE = MyISAM;
ALTER TABLE catalog_product_index_price_bndl_opt ENGINE = MyISAM;
ALTER TABLE catalog_product_index_price_bndl_sel ENGINE = MyISAM;
ALTER TABLE catalog_product_index_price_bundle ENGINE = MyISAM;
ALTER TABLE catalog_product_index_price_downloadable_idx ENGINE = MyISAM;
ALTER TABLE catalog_product_index_price_final_idx ENGINE = MyISAM;
ALTER TABLE catalog_product_index_price_idx_cfg_option ENGINE = MyISAM;
ALTER TABLE catalog_product_index_price_idx_cfg_opt_aggregate ENGINE = MyISAM;
ALTER TABLE catalog_product_index_price_idx_option ENGINE = MyISAM;
ALTER TABLE catalog_product_index_price_idx_option_aggregate ENGINE = MyISAM;
ALTER TABLE sendfriend_log ENGINE = MyISAM;
After updating to InnoDB I can able to reindex everything successfully and the store seems to be back up and running as it should. All products are back.
I hope this post will help to solve problem of reindexing.
If this post is helpful to you, don’t forget to comment.
Thanks,
Bijal Bhavsar 🙂
Magecracker 