Magento 2 reindex gives error Duplicate entry

Question

I am experiencing an issue which not sure why when I try to reindex it gives the error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 
'4458-142-1-1293-4458' for key 'PRIMARY', query was: INSERT INTO 
`catalog_product_index_eav_temp` 
(`entity_id`,`attribute_id`,`store_id`,`value`,`source_id`) VALUES (?, 
?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, 
?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, 
?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?).....

Now the problem is I don't see a table in the Database called catalog_product_index_eav_temp otherwise I would delete the duplicate entry and problem would be solved.

The line that is failing is with EAV:

php bin/magento indexer:reindex Design Config Grid index has been rebuilt successfully in 00:00:00 Customer Grid index has been rebuilt successfully in 00:00:00 Category Products index has been rebuilt successfully in 00:00:00 Product Categories index has been rebuilt successfully in 00:00:00 Product Price index has been rebuilt successfully in 00:00:07

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 
'4458-142-1-1293-4458' for key 'PRIMARY', query was: INSERT INTO 
`catalog_product_index_eav_temp` 
(`entity_id`,`attribute_id`,`store_id`,`value`,`source_id`) VALUES (?, 
?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, 
?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, 
?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?).....

Stock index has been rebuilt successfully in 00:00:00 Catalog Search index has been rebuilt successfully in 00:00:01 Catalog Rule Product index has been rebuilt successfully in 00:00:00 Catalog Product Rule index has been rebuilt successfully in 00:00:00

Does anyone know what could be wrong and how to fix! txs.

Answer 1

I have also face this issue,your error is Duplicate entry 4458-142-1-1293-4458 in your case the attribute id is 142, go to eav_attribute table and check 142 attrbute backend_type and entity_type_id, for example if in 142 id your attribute backend_type is varchar and entity_type_id is catalog_product, then go to the catalog_product_entity_varchar and filter 142 attribute_id rows there one row with duplicate value 4458 for example it may be (123,234,4458,4458), so you need to remove one time 4458 it should be (123,234,4458), it will fix. Thanks.

Answer 2

4458-142-1-1293-4458

Select * from [prefix]catalog_product_entity_varchar where attribute_id=142 and entity_id=4458

Remove duplicate values from value column and then reindex

Answer 3

After the upgrade of the Magento software we need to reindex the data using the standard method by shell:

php bin/magento indexer:reindex

If the reindex task produces the following error you'll need to fix the data in the product entity tables by this script.

Answer 4

So not sure how I ended up in the same situation, but for me the fix was to re-save from the admin panel the products where the entity_id was included in the error message: {entity_id}-{product attribute id}-{value 1}-{value 2}-{entity_id}.
I did not execute delete direct queries!

I leave this post here, maybe it will help someone else.

Cheers