cancel
Showing results for 
Search instead for 
Did you mean: 

Corrupted database?

Corrupted database?

Started having problems after colleague was rearranging categories and he created a new attribute for filtering.

He couldn't get access to the products after that - I found that the attribute had been created but had a duplicate value of '0' in the database table. I corrected this by giving it it's own value id and the site worked again.

 

However I have since run into a lot of problems.

 

There are approx 370 products in store all with the same price of 9.99 but the catalog_product_index_price and catalog_product_index_price_idx have become very large - 20MB each both with 315,000 records and they seem to be increasing!

 

I was getting timeout errors on the server when saving products and the hosts informed me that there was no mysql optimisation on the server and was advised to increase the innodb_buffer_pool size.

 

I've tried running the database repair tool only to get the following errors

 

Repair Corrupted Database

    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_category_entity_datetime` ADD PRIMARY KEY (`value_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_category_entity_int` ADD PRIMARY KEY (`value_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_category_entity_text` ADD PRIMARY KEY (`value_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_category_entity_varchar` ADD PRIMARY KEY (`value_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_entity_datetime` ADD PRIMARY KEY (`value_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_entity_decimal` ADD PRIMARY KEY (`value_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_entity_int` ADD PRIMARY KEY (`value_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_entity_text` ADD PRIMARY KEY (`value_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_entity_varchar` ADD PRIMARY KEY (`value_id`)
    Error #1062: Duplicate entry '7-0-1' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_index_price` ADD PRIMARY KEY (`entity_id`,`customer_group_id`,`website_id`)
    Error #1062: Duplicate entry '7-0-1' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_index_price_idx` ADD PRIMARY KEY (`entity_id`,`customer_group_id`,`website_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_link` ADD PRIMARY KEY (`link_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_link_attribute_int` ADD PRIMARY KEY (`value_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_option` ADD PRIMARY KEY (`option_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalog_product_option_title` ADD PRIMARY KEY (`option_title_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_catalogsearch_fulltext` ADD PRIMARY KEY (`fulltext_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_core_config_data` ADD PRIMARY KEY (`config_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_core_url_rewrite` ADD PRIMARY KEY (`url_rewrite_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_dataflow_batch` ADD PRIMARY KEY (`batch_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_dataflow_batch_export` ADD PRIMARY KEY (`batch_export_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_dataflow_batch_import` ADD PRIMARY KEY (`batch_import_id`)
    Error #1062: Duplicate entry '0' for key 'PRIMARY' on SQL: ALTER TABLE `cre_dataflow_profile_history` ADD PRIMARY KEY (`history_id`)
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_catalog_product_bundle_option` ADD CONSTRAINT `FK_CRE_CAT_PRD_BNDL_OPT_PARENT_ID_CRE_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`parent_id`) REFERENCES `cre_catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_catalog_product_entity` ADD CONSTRAINT `FK_CRE_CAT_PRD_ENTT_ATTR_SET_ID_CRE_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `cre_eav_attribute_set` (`attribute_set_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 150) on SQL: ALTER TABLE `cre_catalog_product_link_attribute_decimal` ADD CONSTRAINT `FK_CRE_CAT_PRD_LNK_ATTR_DEC_LNK_ID_CRE_CAT_PRD_LNK_LNK_ID` FOREIGN KEY (`link_id`) REFERENCES `cre_catalog_product_link` (`link_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 150) on SQL: ALTER TABLE `cre_catalog_product_link_attribute_int` ADD CONSTRAINT `FK_CRE_CAT_PRD_LNK_ATTR_INT_LNK_ID_CRE_CAT_PRD_LNK_LNK_ID` FOREIGN KEY (`link_id`) REFERENCES `cre_catalog_product_link` (`link_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 150) on SQL: ALTER TABLE `cre_catalog_product_link_attribute_varchar` ADD CONSTRAINT `FK_CRE_CAT_PRD_LNK_ATTR_VCHR_LNK_ID_CRE_CAT_PRD_LNK_LNK_ID` FOREIGN KEY (`link_id`) REFERENCES `cre_catalog_product_link` (`link_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 150) on SQL: ALTER TABLE `cre_catalog_product_option_price` ADD CONSTRAINT `FK_CRE_CAT_PRD_OPT_PRICE_OPT_ID_CRE_CAT_PRD_OPT_OPT_ID` FOREIGN KEY (`option_id`) REFERENCES `cre_catalog_product_option` (`option_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 150) on SQL: ALTER TABLE `cre_catalog_product_option_title` ADD CONSTRAINT `FK_CRE_CAT_PRD_OPT_TTL_OPT_ID_CRE_CAT_PRD_OPT_OPT_ID` FOREIGN KEY (`option_id`) REFERENCES `cre_catalog_product_option` (`option_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 150) on SQL: ALTER TABLE `cre_catalog_product_option_type_value` ADD CONSTRAINT `FK_CRE_CAT_PRD_OPT_TYPE_VAL_OPT_ID_CRE_CAT_PRD_OPT_OPT_ID` FOREIGN KEY (`option_id`) REFERENCES `cre_catalog_product_option` (`option_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_core_store` ADD CONSTRAINT `FK_CRE_CORE_STORE_GROUP_ID_CRE_CORE_STORE_GROUP_GROUP_ID` FOREIGN KEY (`group_id`) REFERENCES `cre_core_store_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_core_store_group` ADD CONSTRAINT `FK_CRE_CORE_STORE_GROUP_WEBSITE_ID_CRE_CORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `cre_core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_customer_entity` ADD CONSTRAINT `FK_CRE_CUSTOMER_ENTITY_STORE_ID_CRE_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `cre_core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 150) on SQL: ALTER TABLE `cre_dataflow_batch_export` ADD CONSTRAINT `FK_7AF9F1B22EDC82A39EAAD93093551510` FOREIGN KEY (`batch_id`) REFERENCES `cre_dataflow_batch` (`batch_id`) ON DELETE CASCADE ON UPDATE NO ACTION
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 150) on SQL: ALTER TABLE `cre_dataflow_batch_import` ADD CONSTRAINT `FK_4A81AEE0CA1FC4AF271C24EA0490C6C2` FOREIGN KEY (`batch_id`) REFERENCES `cre_dataflow_batch` (`batch_id`) ON DELETE CASCADE ON UPDATE NO ACTION
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_eav_attribute` ADD CONSTRAINT `FK_CRE_EAV_ATTR_ENTT_TYPE_ID_CRE_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `cre_eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_eav_attribute_option` ADD CONSTRAINT `FK_CRE_EAV_ATTR_OPT_ATTR_ID_CRE_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `cre_eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_eav_attribute_option_value` ADD CONSTRAINT `FK_CRE_EAV_ATTR_OPT_VAL_OPT_ID_CRE_EAV_ATTR_OPT_OPT_ID` FOREIGN KEY (`option_id`) REFERENCES `cre_eav_attribute_option` (`option_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_eav_attribute_set` ADD CONSTRAINT `FK_CRE_EAV_ATTR_SET_ENTT_TYPE_ID_CRE_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `cre_eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_m_filter` ADD CONSTRAINT `FK_CRE_M_FILTER_TYPE` FOREIGN KEY (`entity_type_id`) REFERENCES `cre_eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_m_filter2_store` ADD CONSTRAINT `FK_CRE_M_FILTER2_STORE_CORE/STORE` FOREIGN KEY (`store_id`) REFERENCES `cre_core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_newsletter_queue` ADD CONSTRAINT `FK_CRE_NLTTR_QUEUE_TEMPLATE_ID_CRE_NLTTR_TEMPLATE_TEMPLATE_ID` FOREIGN KEY (`template_id`) REFERENCES `cre_newsletter_template` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_poll` ADD CONSTRAINT `FK_CRE_POLL_STORE_ID_CRE_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `cre_core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_poll_answer` ADD CONSTRAINT `FK_CRE_POLL_ANSWER_POLL_ID_CRE_POLL_POLL_ID` FOREIGN KEY (`poll_id`) REFERENCES `cre_poll` (`poll_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_rating` ADD CONSTRAINT `FK_CRE_RATING_ENTITY_ID_CRE_RATING_ENTITY_ENTITY_ID` FOREIGN KEY (`entity_id`) REFERENCES `cre_rating_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_rating_option` ADD CONSTRAINT `FK_CRE_RATING_OPTION_RATING_ID_CRE_RATING_RATING_ID` FOREIGN KEY (`rating_id`) REFERENCES `cre_rating` (`rating_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_review` ADD CONSTRAINT `FK_CRE_REVIEW_ENTITY_ID_CRE_REVIEW_ENTITY_ENTITY_ID` FOREIGN KEY (`entity_id`) REFERENCES `cre_review_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_billing_agreement` ADD CONSTRAINT `FK_CRE_SALES_BILLING_AGRT_CSTR_ID_CRE_CSTR_ENTT_ENTT_ID` FOREIGN KEY (`customer_id`) REFERENCES `cre_customer_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_flat_creditmemo` ADD CONSTRAINT `FK_AD4CE6E8126CE8626F4E07BA33C87FED` FOREIGN KEY (`order_id`) REFERENCES `cre_sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_flat_invoice` ADD CONSTRAINT `FK_CRE_SALES_FLAT_INVOICE_ORDER_ID_CRE_SALES_FLAT_ORDER_ENTT_ID` FOREIGN KEY (`order_id`) REFERENCES `cre_sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_flat_order` ADD CONSTRAINT `FK_CRE_SALES_FLAT_ORDER_CSTR_ID_CRE_CSTR_ENTT_ENTT_ID` FOREIGN KEY (`customer_id`) REFERENCES `cre_customer_entity` (`entity_id`) ON DELETE SET NULL ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_flat_order_item` ADD CONSTRAINT `FK_014EC2076A4C5265AA1097B1D2438BCC` FOREIGN KEY (`order_id`) REFERENCES `cre_sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_flat_order_payment` ADD CONSTRAINT `FK_639C3FE94680D764872C0F04C19ADAFF` FOREIGN KEY (`parent_id`) REFERENCES `cre_sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_flat_quote` ADD CONSTRAINT `FK_CRE_SALES_FLAT_QUOTE_STORE_ID_CRE_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `cre_core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_flat_quote_address` ADD CONSTRAINT `FK_2010A450A4E6FC197DA8B6FC37029C04` FOREIGN KEY (`quote_id`) REFERENCES `cre_sales_flat_quote` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_flat_quote_item` ADD CONSTRAINT `FK_1039F06A86E02C227B34888D8176B463` FOREIGN KEY (`quote_id`) REFERENCES `cre_sales_flat_quote` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_flat_shipment` ADD CONSTRAINT `FK_CRE_SALES_FLAT_SHIPMENT_ORDER_ID_CRE_SALES_FLAT_ORDER_ENTT_ID` FOREIGN KEY (`order_id`) REFERENCES `cre_sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_sales_recurring_profile` ADD CONSTRAINT `FK_CRE_SALES_RECURRING_PROFILE_CSTR_ID_CRE_CSTR_ENTT_ENTT_ID` FOREIGN KEY (`customer_id`) REFERENCES `cre_customer_entity` (`entity_id`) ON DELETE SET NULL ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_salesrule_coupon` ADD CONSTRAINT `FK_CRE_SALESRULE_COUPON_RULE_ID_CRE_SALESRULE_RULE_ID` FOREIGN KEY (`rule_id`) REFERENCES `cre_salesrule` (`rule_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_tag` ADD CONSTRAINT `FK_CRE_TAG_FIRST_CUSTOMER_ID_CRE_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`first_customer_id`) REFERENCES `cre_customer_entity` (`entity_id`) ON DELETE SET NULL ON UPDATE NO ACTION
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_widget_instance_page` ADD CONSTRAINT `FK_A9B5C34DF9B406A1F4AF9AB98F3DF0FF` FOREIGN KEY (`instance_id`) REFERENCES `cre_widget_instance` (`instance_id`) ON DELETE CASCADE ON UPDATE CASCADE
    Error #1005: Can't create table 'database2.#sql-83e_196244' (errno: 121) on SQL: ALTER TABLE `cre_wishlist` ADD CONSTRAINT `FK_CRE_WISHLIST_CUSTOMER_ID_CRE_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `cre_customer_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE

 

Would like any suggestions as to what would be the best option - should I move the site to a different host or shall I go about fixing the issues?

 

 

2 REPLIES

Re: Corrupted database?

Your best bet is to restore the database from a working backup before the corruption. 

 

Is there any reason why MySQL is not properly tuned before you even install Magento? Are you on shared hosting or your own server? Is it managed or unmanaged? 

James Lee | Moderator • Magento Master
See My Recommended Magento Hosting & Security Tips

Re: Corrupted database?

it may be causes your php version are not supported on our hosting ,Contact our Hosting provider