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?
