Magento 2 - Reindexing generates SQLSTATE[23000]: Integrity constraint violation

0
votes
  • I imported products through the CSV import feature.
  • The import went without any issues.
  • All imported products are listed in admin panel.
  • the problem is with indexing catalog_product_attribute only. The rest indexes reindex ok.

When I tried reindex the index (in console) to make them visible in the frontend store, I got SQLSTATE[23000]: Integrity constraint violation error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8942-138-1-265' for key 'PRIMARY', query was: INSERT INTO mag_catalog_product_index_eav_idx (entity_id,attribute_id,store_id,value) VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?).....

Please advice me what shall I do? Thanks!

3
magentoindexingmagento2
Do you import at least into 2 stores? The attribute with id= 138 is a multiselect? – Detzler
I have the same problem. Only one store and in my case the attribute is indeed a multiselect. @Pete-jaworski: did you ever solve this? – Giel Berkers
Did you find a solution for this issue? – Corgalore

3 Answers

2
votes

I found the problem in my case. My attribute was of type multiselect. This means that Magento will split the string apart into separate ID's and create unique keys in the index-table for each option.

However... If you have duplicate values in your database (due to a failed import or some other reason), this error will be thrown.

In my case the value in my database was 227,15,227, which has the value 227 2 times, throwing the above error. Changing the value to 227,15 solved this issue for me.

1
votes

Problem description : Issue is with multiselect filterable attributes only. Suppose if we save single value from multiselect options for pert. SKU then in catalog_product_entity_varchar table value stored with comma separate(because as per multselect behaviour Magento storing data with comma separate).

Solution : Create cron file through which get all values of that attribute from catalog_product_entity_varchar table and then using PHP explode using (,) comma and then make unique array and again update same value using primary_key.

Working for me.

0
votes

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.