Can't reindex product flat data

Question

When I try to run the reindex process for product flat data I get this error message

SQLSTATE[HY000]: General error: 1005 Can't create table 'mylal_mage233.#sql-4497_2115c' (errno: 121)

The query being executed is:

ALTER TABLE `mg_catalog_product_flat_1` ADD CONSTRAINT `FK_MG_MG_CAT_PRD_FLAT_1_ENTT_ID_MG_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `mg_catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE

This is usually a foreign key related issue. The problem is that other 4 store views related tables are created with no issue at all and I know there isn't another foreign key with the same name. I checked using this query

    SELECT
    constraint_name,
    table_name
FROM
    information_schema.table_constraints
WHERE
    constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
    constraint_name;

---

EDITED:

Just to clarify: I cannot create a table with a Foreign Key called

FK_MG_MG_CAT_PRD_FLAT_1_ENTT_ID_MG_CAT_PRD_ENTT_ENTT_ID

As said before, there is no Foreign Key in my DB with the same name, which is the error MySQL is returning.

The following code will fail

CREATE TABLE `table_test` (
  `entity_id` int UNSIGNED NOT NULL COMMENT 'entity_id' ,
  PRIMARY KEY  (`entity_id`),
  CONSTRAINT `FK_MG_MG_CAT_PRD_FLAT_1_ENTT_ID_MG_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `mg_catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

Answer 1

Likely, you have missing or malformed foreign keys on your existing tables, and then the indexer creates a temporary table and tries to setup a foreign key relationship with the permeate tables, it errors.

My suggestion is to run the Magento Database Repair Tool, located here: http://www.magentocommerce.com/wiki/doc/db-repair-tool

This involves creating a fresh installation of Magento with another database, using the same version of Magento that you're site currently uses, and then letting this tool compare the two databases for problems.

Make sure to delete this tool when you're done and test it on a non-production environment first.

Answer 2

It seems Magento did not clean the table when you have deleted some informations; so you need to clean it manually, using this SQL query:

TRUNCATE TABLE ´catalog_product_flat_1´;
Then, run reindex process.

It's okay to empty that table; since Magento uses EAV tables to rebiuld (reindex) it again.