2

I have problem with add FOREIGN KEY to table catalog_product_flat_8 - Magento 1.9

When I try reindexing, I get:

Product Flat Data index process unknown error:
Exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1022 Can't write; duplicate key in table '#sql-4c36_d67'

I try add in MySQL

ALTER TABLE catalog_product_flat_8
ADD CONSTRAINT FK_CAT_PRD_FLAT_8_ENTT_ID_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (entity_id) REFERENCES catalog_product_entity (entity_id) ON DELETE CASCADE ON UPDATE CASCADE;

Still

1022 Can't write; duplicate key in table '#sql-4c36_1099

I check in information_schema

SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = "magento_db" AND CONSTRAINT_NAME LIKE "FK_CAT_PRD_FLAT_8_ENTT_ID_CAT_PRD_ENTT_ENTT_ID"

but 0 records

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, 
REFERENCED_TABLE_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'catalog_product_flat_8';

I have only PRIMARY KEY for entity_id.

So, I check:

select *
from information_schema.table_constraints
where constraint_schema = 'magento_db'

Only 1 record with "flat_8" for entity_id (PRIMARY),

And I search "FK_CAT_PRD_FLAT_8_ENTT_ID_CAT_PRD_ENTT_ENTT_ID" in every tables from information_schema

Results:

`information_schema`.`INNODB_SYS_FOREIGN`

| ID                                                    |FOR_NAME                   |REF_NAME                           | N_COLS | TYPE |
| magento_db/FK_CAT_PRD_FLAT_8_ENTT_ID_CAT_PRD_ENTT_E... |magento_db/#sql-6756_19e0f | magento_db/catalog_product_entity | 1      | 5    |






`information_schema`.`INNODB_SYS_FOREIGN_COLS`

| ID                                                    | FOR_COL_NAME| REF_COL_NAME | POS |
| magento_db/FK_CAT_PRD_FLAT_8_ENTT_ID_CAT_PRD_ENTT_E...    | entity_id   | entity_id    |  0  |

I have not idea, please help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
lukas_osw
  • 29
  • 1

1 Answers1

1

I currenlty have the same problem. The key is in the INNODB_SYS_FOREIGN_COLS but is missing in TABLE_CONSTRAINTS. For this reason one obviously cannot alter table to drop the key.

The only solution i came up with is to dump, drop and import the database.

Vladimir Samsonov
  • 1,344
  • 2
  • 11
  • 18