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

Answer 2

Additional solution:-

When truncating the core_url_rewrite table you will loose all legacy url key change propagation records. I have discovered that deleting all records marked as "is_system" 1 will maintain these legacy url redirection, use query:-

DELETE FROM `core_url_rewrite` WHERE `is_system` = 1;

This has the same impact as truncating the table whilst maintaining the url change propagation or bespoke redirects you may have created.

As Alan Storm explains:-

The is_system property might be more accurately named is_canonical_rewrite_for_category_or_product_category_combo. That is, is_system, is a boolean flag that Magento sets to let itself know what rows are system level rewrites, created by Magento, and currently represent the “main” URL for a particular entity (as opposed to the redirection rewrites, which are also created by the system, but have their is_system flag set to false). Alan Storm - http://alanstorm.com/

Additionally if you can narrow the corruption to either product or category records you could use the following:-

Maintain category redirection:-

DELETE FROM `core_url_rewrite` WHERE `is_system` = 1 AND `category_id` IS NOT NULL;

Maintain product redirection:-

DELETE FROM `core_url_rewrite` WHERE `is_system` = 1 AND `product_id` IS NOT NULL;

Overall keep in mind that all records marked as is_system 1 were created by Magento from the Product or Category data and can be recreated. Records marked is_system 0 exist nowhere else and will be lost forever "dead ending" legacy links from other sites if you simply truncate core_url_rewrite.

Another issue that has an impact is the amount of URL redirections that Magneto is trying to create when it re-indexes. If you have a large amount of products using the following extension can speed up the index time and reduce the number of records in the core_url_rewrite table:- http://www.magentocommerce.com/magento-connect/dn-d-patch-index-url-1.html

The Dn'D Patch Index URL extension limits the URL index to only creating records for Enabled products that are Visible Individually. This works very well.

I hope this helps some!