Then I had an idea I used phpmyadmin to truncate the core_url_rewrite table. Note I did not delete it I emptied the table that is I deleted all the rows but left the table structure intact.
Next I re-indexed from the command line again and it worked without errors. I tried re-indexing in the admin interface and again it worked without errors.
It appears to have fixed the problem
OMG after 2 days of searching this solution actually worked. I was a bit worried about emptying data out of my SQL db but after 2 days I was ready to try anything.
Used PHPMYADMIN, found the core_url_rewrite table, selected browse and used the truncate command (bottom right). Then went back into the magento admin and reindexed all.
WHOOOOOOHOOOOO it took a while but finished without error 500. Now I know its good I’m gonna back this mf up!
The new Zend security update screwed with my URL rewrites for some reason. However I was able to get my re-indexing back to being error-free by simply truncating the core_url_rewrite table based on this thread’s advice. Thanks for the help guys!
For what its worth, I ran into this problem after upgrading Magento v1.4 to v1.6.
Due to SEO reasons, I couldn’t truncate core_url_rewrite table.
Instead, I ran the following SQL query which fixed my problem:
UPDATE core_url_rewrite SET product_id = NULL WHERE id_path LIKE 'category%';
Some of the original categories (created under v1.4) had a blank/whitespace (not NULL) value under the product_id column in the table. This command manually set the product_id to NULL for any categories.
Good day. Today I met such an error when tried to reindex catalog URLs:
Fatal error: Call to undefined method Varien_Db_Adapter_Pdo_Mysql::getSubstringSql() in C:\Webserver\Apache\htdocs\default\app\code\core\Mage\Catalog\Model\Resource\Url.php on line 190
The function in Url.php is
public function getLastUsedRewriteRequestIncrement($prefix, $suffix, $storeId) { $adapter = $this->_getWriteAdapter(); $requestPathField = new Zend_Db_Expr($adapter->quoteIdentifier('request_path')); //select increment part of request path and cast expression to integer $urlIncrementPartExpression = Mage::getResourceHelper('eav') ->getCastToInt($adapter->getSubstringSql( $requestPathField, strlen($prefix) + 1, $adapter->getLengthSql($requestPathField) . ' - ' . strlen($prefix) . ' - ' . strlen($suffix) ));
I made a search in sources of a whole site - there is really not a single sign of a getSubstringSql function, neither in Varien_Db_Adapter_Pdo_Mysql, nor in other files. One day before it worked correctly. I didn’t change indexer.php or any of it’s includes, just added some more products in catalog. What can be the source of this problem? Thank you.
One other possible cause of the core_url_rewrite table having issues is from a third party product import tool improperly injecting data into the database. Take a look at this thread:
http://www.magentocommerce.com/boards/v/viewthread/260663/
Try manually reinitiating the indexing process by accessing your Magento’s root folder via FTP and deleting all files under var/locks. Before that make sure you also have all caches fully flushed and disabled and make sure you’re logged out of the admin as well.
The next time you login that should initiate the indexing – if it doesn’t automatically just go to System > Index Management and reindex all data.
