Catalog URL Rewrites Errors Please Help

[Frank G]

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.

[Kingdom Inc]

Hello,

This is how I would attempt to diagnose this problem.

1) Understand the problem.

The problem is that the indexer is attempting to insert two or more rows into your `core_url_rewrite` that violate a data integrity constraint.  If you view the table structure, you will find that the unique constraint "UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID" states that the no duplicate records should exist whose values match for the following columns: `id_path`, `is_system`, and `store_id`.  This means that if the indexer were to finish its work without error, then the following query should return one and only one result (replace X, Y, and Z with arbitrary data):

SELECT * FROM magento.core_url_rewrite WHERE `id_path` = X AND `is_system` = Y AND `store_id` = Z;

2) Write the bad data.

Create a COPY of your database (we will throw this database away after testing).  Run the following query on your COPIED database to remove the constraint:

DROP index `UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID` FROM TABLE `yourdatabase`.`core_url_rewrite`;

Run your indexer process and let it finish successfully.  If that went well, you should now have a good clue as to where the bad data resides.

3) Find the bad data.

Now that the bad data is in the table ripe for the picking, we need to find it.  The following query should help us do that:

SELECT
         `store_id`,
         `is_system`,
         `id_path`,
         count(*)
FROM     `core_url_rewrite`
GROUP BY `store_id`,
         `is_system`,
         `id_path`
HAVING count(*) > 1;

4) Investigate the suspect data.

You should now have `id_path` to investigate (which you can associate with another table).  Odds are, there's something wrong with one of the sources that the indexer is pulling from.

5) Remove / Modify the bad data.

That's what I would do, your mileage may vary.

Good luck!