Try the Demo

Magento

  • Home
  • Wiki
  • Recover
  • Recover categories and products from a broken Magento database

Recover categories and products from a broken Magento database

Last modified by Nick Weisser on Fri, June 25, 2010 13:10
Source|Old Revisions  

This is an old revision of the document!

I had a client who inadvertently upgraded from Magento 1.4.0.1 to 1.4.1.0 via Virtualmin script installer and ended up with a broken store. We were then trying to connect the database to a fresh install of 1.4.0.1 to no avail, so we decided to just extract all catalog* and eav* tables of the spoiled database and import them into a clean 1.4.0.1 database.

Once you disabled foreign key constraints, this is pretty easy to do. It can be easily achieved by adding 

SET foreign_key_checks = 0;

to the top of your sql queries for steps 2 and 3:

  1. export all catalog* and eav* tables (via phpMyAdmin) from the old database
  2. delete all catalog* and eav* tables in the new database
  3. import all exported tables from step 1 into the new database

SQL queries for step 2

 
SET foreign_key_checks = 0; 
DROP TABLE IF EXISTS catalog_category_anc_categs_index_idx;
DROP TABLE IF EXISTS catalog_category_anc_categs_index_tmp;
DROP TABLE IF EXISTS catalog_category_anc_products_index_idx;
DROP TABLE IF EXISTS catalog_category_anc_products_index_tmp;
DROP TABLE IF EXISTS catalog_category_entity;
DROP TABLE IF EXISTS catalog_category_entity_datetime;
DROP TABLE IF EXISTS catalog_category_entity_decimal;
DROP TABLE IF EXISTS catalog_category_entity_int;
DROP TABLE IF EXISTS catalog_category_entity_text;
DROP TABLE IF EXISTS catalog_category_entity_varchar;
DROP TABLE IF EXISTS catalog_category_product;
DROP TABLE IF EXISTS catalog_category_product_index;
DROP TABLE IF EXISTS catalog_category_product_index_enbl_idx;
DROP TABLE IF EXISTS catalog_category_product_index_enbl_tmp;
DROP TABLE IF EXISTS catalog_category_product_index_idx;
DROP TABLE IF EXISTS catalog_category_product_index_tmp;
DROP TABLE IF EXISTS catalog_compare_item;
DROP TABLE IF EXISTS catalog_eav_attribute;
DROP TABLE IF EXISTS catalog_product_bundle_option;
DROP TABLE IF EXISTS catalog_product_bundle_option_value;
DROP TABLE IF EXISTS catalog_product_bundle_price_index;
DROP TABLE IF EXISTS catalog_product_bundle_selection;
DROP TABLE IF EXISTS catalog_product_bundle_stock_index;
DROP TABLE IF EXISTS catalog_product_enabled_index;
DROP TABLE IF EXISTS catalog_product_entity;
DROP TABLE IF EXISTS catalog_product_entity_datetime;
DROP TABLE IF EXISTS catalog_product_entity_decimal;
DROP TABLE IF EXISTS catalog_product_entity_gallery;
DROP TABLE IF EXISTS catalog_product_entity_int;
DROP TABLE IF EXISTS catalog_product_entity_media_gallery;
DROP TABLE IF EXISTS catalog_product_entity_media_gallery_value;
DROP TABLE IF EXISTS catalog_product_entity_text;
DROP TABLE IF EXISTS catalog_product_entity_tier_price;
DROP TABLE IF EXISTS catalog_product_entity_varchar;
DROP TABLE IF EXISTS catalog_product_index_eav;
DROP TABLE IF EXISTS catalog_product_index_eav_decimal;
DROP TABLE IF EXISTS catalog_product_index_eav_decimal_idx;
DROP TABLE IF EXISTS catalog_product_index_eav_decimal_tmp;
DROP TABLE IF EXISTS catalog_product_index_eav_idx;
DROP TABLE IF EXISTS catalog_product_index_eav_tmp;
DROP TABLE IF EXISTS catalog_product_index_price;
DROP TABLE IF EXISTS catalog_product_index_price_bundle_idx;
DROP TABLE IF EXISTS catalog_product_index_price_bundle_opt_idx;
DROP TABLE IF EXISTS catalog_product_index_price_bundle_opt_tmp;
DROP TABLE IF EXISTS catalog_product_index_price_bundle_sel_idx;
DROP TABLE IF EXISTS catalog_product_index_price_bundle_sel_tmp;
DROP TABLE IF EXISTS catalog_product_index_price_bundle_tmp;
DROP TABLE IF EXISTS catalog_product_index_price_cfg_opt_agr_idx;
DROP TABLE IF EXISTS catalog_product_index_price_cfg_opt_agr_tmp;
DROP TABLE IF EXISTS catalog_product_index_price_cfg_opt_idx;
DROP TABLE IF EXISTS catalog_product_index_price_cfg_opt_tmp;
DROP TABLE IF EXISTS catalog_product_index_price_downlod_idx;
DROP TABLE IF EXISTS catalog_product_index_price_downlod_tmp;
DROP TABLE IF EXISTS catalog_product_index_price_final_idx;
DROP TABLE IF EXISTS catalog_product_index_price_final_tmp;
DROP TABLE IF EXISTS catalog_product_index_price_idx;
DROP TABLE IF EXISTS catalog_product_index_price_opt_agr_idx;
DROP TABLE IF EXISTS catalog_product_index_price_opt_agr_tmp;
DROP TABLE IF EXISTS catalog_product_index_price_opt_idx;
DROP TABLE IF EXISTS catalog_product_index_price_opt_tmp;
DROP TABLE IF EXISTS catalog_product_index_price_tmp;
DROP TABLE IF EXISTS catalog_product_index_tier_price;
DROP TABLE IF EXISTS catalog_product_index_website;
DROP TABLE IF EXISTS catalog_product_link;
DROP TABLE IF EXISTS catalog_product_link_attribute;
DROP TABLE IF EXISTS catalog_product_link_attribute_decimal;
DROP TABLE IF EXISTS catalog_product_link_attribute_int;
DROP TABLE IF EXISTS catalog_product_link_attribute_varchar;
DROP TABLE IF EXISTS catalog_product_link_type;
DROP TABLE IF EXISTS catalog_product_option;
DROP TABLE IF EXISTS catalog_product_option_price;
DROP TABLE IF EXISTS catalog_product_option_title;
DROP TABLE IF EXISTS catalog_product_option_type_price;
DROP TABLE IF EXISTS catalog_product_option_type_title;
DROP TABLE IF EXISTS catalog_product_option_type_value;
DROP TABLE IF EXISTS catalog_product_relation;
DROP TABLE IF EXISTS catalog_product_super_attribute;
DROP TABLE IF EXISTS catalog_product_super_attribute_label;
DROP TABLE IF EXISTS catalog_product_super_attribute_pricing;
DROP TABLE IF EXISTS catalog_product_super_link;
DROP TABLE IF EXISTS catalog_product_website;
DROP TABLE IF EXISTS catalogindex_aggregation;
DROP TABLE IF EXISTS catalogindex_aggregation_tag;
DROP TABLE IF EXISTS catalogindex_aggregation_to_tag;
DROP TABLE IF EXISTS catalogindex_eav;
DROP TABLE IF EXISTS catalogindex_minimal_price;
DROP TABLE IF EXISTS catalogindex_price;
DROP TABLE IF EXISTS cataloginventory_stock;
DROP TABLE IF EXISTS cataloginventory_stock_item;
DROP TABLE IF EXISTS cataloginventory_stock_status;
DROP TABLE IF EXISTS cataloginventory_stock_status_idx;
DROP TABLE IF EXISTS cataloginventory_stock_status_tmp;
DROP TABLE IF EXISTS catalogrule;
DROP TABLE IF EXISTS catalogrule_affected_product;
DROP TABLE IF EXISTS catalogrule_group_website;
DROP TABLE IF EXISTS catalogrule_product;
DROP TABLE IF EXISTS catalogrule_product_price;
DROP TABLE IF EXISTS catalogsearch_fulltext;
DROP TABLE IF EXISTS catalogsearch_query;
DROP TABLE IF EXISTS catalogsearch_result;
DROP TABLE IF EXISTS checkout_agreement;
DROP TABLE IF EXISTS checkout_agreement_store;
DROP TABLE IF EXISTS eav_attribute;
DROP TABLE IF EXISTS eav_attribute_group;
DROP TABLE IF EXISTS eav_attribute_label;
DROP TABLE IF EXISTS eav_attribute_option;
DROP TABLE IF EXISTS eav_attribute_option_value;
DROP TABLE IF EXISTS eav_attribute_set;
DROP TABLE IF EXISTS eav_entity;
DROP TABLE IF EXISTS eav_entity_attribute;
DROP TABLE IF EXISTS eav_entity_datetime;
DROP TABLE IF EXISTS eav_entity_decimal;
DROP TABLE IF EXISTS eav_entity_int;
DROP TABLE IF EXISTS eav_entity_store;
DROP TABLE IF EXISTS eav_entity_text;
DROP TABLE IF EXISTS eav_entity_type;
DROP TABLE IF EXISTS eav_entity_varchar;
DROP TABLE IF EXISTS eav_form_element;
DROP TABLE IF EXISTS eav_form_fieldset;
DROP TABLE IF EXISTS eav_form_fieldset_label;
DROP TABLE IF EXISTS eav_form_type;
DROP TABLE IF EXISTS eav_form_type_entity;



 

Explore the Wiki

Magento Developer Certification

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs