Constraint violation / Duplicate key when reindexing Magento

Question

I am using Magento CE 1.6.2 and I have a problem with my reindexer ( the url_rewrite )

php shell/indexer.php --reindex catalog_url
Catalog URL Rewrites index process unknown error:
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '33432700_1343855802-0-1' for key 'UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID'' in /home/website/public_html/lib/Zend/Db/Statement/Pdo.php:228

When I truncate the core_url_rewrite... and hit the indexer via the backend for the first time, everything is fine, and my url rewrites are stored in the core_url_rewrites... But if I start the indexer a second time (without flushing the table), I get an error of duplicate key.

Here is a screen shot of my table: https://www.dropbox.com/s/6v9uawp5v437w3h/seo_Magewroks.png

note: UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID is an index key

How can I find the source of the problem?

Answer 1

This should fix the problem,

Copy the core file: /app/code/core/Mage/Catalog/Model/Resource/Url.php To: /app/code/local/Mage/Catalog/Model/Resource/Url.php

find this function:

public function saveRewriteHistory($rewriteData)
{
    $rewriteData = new Varien_Object($rewriteData);
    // check if rewrite exists with save request_path
    $rewrite = $this->getRewriteByRequestPath($rewriteData->getRequestPath(), $rewriteData->getStoreId());

    if ($rewrite === false) {
        // create permanent redirect
        $this->_getWriteAdapter()->insert($this->getMainTable(), $rewriteData->getData());
    }

    return $this;

}

replace it with:

protected $_processedRewrites = array();   // add this to your class vars on top

public function saveRewriteHistory($rewriteData)
{
    $rewriteData = new Varien_Object($rewriteData);
    // check if rewrite exists with save request_path
    $rewrite = $this->getRewriteByRequestPath($rewriteData->getRequestPath(), $rewriteData->getStoreId());
    $data = $rewriteData->getData();

    $current = $data["id_path"]."_".$data["is_system"]."_".$data["store_id"];
    if ($rewrite === false && !in_array($current, $this->_processedRewrites)) {
        $this->_processedRewrites[] = $current;
        // create permanent redirect
        $this->_getWriteAdapter()->insert($this->getMainTable(), $rewriteData->getData());
    }

    return $this;
}

The problem is because the function check's the DB to see if the rewrite exists in core_url_rewrites before inserting it. And this is fine. But it does the check with the following attributes: request_path, is_system, store_id

Our problem was that some rows had duplicated id_path but with different request_path... it's weird, not sure why it is not supposed to..

But with this replacement function it will also check if the id_path was processed before, if yes it wont insert it. It solves the problem..

But still, we dont know the source of the problem