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

score 1 · Answer 1 · edited Feb 05 '15 at 20:47

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.

edited Feb 05 '15 at 20:47

Marius

197,939
53
422
830

answered Feb 03 '15 at 19:14

mrumair.com

1,365
12
13

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 03 '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. – scrowler Sep 30 '15 at 21:40

score 1 · Answer 2 · answered Feb 03 '15 at 19:31

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.

answered Feb 03 '15 at 19:31

Tyler V.

2,987
2
27
50

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 03 '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 03 '15 at 19:51
Nothing happened because the table don't exist. Magento can't create it. – Filipe Sá Feb 03 '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 04 '15 at 00: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 04 '15 at 00:16
The only thing I can think of is that 1) You missed that key while looking at other tables for it, or 2) That code is being run more than once during a single MySQL connection and the temporary table is being created twice, and the second time you get this error. My suggestion is to find the PHP that's creating the table, and add Mage::log('Create tmp table', null, 'system.log', true); and see if it hits that too many times. – Tyler V. Feb 04 '15 at 00:40
@ Tyler V. Check and Check. If I try to manually run the query I get the same error. I used the query in the main question to make sure there are no other FK's with the same name. This really makes no sense. – Filipe Sá Feb 04 '15 at 01:50
Can you post the output of that table_constraints query? --- In the Magento site that I tested it on, I quickly found that my copy of catalog_product_flat_1 has FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID and there is no index currently running. – Tyler V. Feb 04 '15 at 01:55
@ Tyler V. There are 960 records. The most important ones:
NAME TABLE
FK_mg_INDEX_PROCESS_EVENT_EVENT_ID_mg_INDEX_EVENT_EVENT_ID mg_index_process_event
FK_MG_MG_CAT_PRD_FLAT_2_ENTT_ID_MG_CAT_PRD_ENTT_ENTT_ID mg_catalog_product_flat_2
FK_MG_MG_CAT_PRD_FLAT_3_ENTT_ID_MG_CAT_PRD_ENTT_ENTT_ID mg_catalog_product_flat_3
FK_MG_MG_CAT_PRD_FLAT_4_ENTT_ID_MG_CAT_PRD_ENTT_ENTT_ID mg_catalog_product_flat_4
Others tables FK's are created without any issue.
– Filipe Sá Feb 04 '15 at 02:05

Can't reindex product flat data

2 Answers2