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?

score 5 · Answer 1 · answered Aug 02 '12 at 19:58

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

Man you rock I was looking for DAYS for this fix. I have tried everything under the sun including trunicating core_url table and a slew of others. Were you ever able to find the issues of the source? Thanks a mill! — Frank G., Oct 02 '15 at 14:23

score 3 · Answer 2 · answered May 02 '14 at 14:50

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!

score 2 · Answer 3 · answered Aug 02 '12 at 17:21

Working the first time, but failing on subsequent reindexes seems a little unusual. Are you sure that you're using InnoDB and that your MySQL settings are correct? I would check to make sure your innodb cache is large enough, and see if MySQL itself is spitting out any kinds of errors at all. Magento takes advantage of InnoDB's transactional queries, and if MySQL is running out of memory or space to store the prepared queries from a large transaction such as this, you may be encountering issues.

The error you're getting is misleading, because a duplicate key would be caught on the first reindex, when the table is first built. If this were a simple data issue, such as a duplicate SKU or improper hierarchy of categories, then the index would fail on the first attempt.

You might also want to make sure that the MySQL user for your application has sufficient permissions to flush the table if necessary. Magento will want to nuke the table; if it can't, it may try to rebuild anyway, resulting in a duplicate key error.

I would also try to tail your exception.log file and see if you can get a stack trace and post it on here. Also, try enabling debug logging for the MySQL library (you could just change the default value of $_debug to true on line 103 of /lib/Varien/Db/Adapter/Pdo/Mysql.php -- you may also wish to change $_logAllQueries and $_logCallStack to true as well). Look in $_debugFile to find the path to tail.

Georges (my colleague) found a workaround but we still don't understand what caused the problem. — user1023021, Aug 02 '12 at 20:05
I'm having the same issue. The stack trace includes as OP described followed by similar to [link](http://stackoverflow.com/q/8940794/3376305). When I clear core_url_rewrite table and reindex once it works and second time it squeaks. The duplicate entry is different each time but always ends with -0-1. What MySql settings would you recommend? I'm running a local xampp with about a hundred products? — tzvi, May 14 '15 at 20:07

score 1 · Answer 4 · answered Sep 02 '12 at 14:04

I found that the db user had the correct permissions and I am not a fan of changing core code regardless of if it is moved to local.

The error was experienced following a command line reindex after an upgrade from 1.4 to 1.6.

php indexer.php -reindex catalog_url
An error occurred while saving the URL rewrite

The was paired with the following Exception in the log:-

exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'category/360-1-6' for key 'UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID'' in httpdocs/lib/Zend/Db/Statement/Pdo.php:228

I ran the following query via mysql to locate the problem record (note how the query relates to the Exception), it transpired this related to a category that had been renamed...

QUERY: select * from core_url_rewrite where id_path =  "category/360";

I note that this is a category url rewrite and that the product_id record is 0 as opposed to NULL, I suspect this is the issue and if you are having this problem it would be interesting to see if this is the same for you, run the following:

QUERY: select * from core_url_rewrite where product_id=0;

When I ran the above query it returned one record the problem record.

The solution I found was to delete the problem record...

Now catalog_url reindex works with out an issue.

Running the below query returns a new record with product_id of NULL and represents the new URI path. The old URI results in a 404 as opposed to redirecting as it should do.

QUERY: select * from core_url_rewrite where id_path =  "category/360";

I believe this problem is a single data record issue, probably following an upgrade. I do not agree that changing the URL.php or truncating core_url_rewrite is correct.

I have since rebuilt the index for catalog_url twice and a third as part of a complete reindex. All returned: Catalog URL Rewrites index was rebuilt successfully

I have documented my findings your circumstances maybe different but this goes a little way to explaining how you might locate the issue and fix it.

score 0 · Answer 5 · answered Aug 22 '14 at 20:54

Don't know what caused this in our site, but the fix was simple. If you do not care about old URL reweites (as in, your product URLs have always been what they are right now, or you dont care to loose the history) truncate the core_url_rewrite table, then reindex.

score 0 · Answer 6 · answered Nov 05 '16 at 04:39

0

The easiest solution for this is to delete those records from core_url_rewrite table and reindex again

answered Nov 05 '16 at 04:39

Suraj

363
2
16

Constraint violation / Duplicate key when reindexing Magento

6 Answers6