3

I have tried everything possiable. I upgraded my Magento from 1.7.X to 1.9.X then just upgraded to Magento 1.9.2. Over the last couple years. Going from 1.7 to 1.9 had no problems. All is well however I am getting an error when I try to reindex my Catalog URL Rewrites. Here is what I tried to do with NO success:

System Specs: - Windows Server 2008 - Magento Version 1.9.2 - PHP 5.5

What I have Tried: - I tried Trunicating my core_url_rewrite table - I cleared out my magento Cache both in backend and on the server. - I looked for the Var/Locks folder but my Magento didn't create the folder so I couldn't delete it. I thought maybe it would be a permissions issues so I gave the entire folder of Magento "Everyone" Full control permissions and tried reindexing again. Still errors. - I logged in to my server using SSH and ran "php indexer.php -reindexall" and "php indexer.php -reindex catalog_url" it indexes everything but the core url. It does show a bunch of stuff on the screen which I don't understand when it does try the to reindex.

The Returned Error:

C:\mydomain\shell>php indexer.php -reindex catalog_url

Catalog URL Rewrites index process unknown error:
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '35270000_1443190673-
0-1' for key 'UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID'' in C:\mydomain\lib\Zend\Db\St
atement\Pdo.php:228

Stack trace:

#0 C:\mydomain\lib\Zend\Db\Statement\Pdo.php(228): PDOStatement->execute(Array)
#1 C:\mydomain\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 C:\mydomain\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 C:\mydomain\lib\Zend\Db\Adapter\Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 C:\mydomain\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `co...', Array)
#5 C:\mydomain\lib\Varien\Db\Adapter\Pdo\Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `co...', Array)
#6 C:\mydomain\lib\Zend\Db\Adapter\Abstract.php(576): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `co...', Array)
#7 C:\mydomain\app\code\core\Mage\Catalog\Model\Resource\Url.php(338): Zend_Db_Adapter_Abstract->insert('core_url_rewrit...', Array)
#8 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(963): Mage_Catalog_Model_Resource_Url->saveRewriteHistory(Array)
#9 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(369): Mage_Catalog_Model_Url->_saveRewriteHistory(Array, Object(Varien_Object))
#10 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(568): Mage_Catalog_Model_Url->_refreshProductRewrite(Object(Varien_Object), Object(Varien_Object))
#11 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(255): Mage_Catalog_Model_Url->refreshProductRewrites('1')
#12 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(248): Mage_Catalog_Model_Url->refreshRewrites('1')
#13 C:\mydomain\app\code\core\Mage\Catalog\Model\Indexer\Url.php(257): Mage_Catalog_Model_Url->refreshRewrites()
#14 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(212): Mage_Catalog_Model_Indexer_Url->reindexAll()
#15 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(260): Mage_Index_Model_Process->reindexAll()
#16 C:\mydomain\shell\indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#17 C:\mydomain\shell\indexer.php(216): Mage_Shell_Compiler->run()
#18 {main}



Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '
35270000_1443190673-0-1' for key 'UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID', query was: INSERT INTO `core_url_rewrite` (`st
ore_id`, `category_id`, `product_id`, `id_path`, `request_path`, `target_path`, `is_system`, `options`) VALUES (?, ?, ?, ?, ?, ?,?, ?)' in C:\mydomain\lib\Zend\Db\Statement\Pdo.php:235 
Stack trace:
#0 C:\mydomain\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:\mydomain\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 C:\mydomain\lib\Zend\Db\Adapter\Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 C:\mydomain\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `co...', Array)
#4 C:\mydomain\lib\Varien\Db\Adapter\Pdo\Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `co...', Array)
#5 C:\mydomain\lib\Zend\Db\Adapter\Abstract.php(576): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `co...', Array)
#6 C:\mydomain\app\code\core\Mage\Catalog\Model\Resource\Url.php(338): Zend_Db_Adapter_Abstract->insert('core_url_rewrit...', Array)
#7 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(963): Mage_Catalog_Model_Resource_Url->saveRewriteHistory(Array)
#8 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(369): Mage_Catalog_Model_Url->_saveRewriteHistory(Array, Object(Varien_Object))
#9 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(568): Mage_Catalog_Model_Url->_refreshProductRewrite(Object(Varien_Object), Object(Varien_Object))
#10 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(255): Mage_Catalog_Model_Url->refreshProductRewrites('1')
#11 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(248): Mage_Catalog_Model_Url->refreshRewrites('1')
#12 C:\mydomain\app\code\core\Mage\Catalog\Model\Indexer\Url.php(257): Mage_Catalog_Model_Url->refreshRewrites()
#13 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(212): Mage_Catalog_Model_Indexer_Url->reindexAll()
#14 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(260): Mage_Index_Model_Process->reindexAll()
#15 C:\mydomain\shell\indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#16 C:\mydomain\shell\indexer.php(216): Mage_Shell_Compiler->run()
#17 {main}



C:\mydomain\shell>

I don't know what else to try or do can someone please help me? Am I open to any suggestions? Please let me know if you have any Questions?

  • take backup of your "core_url_rewrite" table and truncate it and try to re-index again. – Akhilesh Patel Sep 26 '15 at 2:42
  • It looks like it's failing on a custom rewrite. I would check the item that is causing your issue, save it manually in Magento, and try again. Also, disable all your custom modules if you have any, they probably don't help. – Mayers Sep 26 '15 at 10:09
  • I'm not sure how to check what item? In the backend of magento Catalog URL rewrites is in red. If I trunicate core_url table then reindex it works. But if I reindex again it breaks. Can you provide me more detail? – Frank G. Sep 29 '15 at 13:42
