SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry - Magento 2

Question

I'm trying to setup my crons for Magento 2.2.2, but nothing seems to be updating.

I have created cron in cPanel, like below, each once per minute:

/usr/local/bin/php /home/userishere/public_html/bin/magento cron:run | awk '{ print strftime("\%Y-\%m-\%d \%H:\%M:\%S"), $0; fflush(); }' 2>&1 >> ~/magento_cron.log

/usr/local/bin/php /home/userishere/public_html/update/cron.php | awk '{ print strftime("\%Y-\%m-\%d \%H:\%M:\%S"), $0; fflush(); }' 2>&1 >> ~/magento_cron.log

/usr/local/bin/php /home/userishere/public_html/bin/magento setup:cron:run | awk '{ print strftime("\%Y-\%m-\%d \%H:\%M:\%S"), $0; fflush(); }' 2>&1 >> ~/magento_cron.log

I check the scheduale in PhpMyAdmin to see this:

Error:

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

Any ideas where I'm going wrong? If you need more info please let me know.

Answer 1

Entry key '4639-168-1-283-4639', where 4639 is product_id, 168 is attribute_id, 1 is store_id, 283 is attribute_value_id for which is getting this issue.

So if suppose attribute 168 type is varchar, then its values will be in catalog_product_entity_varchar.

Now search for attribute 168 along with entity_id 4639, you can see there is some values in 'value' column.

Edit that row, you will see there is more than 1 entry (comma separated) for same value_id (here 283), just remove all other duplicates values from there and save it.

Flush cache and re-run index command.

If error comes again then again (that may come for other products as well) check and fix in similar way.

Not sure why magento puts duplicate values for multi-select.

Answer 2

maybe too late but here is a fix in indexer process of Magento, that will fix your problem (not the cause, but the "aftermath").

Method: \Magento\Catalog\Model\ResourceModel\Product\Indexer\Eav\Source::saveDataFromSelect

Added array_unique for $values.

    private function saveDataFromSelect(\Magento\Framework\DB\Select $select, array $options)
{
    $i = 0;
    $data = [];
    $query = $select->query();
    while ($row = $query->fetch()) {
        $values = explode(',', $row['value']);
        // FIX START - multiselect attributes contains duplicated values;
        $values = array_unique($values);
        // FIX END
        foreach ($values as $valueId) {
            if (isset($options[$row['attribute_id']][$valueId])) {
                $data[] = [$row['entity_id'], $row['attribute_id'], $row['store_id'], $valueId, $row['source_id']];
                $i++;
                if ($i % 10000 == 0) {
                    $this->_saveIndexData($data);
                    $data = [];
                }
            }
        }
    }

    $this->_saveIndexData($data);
}

And yep, I still do not know why these duplicated values are placed/saved in Magento.

Answer 3

I had the same problem with another message, I got:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'default-0-amasty_checkout/layout_builder/layout_builder_config' for key 'CORE_CONFIG_DATA_SCOPE_SCOPE_ID_PATH', query was: INSERT INTO `core_config_data` (`scope`, `scope_id`, `path`, `value`) VALUES (?, ?, ?, ?)

So I opened phpmyadmin and executed the following query

SELECT * FROM `core_config_data` WHERE path LIKE "%amasty_checkout%"

Then I deleted all entries. But be careful, this will delete all your settings from this extension.

Then I executed php bin/magento setup:upgrade again and it worked.

You can also search for the duplicate one and delete it, but I have to reconfigure the extension anyways so I just deleted all.