0

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
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.

  • I know the Foreign Key is missing. The tool won't be of many help since the query won't be executed anyway. I just won't understand why is MySQL telling me there is already a foreign key with that name, when a select to the table_constraints returns no results. – Filipe Sá Feb 3 '15 at 19:49
  • My theory here is that this is a cascading failure from problems with other keys. It doesn't take long to run, and it's a worthwhile shot. I have a very similar problem with the indexer trying to create a temporary table, and this fixed the issue. Give it a try. – Tyler V. Feb 3 '15 at 19:51
  • Nothing happened because the table don't exist. Magento can't create it. – Filipe Sá Feb 3 '15 at 21:30
  • Is there any chance that when you're reindexing, the same index is running twice in one request? – Tyler V. Feb 4 '15 at 0:06
  • V I'm using magento built-in indexer. I've 6 store views, 4 tables are created just fine, but when magento tries to create the table with ID 1 and the FK FK_MG_MG_CAT_PRD_FLAT_1_ENTT_ID_MG_CAT_PRD_ENTT_ENTT_ID it shows the error above . What I can't understand is why. There is no other FK in the db with the same name. – Filipe Sá Feb 4 '15 at 0:16
1

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.

  • This is not an error when Magento tries to add/delete/update data on the table. This happens when the Foreign Key is being added to the table schema. – Filipe Sá Feb 3 '15 at 19:18
  • Perhaps dropping the table entirely would be better if you have key/constraint name conflicts between the tmp_indexer table and the main flat table. The temporary table should create all the data then replace the flat table afterwards - possible something went wrong in the process and its now stuck. – Robbie Averill Sep 30 '15 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.