2

The company I work for is having the below error when trying to re-index the 'catalog_product_attribute' index, with the indexer.

Error:


exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '13228-151-1-222' for key 'PRIMARY'' in /home/apeek22/public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/apeek22/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/apeek22/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/apeek22/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/apeek22/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/apeek22/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#5 /home/apeek22/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#6 /home/apeek22/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(1974): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...', Array)
#7 /home/apeek22/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Source.php(276): Varien_Db_Adapter_Pdo_Mysql->insertArray('catalog_product...', Array, Array)
#8 /home/apeek22/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Source.php(250): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source->_saveIndexData(Array)
#9 /home/apeek22/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Source.php(84): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source->_prepareMultiselectIndex(NULL, NULL)
#10 /home/apeek22/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(50): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source->_prepareIndex()
#11 /home/apeek22/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav.php(185): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract->reindexAll()
#12 /home/apeek22/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Eav->reindexAll()
#13 /home/apeek22/public_html/app/code/core/Mage/Index/Model/Process.php(209): Mage_Index_Model_Indexer_Abstract->reindexAll()
#14 /home/apeek22/public_html/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#15 /home/apeek22/public_html/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#16 /home/apeek22/public_html/shell/indexer.php(198): Mage_Shell_Compiler->run()
#17 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '13228-151-1-222' for key 'PRIMARY'' in /home/apeek22/public_html/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /home/apeek22/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/apeek22/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/apeek22/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home/apeek22/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#4 /home/apeek22/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#5 /home/apeek22/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(1974): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...', Array)
#6 /home/apeek22/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Source.php(276): Varien_Db_Adapter_Pdo_Mysql->insertArray('catalog_product...', Array, Array)
#7 /home/apeek22/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Source.php(250): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source->_saveIndexData(Array)
#8 /home/apeek22/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Source.php(84): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source->_prepareMultiselectIndex(NULL, NULL)
#9 /home/apeek22/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(50): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Source->_prepareIndex()
#10 /home/apeek22/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav.php(185): Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract->reindexAll()
#11 /home/apeek22/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Eav->reindexAll()
#12 /home/apeek22/public_html/app/code/core/Mage/Index/Model/Process.php(209): Mage_Index_Model_Indexer_Abstract->reindexAll()
#13 /home/apeek22/public_html/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#14 /home/apeek22/public_html/shell/indexer.php(158): Mage_Index_Model_Process->reindexEverything()
#15 /home/apeek22/public_html/shell/indexer.php(198): Mage_Shell_Compiler->run()
#16 {main}

I have tried at least 50 different solutions today alone. If somebody could please shed some light on a fix for this I would be greatly indebted to you.

5
  • Assuming this happens on every product and not just that 1 SKU? Have you looked at below thread? phpmysqltalk.com/… – MagentoMac Sep 16 '14 at 20:28
  • Assuming also you've checked you don't have a duplicate SKU? – MagentoMac Sep 16 '14 at 20:32
  • I actually have tried that thread, didn't work. Also I have found that there is multiple entries for entity_id (7092) in the catalog_product_eav and catalog_product_entity. Would that be the cause and would I have to delete every multiple entry for entity_id(7092)? – CodyW Sep 16 '14 at 20:43
  • That will be your issue. Every entity_id should be unique, and should automatically assign numerically in chronological order. I would certainly recommend deleting every duplicate entity. Backup first though. Delete from Magento admin, not from DB directly. – MagentoMac Sep 17 '14 at 19:03
  • Sounds like auto-increment and/or UNIQUE ID issue. See this link stackoverflow.com/questions/11781312/… – Tim Hallman May 22 '15 at 1:19
5

I experienced a similar issue, as a result of programmatically importing products.

The issue arose when trying to re-index the "Product Attributes" index (which consistently failed). The error message was almost exactly what you quoted above and was very difficult to isolate the root cause.

I discovered that for "multiselect" attributes, I was rather erroneously adding duplicate selections.

Consider the following example to explain the issue and how I resolved it...

Picture a "multiselect" attribute called "Writers", with three options as follows:

  1. Jimbob (ID 1)
  2. Mary (ID 2)
  3. Stephen (ID 3)

I was programmatically assigning selections using the code :

$product->setData("attribute_code",$values);

Where $values was an array of Ids. So for instance a book (product) written by Jimbob and Stephen would have an array $values of [1,3].

Somewhere along the line I was adding in values like [3,3].

This duplicate selection will not cause errors elsewhere in the system, but certainly will when it comes to indexing.

I suggest simply using the PHP method "array_unique" to prevent duplicates.

You might have some luck looking at the "catalog_product_entity_varchar" table for possible duplicates strings of IDs as described above (this is the table where product attribute multiselect selections are stored).

Perhaps this suggestion will help, I know it took me some considerable digging to find it.

2
  • 1
    Brilliant!! This explains a lot of things to me :) – Paras Sood Apr 16 '15 at 10:01
  • great details, can save lot of time for research! thx! – FireBear Jul 26 '16 at 7:44
0

I was facing the same issue, and here how I could resolve it :

You have to look in details where exactly on which line indexer was interrupted

just before that line you add

write ->exec("set foreign_key_checks=0");
....
$write->query($query);
...

and after it you add

write ->exec("set foreign_key_checks=1");

and voilà

3
  • It's what I call a real workaround. Don't do it at home kids! – medina Feb 1 '16 at 22:48
  • This is an old post, but I thought that was interesting you thought of this also. I as well did something similar to what you did above. But in my particular case that actually did not technically fix the issue, just rather allowed the database to have multiple unique keys and the indexer to function. I suppose that could not be an issue for certain situations, but in my particular case, it certainly did not. As by indexing all of the duplicate unique keys, when using a "Make, Model, Year" selector that my company required, it wouldn't return the correct products. – CodyW Oct 25 '16 at 20:21
  • I realized that the developer they have in Florida had been the one who made all of these errors in the first place. So this is what I did: Convince my boss that the other developer screwed up, Re-install Magento, and let me start it back up from scratch (Of course with his invited step-by-step feedback, questions, and approvals). What resulted was a working indexer, working "Make, Model, Year" selector, happy boss, job security, enemy in Florida. – CodyW Oct 25 '16 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.