Updating SKU's from a CSV in Magento - Integrity constraint violation: 1062 error

Question

I have the code below to update a list of CSV's from an old value to a new value. However it triggers an error for some products:

Cannot retrieve products from Magento: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '168-122-1-7' for key 1

    include_once '../app/Mage.php';
    Mage::app();

    $updates_file="/home/varsityk/public_html/csvs/sku2sku.csv";

    $sku_entry=array();

    $updates_handle=fopen($updates_file, 'r');
    if($updates_handle) { 
        while($sku_entry=fgetcsv($updates_handle, 1000, ",")) { 
            $old_sku=$sku_entry[0];
            $new_sku=$sku_entry[1];
            echo "<br>Updating ".$old_sku." to ".$new_sku." - ";
            try {
                $get_item = Mage::getModel('catalog/product')->loadByAttribute('sku', $old_sku);

                if ($get_item) {
                    $get_item->setSku($new_sku)->save();
                    echo "successful";
                } else {
                    echo "item not found";
                }
            } catch (Exception $e) { 
                echo "Cannot retrieve products from Magento: ".$e->getMessage()."<br>";
                return;
            }
        }
    }

    fclose($updates_handle);

Alan Storm Alan Storm · Accepted Answer · 2012-01-09T17:00:01

There's not enough information in your post to accurately track this down. When you save product information in Magento there's multiple tables whose information will need to be updated. One of these tables has a four column unique key that, for some reason, your system is trying to update with information that would cause the four columns in that key to be a duplicate of another row

Integrity constraint violation: 1062 Duplicate entry '168-122-1-7' for key 1

You need to figure out which table this is, and then figure out why Magento might be bailing on this update. The quickest way to do this is add some looking to

lib/Zend/Db/Adapter/Abstract.php

This is the class that most database queries are routed through in Magento. Add your logging to the update and insert methods. Look for the spot where the SQL strings are created

    $sql = "UPDATE "
         . $this->quoteIdentifier($table, true)
         . ' SET ' . implode(', ', $set)
         . (($where) ? " WHERE $where" : '');


    $sql = "INSERT INTO "
         . $this->quoteIdentifier($table, true)
         . ' (' . implode(', ', $cols) . ') '
         . 'VALUES (' . implode(', ', $vals) . ')';

With this logging in place, you'll know the last table Magento was trying to insert or update before throwing that exception, which should give you the information you need to troubleshoot.

Updating SKU's from a CSV in Magento - Integrity constraint violation: 1062 error

2 Answers