Solved: Catalog URL Rewrites Error

Frank G · ‎09-25-2015

Hello All,

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

- Mageneto 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(Ar

ray)

#2 C:\mydomain\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_exec

ute(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('IN

SERT 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('INS

ERT INTO `co...', Array)

#7 C:\mydomain\app\code\core\Mage\Catalog\Model\Resource\Url.php(338): Zend_Db_Adapter_Abstra

ct->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->_saveRe

writeHistory(Array, Object(Varien_Object))

#10 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(568): Mage_Catalog_Model_Url->_refre

shProductRewrite(Object(Varien_Object), Object(Varien_Object))

#11 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(255): Mage_Catalog_Model_Url->refres

hProductRewrites('1')

#12 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(248): Mage_Catalog_Model_Url->refres

hRewrites('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_U

rl->reindexAll()

#15 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(260): Mage_Index_Model_Process->re

indexAll()

#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(Ar

ray)

#1 C:\mydomain\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_exec

ute(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('IN

SERT 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('INS

ERT INTO `co...', Array)

#6 C:\mydomain\app\code\core\Mage\Catalog\Model\Resource\Url.php(338): Zend_Db_Adapter_Abstra

ct->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->_saveRe

writeHistory(Array, Object(Varien_Object))

#9 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(568): Mage_Catalog_Model_Url->_refres

hProductRewrite(Object(Varien_Object), Object(Varien_Object))

#10 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(255): Mage_Catalog_Model_Url->refres

hProductRewrites('1')

#11 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(248): Mage_Catalog_Model_Url->refres

hRewrites('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_U

rl->reindexAll()

#14 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(260): Mage_Index_Model_Process->re

indexAll()

#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? I'm open to any suggestions? Please let me know if you have any Questions?

Thanks,

Frank G.

Frank G · ‎09-25-2015

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

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 with This:

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.

Hope this helps!!

View solution in original post

iweb_smartie · ‎09-25-2015

Hi Frank.

That error doesn't really tell us anything. Can you give us the entire stack trace please?

Problem solved? Click Accept as Solution!
www.iwebsolutions.co.uk | Magento Small Business Partner

Frank G · ‎09-25-2015

Hello Iweb,

Here is the full error when I run php indexer.php -reindex catalog_url

ERROR Returned:

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(Ar

ray)

#2 C:\mydomain\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_exec

ute(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('IN

SERT 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('INS

ERT INTO `co...', Array)

#7 C:\mydomain\app\code\core\Mage\Catalog\Model\Resource\Url.php(338): Zend_Db_Adapter_Abstra

ct->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->_saveRe

writeHistory(Array, Object(Varien_Object))

#10 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(568): Mage_Catalog_Model_Url->_refre

shProductRewrite(Object(Varien_Object), Object(Varien_Object))

#11 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(255): Mage_Catalog_Model_Url->refres

hProductRewrites('1')

#12 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(248): Mage_Catalog_Model_Url->refres

hRewrites('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_U

rl->reindexAll()

#15 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(260): Mage_Index_Model_Process->re

indexAll()

#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(Ar

ray)

#1 C:\mydomain\app\code\core\Zend\Db\Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_exec

ute(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('IN

SERT 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('INS

ERT INTO `co...', Array)

#6 C:\mydomain\app\code\core\Mage\Catalog\Model\Resource\Url.php(338): Zend_Db_Adapter_Abstra

ct->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->_saveRe

writeHistory(Array, Object(Varien_Object))

#9 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(568): Mage_Catalog_Model_Url->_refres

hProductRewrite(Object(Varien_Object), Object(Varien_Object))

#10 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(255): Mage_Catalog_Model_Url->refres

hProductRewrites('1')

#11 C:\mydomain\app\code\core\Mage\Catalog\Model\Url.php(248): Mage_Catalog_Model_Url->refres

hRewrites('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_U

rl->reindexAll()

#14 C:\mydomain\app\code\core\Mage\Index\Model\Process.php(260): Mage_Index_Model_Process->re

indexAll()

#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>

Hope this helps? Thanks again for the fast response!

Frank G · ‎09-25-2015

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

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 with This:

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.

Hope this helps!!

Catalog URL Rewrites Error

Catalog URL Rewrites Error

Re: Catalog URL Rewrites Error

Re: Catalog URL Rewrites Error

Re: Catalog URL Rewrites Error

Re: Catalog URL Rewrites Error