Product Attributes could not be reindexed

Question

I am recieving this error while trying to reindex "Product Attributes". Can anyone suggest, what might be the issue and possible solution

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2828-148-1-471' for key 'PRIMARY'' in E:\xampp\htdocs\magento\lib\Zend\Db\Statement\Pdo.php:228 Stack trace: #0 E:\xampp\htdocs\magento\lib\Zend\Db\Statement\Pdo.php(228): PDOStatement->execute(Array) #1 E:\xampp\htdocs\magento\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) #2 E:\xampp\htdocs\magento\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array) #3 E:\xampp\htdocs\magento\lib\Zend\Db\Adapter\Abstract.php(480): Zend_Db_Statement->execute(Array) #4 E:\xampp\htdocs\magento\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO ca...', Array) #5 E:\xampp\htdocs\magento\lib\Varien\Db\Adapter\Pdo\Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTOca...', Array) #6 E:\xampp\htdocs\magento\lib\Varien\Db\Adapter\Pdo\Mysql.php(1998): Varien_Db_Adapter_Pdo_Mysql in E:\xampp\htdocs\magento\lib\Zend\Db\Statement\Pdo.php on line 234

Answer 1

How to interpret the error message

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2828-148-1-471' 
for key 'PRIMARY

In the case of the product attribute indexer, this means there's a constraint error while saving a record to the catalog_product_index_eav_idx table.

2828-148-1-471 refers to:

• product entity id: 2828
• attribute id: 148
• store id: 1
• value: 471

Possible origin of the problem

I'm assuming that the attribute that's causing problems is of type multiselect. A value which is linked multiple times to a product, will cause this type of error.

You can check this by executing the following query:

SELECT * FROM `catalog_product_entity_varchar` 
where entity_id = 2828 and attribute_id = 148 and store_id = 1;

I'm guessing that the id 471 will be listed multiple times in the column value. Every id should only be listed once in that column.

Are you using an importer to add product data to Magento?

Answer 2

Below solution worked for me and may help others :

2828-148-1-471 refers to:

product entity id: 2828 attribute id: 148 store id: 1 value: 471

Just open product with id 2828 and save it and reindex again.

that may solve your problem

Answer 3

I got it, it was an issue with the multi select dropdown attributes, In my script, sometimes same attribute was being added multiple times which was causing error, posting this answer for someone if need help in future