Last few weeks I was fighting with problem that partially broke Magento url indexer. Basically the url indexer was working but at certain point it was breaking. At this point I was getting exception and not all url rewrites were written to core_url_rewrite table. Everyday the problem was getting bigger and bigger because all new products contained “catalog/product/view/id” (I call those urls – “ugly” urls) but the desired urls suppose to be human readable and end with “.html”.
This issue could be reproduced only on live environment and in order to reproduce it locally I had to copy all catalog tables (catalog_*) and core_url_rewrite table from live to local environment.
When I run Magento url indexer from command line I got:
php shell/indexer.php --reindex catalog_url An error occurred while saving the URL rewrite
In Magento exception log I had:
exception 'PDOException' with message 'SQLSTATE: Integrity constraint violation: 1062 Duplicate entry 'product/2469/361-1-1' for key 'UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID''