Posts Tagged ‘not working’

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 🙂

Advertisements