Magento Fixed: An error occurred while saving the URL rewrite

I recently upgraded magento to 1.6.2.0 and had some major issues with SEF URLS and Re-indexing URL Rewrites

• After the upgrade I found that all search friendly URLs and product structures were MIA
• When i saved products and entries there was a massive delay / lag
• Re-indexing URL Rewrites failed with the error “An error occurred while saving the URL rewrite”

Technical Diagnosis

[b]When i attempt to reindex via shell i would get[/b]

[~/www/shell]# php indexer.php reindexall
Product Attributes index was rebuilt successfully
Product Prices index was rebuilt successfully
An error occurred while saving the URL rewrite
Product Flat Data index was rebuilt successfully
Category Flat Data index was rebuilt successfully
Category Products index was rebuilt successfully
Catalog Search Index index was rebuilt successfully
Stock Status index was rebuilt successfully
Tag Aggregation Data index was rebuilt successfully

If i tried to re-index via the interface i get the error message An error occurred while saving the URL rewrite

I enabled logging and got this

2012-02-15T09:18:37+00:00 ERR (3):
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'product_id' cannot be null' in /home/site/public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/site/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/site/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/site/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/site/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/site/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `co...', Array)
#5 /home/site/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(389): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `co...', Array)
#6 /home/site/public_html/lib/Zend/Db/Adapter/Abstract.php(574): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `co...', Array)
#7 /home/site/public_html/app/code/core/Mage/Catalog/Model/Resource/Url.php(283): Zend_Db_Adapter_Abstract->insert('core_url_rewrit...', Array)
#8 /home/site/public_html/app/code/core/Mage/Catalog/Model/Url.php(290): Mage_Catalog_Model_Resource_Url->saveRewrite(Array, NULL)
#9 /home/site/public_html/app/code/core/Mage/Catalog/Model/Url.php(317): Mage_Catalog_Model_Url->_refreshCategoryRewrites(Object(Varien_Object), '/', false)
#10 /home/site/public_html/app/code/core/Mage/Catalog/Model/Url.php(458): Mage_Catalog_Model_Url->_refreshCategoryRewrites(Object(Varien_Object), NULL, false)
#11 /home/site/public_html/app/code/core/Mage/Catalog/Model/Url.php(252): Mage_Catalog_Model_Url->refreshCategoryRewrite('2', '1', false)
#12 /home/site/public_html/app/code/core/Mage/Catalog/Model/Url.php(246): Mage_Catalog_Model_Url->refreshRewrites('1')
#13 /home/site/public_html/app/code/core/Mage/Catalog/Model/Indexer/Url.php(256): Mage_Catalog_Model_Url->refreshRewrites()
#14 /home/site/public_html/app/code/core/Mage/Index/Model/Process.php(207): Mage_Catalog_Model_Indexer_Url->reindexAll()
#15 /home/site/public_html/app/code/core/Mage/Index/Model/Process.php(253): Mage_Index_Model_Process->reindexAll()
#16 /home/site/public_html/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#17 /home/site/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(420): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#18 /home/site/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('reindexProcess')
#19 /home/site/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#20 /home/site/public_html/app/code/core/Mage/Core/Model/App.php(349): Mage_Core_Controller_Varien_Front->dispatch()
#21 /home/site/public_html/app/Mage.php(640): Mage_Core_Model_App->run(Array)
#22 /home/site/public_html/index.php(80): Mage::run('', 'store')
#23 {main}

I enabled advanced logging and the key bit of info i got was this

2012-02-15T09:33:10+00:00 ERR (3): Zend_Db_Statement_Exception Object
(
[_previous:private] => PDOException Object
(
[message:protected] => SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'product_id' cannot be null
[string:private] =>
[code:protected] => 23000
[file:protected] => /home/aaamber/public_html/lib/Zend/Db/Statement/Pdo.php
[line:protected] => 228
[trace:private] => Array
(
[0] => Array
(
[file] => /home/site/public_html/lib/Zend/Db/Statement/Pdo.php
[line] => 228
[function] => execute
[class] => PDOStatement
[type] => ->
[args] => Array
(
[0] => Array
(
[0] => 1
[1] => 19
[2] =>
[3] => category/19
[4] => something-somthing
[5] => catalog/category/view/id/19
[6] => 1
)

)

)

[1] => Array
(
[file] => /home/site/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php
[line] => 110
[function] => _execute
[class] => Zend_Db_Statement_Pdo
[type] => ->
[args] => Array
(
[0] => Array
(
[0] => 1
[1] => 19
[2] =>
[3] => category/19
[4] => something-something
[5] => catalog/category/view/id/19
[6] => 1
)

)

)

[2] => Array
(
[file] => /home/site/public_html/lib/Zend/Db/Statement.php
[line] => 300
[function] => _execute
[class] => Varien_Db_Statement_Pdo_Mysql
[type] => ->
[args] => Array
(
[0] => Array
(
[0] => 1
[1] => 19
[2] =>
[3] => category/19
[4] => something-something
[5] => catalog/category/view/id/19
[6] => 1
)

)

)

[3] => Array
(
[file] => /home/site/public_html/lib/Zend/Db/Adapter/Abstract.php
[line] => 479
[function] => execute
[class] => Zend_Db_Statement
[type] => ->
[args] => Array
(
[0] => Array
(
[0] => 1
[1] => 19
[2] =>
[3] => category/19
[4] => something-something
[5] => catalog/category/view/id/19
[6] => 1
)

)

)

[4] => Array
(
[file] => /home/site/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php
[line] => 238
[function] => query
[class] => Zend_Db_Adapter_Abstract
[type] => ->
[args] => Array
(
[0] => INSERT INTO `core_url_rewrite` (`store_id`, `category_id`, `product_id`, `id_path`, `request_path`, `target_path`, `is_system`) VALUES (?, ?, ?, ?, ?, ?, ?)
[1] => Array
(

As you can see for some reason array [2] => is empty, this is the product id position in the array and also by the error message “Column ‘product_id’ cannot be null”

This led me to inspecting core_url_rewrite table where i found the fix staring me in the face… it took me about 9 hours and countless attempted fixes from what i could find online

The Solution

When i took a close look at the core_url_rewrite table i compared it with one of my other working magento installs and found that “null” was not set on the product_id column, i set the property null to be yes, and set the default value to null.

10 seconds later i could re index without error, my SEF URLS were restored and there was no horrible lag when saving a product.

Somehow during the upgrade this property was removed from the column – which led to the issues. such a simple fix

I hope Ive been able to help