3

My hunch is that this is a result of truncating your core_url_rewrite table, which contains a mix of URLs--those generated by indexing, and those preserved for historical purposes.

The historical URLs likely are the culplrit. To resolve this it's likely best to restore that table from a backup, then delete (not truncate) only the indexed URLs via:

DELETE FROM core_url_rewrite WHERE is_system = 1;

At this point you should be able to rebuild the deleted URLs by re-indexing.

  • Hello STW, I tried the Delete command in SQL and that effected 728 rows. Then I reindexed and that worked fine until I reindexed again and it broke. Any other suggestions? – Frank G. Oct 2 '15 at 2:01
3

Thank you very much for your help. I found the answer myself. You can see the answer here its by Georges: https://stackoverflow.com/questions/11781312/constraint-violation-duplicate-key-when-reindexing-magento.

Here are the steps correct the issue:

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

NOTE: My setup didn't have these folders after app/code/local/Mage/ so I manually created Catalog/Model/Resource and copied the Url.php file to it. Hope this helps!!

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 don't know the source of the problem quite yet

  • Interesting, different solutions for the same problem :-) I was just writing my answer when you posted yours. Anyway, if you're interested you can also check out my answer. – Milan Simek Oct 2 '15 at 14:39
2

First of all make complete backup of you core_url_rewrite table or even whole data base before make any changes.

First step it's trying to find duplicated products SKU which can cause the problems by following SQL query

SELECT
    DISTINCT(`sku`) as `sku`,
    COUNT(`sku`) as `skuCount`,
    entity_id
FROM
    catalog_product_entity
GROUP BY
    `sku`
HAVING
    `skuCount` > 1;

If you find some duplicates by this query - try to change SKU for not get duplicates - this can resolve the issue.

Next advice is not so good but will resolve issue in any way - temporary disable check the unique keys by MySQL

SET foreign_key_checks = 0;

Then reindex and back it to 1.

You can find more advices on similar question, if you find which products cause issue - just manual re-save can help.

  • Hello FireBear, I have ran the query above and nothing returned. Then I ran the set foreign key to 0 then went to magento admin side and reindexed and it still errored "Cannot initialize the indexer process.". I then reset foreign key to 1 again and re indexed again and still errors. I also checked out your provided link for addition help. How do you know what product is causing the issue? Ben is saying to "Open Admin > Catalogue > Manage Products - filter the grid by product ID above - then edit the product." filter how? and what am I looking for the same numbers in two products? – Frank G. Oct 2 '15 at 13:30
1

Maybe the following will work:

Edit the method saveRewriteHistory in the file app/code/core/Mage/Catalog/Model/Resource/Url.php.

Change the following line:

$this->_getWriteAdapter()->insert($this->getMainTable(), $rewriteData->getData());

into:

$this->insert($this->getMainTable(), $rewriteData->getData());

Now add the following new method in the same file:

/**
 * Inserts a table row with specified data.
 *
 * @param mixed $table The table to insert data into.
 * @param array $bind Column-value pairs.
 * @return int The number of affected rows.
 */
public function insert($table, array $bind)
{

    $writeAdapter = $this->_getWriteAdapter();
    // extract and quote col names from the array keys
    $cols = array();
    $vals = array();
    foreach ($bind as $col => $val) {
        $cols[] = $writeAdapter->quoteIdentifier($col, true);
        if ($val instanceof Zend_Db_Expr) {
            $vals[] = $val->__toString();
            unset($bind[$col]);
        } else {
            $vals[] = '?';
        }
    }

    // build the statement
    $sql = "INSERT INTO "
        . $writeAdapter->quoteIdentifier($table, true)
        . ' (' . implode(', ', $cols) . ') '
        . 'VALUES (' . implode(', ', $vals) . ')';

    //add update in case if key already exists (this is the fix)
    $updateSql = array();
    foreach($cols as $k => $col){
        $updateSql[] = $col . '=' . $col;
    }
    $updateSql = implode(',',$updateSql);
    $updateSql = "
                  ON DUPLICATE KEY
                  UPDATE {$updateSql}";


    $sql .= $updateSql;

    // execute the statement and return the number of affected rows
    $stmt = $writeAdapter->query($sql, array_values($bind));
    $result = $stmt->rowCount();
    return $result;
}

I tested the functionality and it seems to work fine for a normal reindex, but of course I couldn't test if it solves your issue.

Please note that while I suggest some quick core edits for the purpose of testing this solution, I'd recommend to create a new module and rewrite the class if it actually works.

Hope this helps!

0

Probably you just have solved your problem, but I'm leaving the tip here to other people who came here from google like me.

This error also is produced when url_rewrite_id reaches max unsigned int size 4,294,967,295

The fastest solution is just truncate this table and reindex again if you are not using custom rewrites.

$adapter->insertOnDuplicate($this->getMainTable(), $rewriteData);

Seem like INSERT ... ON DUPLICATE UPDATE (used by Magento core indexer) bumps this index each time indexer is run.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.