[SOLVED] Magento reindex catalog product flat error: Error in foreign key constraint


I have an error when I try to reindex Magento 1.7 catalog product flat index (through admin panel or command line).

On the command line I get the following error:

Product Flat Data index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1005
Can't create table 'databasename.#sql-340a_22a3' (errno: 121)' in

When I look in mysql using ‘SHOW ENGINE INNODB STATUS;‘, I get the following error:

160830 10:17:09 Error in foreign key constraint creation for table
A foreign key constraint of name 
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

Any idea how to fix this?


Disable foreign_key_checks MySQL option before reindex

SET foreign_key_checks = 0;
// job
SET foreign_key_checks = 1;

Answered By – Nolwennig

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *