Magento reindexing issue

Question

We are using magento enterprise version. Sometimes products are missing and they are coming back during reindexing. Can you tell us what might caused disappearing products?

This happen twice a week or so.

Also when we reindexing, we are getting following error, and we need to reindex again. Please let us know how we can fix reindexing error?

Catalog product price index process unknown error:

exception 'PDOException' with message 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction' in /var/www/mysite/public_html/lib/Zend/Db/Statement/Pdo.php:228

Stack trace:

#0 /var/www/mysite/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)

#1 /var/www/mysite/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

#2 /var/www/mysite/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)

#3 /var/www/mysite/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)

#4 /var/www/mysite/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `catalog...', Array)

#5 /var/www/mysite/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `catalog...', Array)

#6 /var/www/mysite/public_html/app/code/core/Mage/CatalogRule/Model/Resource/Rule/Product/Price.php(88): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `catalog...')

#7 /var/www/mysite/public_html/app/code/core/Mage/CatalogRule/Model/Rule/Product/Price.php(80): Mage_CatalogRule_Model_Resource_Rule_Product_Price->applyPriceRuleT oIndexTable(Object(Varien_Db_Select), Array, 'i.entity_id', 'i.customer_grou...', 'i.website_id', Array, 'wd.website_date')

#8 /var/www/mysite/public_html/app/code/core/Mage/CatalogRule/Model/Observer.php(254): Mage_CatalogRule_Model_Rule_Product_Price->applyPriceRuleToIndexTable(Object(Varien_Db_Select), Array, 'i.entity_id', 'i.customer_grou...', 'i.website_id', Array, 'wd.website_date')

#9 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1338): Mage_CatalogRule_Model_Observer->prepareCatalogProductPriceIndexTable(Object(Varien_Event_Observer))

#10 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Mage_CatalogRule_Model_Observer), 'prepareCatalogP...', Object(Varien_Event_Observer))

#11 /var/www/mysite/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('prepare_catalog...', Array)

#12 /var/www/mysite/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Default.php(304): Mage::dispatchEvent('prepare_catalog...', Array)

#13 /var/www/mysite/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Default.php(120): Mage_Catalog_Model_Resource_Product_Indexer_Price_Default->_prepareFinalPriceData()

#14 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Product/Price/Refresh.php(75): Mage_Catalog_Model_Resource_Product_Indexer_Price_Default->reindexAll()

#15 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Product/Price/Refresh.php(49): Enterprise_Catalog_Model_Index_Action_Product_Price_Refresh->_reindexAll()

#16 /var/www/mysite/public_html/app/code/core/Enterprise/Mview/Model/Client.php(109): Enterprise_Catalog_Model_Index_Action_Product_Price_Refresh->execute()

#17 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Observer/Price.php(129): Enterprise_Mview_Model_Client->execute('enterprise_cata...')

#18 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1338): Enterprise_Catalog_Model_Index_Observer_Price->processShellProductReindexEvent(Object(Varien_Event_Observer))

#19 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Enterprise_Catalog_Model_Index_Observer_Price), 'processShellPro...', Object(Varien_Event_Observer))

#20 /var/www/mysite/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('catalog_product...', Array)

#21 /var/www/mysite/public_html/shell/indexer.php(167): Mage::dispatchEvent('catalog_product...')

#22 /var/www/mysite/public_html/shell/indexer.php(212): Mage_Shell_Compiler->run()

#23 {main}




Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction' in /var/www/mysite/public_html/lib/Zend/Db/Statement/Pdo.php:234

Stack trace:

#0 /var/www/mysite/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

#1 /var/www/mysite/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)

#2 /var/www/mysite/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)

#3 /var/www/mysite/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `catalog...', Array)

#4 /var/www/mysite/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `catalog...', Array)

#5 /var/www/mysite/public_html/app/code/core/Mage/CatalogRule/Model/Resource/Rule/Product/Price.php(88): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `catalog...')

#6 /var/www/mysite/public_html/app/code/core/Mage/CatalogRule/Model/Rule/Product/Price.php(80): Mage_CatalogRule_Model_Resource_Rule_Product_Price->applyPriceRuleToIndexTable(Object(Varien_Db_Select), Array, 'i.entity_id', 'i.customer_grou...', 'i.website_id', Array, 'wd.website_date')

#7 /var/www/mysite/public_html/app/code/core/Mage/CatalogRule/Model/Observer.php(254): Mage_CatalogRule_Model_Rule_Product_Price->applyPriceRuleToIndexTable(Object(Varien_Db_Select), Array, 'i.entity_id', 'i.customer_grou...', 'i.website_id',Array, 'wd.website_date')

#8 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1338): Mage_CatalogRule_Model_Observer->prepareCatalogProductPriceIndexTable(Object(Varien_Event_Observer))

#9 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Mage_CatalogRule_Model_Observer), 'prepareCatalogP...', Object(Varien_Event_Observer))

#10 /var/www/mysite/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('prepare_catalog...', Array)

#11 /var/www/mysite/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Default.php(304): Mage::dispatchEvent('prepare_catalog...', Array)

#12 /var/www/mysite/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Default.php(120): Mage_Catalog_Model_Resource_Product_Indexer_Price_Default->_prepareFinalPriceData()

#13 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Product/Price/Refresh.php(75): Mage_Catalog_Model_Resource_Product_Indexer_Price_Default->reindexAll()

#14 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Product/Price/Refresh.php(49): Enterprise_Catalog_Model_Index_Action_Product_Price_Refresh->_reindexAll()

#15 /var/www/mysite/public_html/app/code/core/Enterprise/Mview/Model/Client.php(109): Enterprise_Catalog_Model_Index_Action_Product_Price_Refresh->execute()

#16 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Observer/Price.php(129): Enterprise_Mview_Model_Client->execute('enterprise_cata...')

#17 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1338): Enterprise_Catalog_Model_Index_Observer_Price->processShellProductReindexEvent(Object(Varien_Event_Observer))

#18 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Enterprise_Catalog_Model_Index_Observer_Price), 'processShellPro...', Object(Varien_Event_Observer))

#19 /var/www/mysite/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('catalog_product...', Array)

#20 /var/www/mysite/public_html/shell/indexer.php(167): Mage::dispatchEvent('catalog_product...')

#21 /var/www/mysite/public_html/shell/indexer.php(212): Mage_Shell_Compiler->run()

#22 {main}



Next exception 'Enterprise_Index_Model_Action_Exception' with message 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction' in /var/www/mysite/public_html/app/code/core/Enterprise/Cat
alog/Model/Index/Action/Product/Price/Refresh.php:54

Stack trace:

#0 /var/www/mysite/public_html/app/code/core/Enterprise/Mview/Model/Client.php(109) : Enterprise_Catalog_Model_Index_Action_Product_Price_Refresh->execute()

#1 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Observer/Price.php(129): Enterprise_Mview_Model_Client->execute('enterprise_cata...')

#2 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1338): Enterprise_Catalog_Model_Index_Observer_Price->processShellProductReindexEvent(Object(Varien_Event_Observer))

#3 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Enterprise_Catalog_Model_Index_Observer_Price), 'processShellPro...', Object(Varien_Event_Observer))

#4 /var/www/mysite/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('catalog_product...', Array)

#5 /var/www/mysite/public_html/shell/indexer.php(167): Mage::dispatchEvent('catalog_product...')

#6 /var/www/mysite/public_html/shell/indexer.php(212): Mage_Shell_Compiler->run()

#7 {main}

Category URL Rewrites index was rebuilt successfully

Product URL Rewrites index was rebuilt successfully

URL Redirects index was rebuilt successfully

Catalog Category/Product Index index process unknown error:exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mysite.catalog_category_product_index_tmp' doesn't exist' in /var/www/mysite/public_html/lib/Zend/Db/Statement/Pdo.php:228

Stack trace:

#0 /var/www/mysite/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)

#1 /var/www/mysite/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

#2 /var/www/mysite/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)

#3 /var/www/mysite/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)

#4 /var/www/mysite/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT IGNORE I...', Array)

#5 /var/www/mysite/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT IGNORE I...', Array)

#6 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Catalog/Category/Product/Refresh.php(747): Varien_Db_Adapter_Pdo_Mysql->query('INSERT IGNORE I...')

#7 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Catalog/Category/Product/Refresh.php(396): Enterprise_Catalog_Model_Index_Action_Catalog_Category_Product_Refresh->_reindexAnchorCategories(Object(Mana_Seo_Rewrite_Store))

#8 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Catalog/Category/Product/Refresh.php(170): Enterprise_Catalog_Model_Index_Action_Catalog_Category_Product_Refresh->_reindex()

#9 /var/www/mysite/public_html/app/code/core/Enterprise/Mview/Model/Client.php(109): Enterprise_Catalog_Model_Index_Action_Catalog_Category_Product_Refresh->execute()

#10 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Observer/Category/Product.php(158): Enterprise_Mview_Model_Client->execute('enterprise_cata...')

#11 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1338): Enterprise_Catalog_Model_Index_Observer_Category_Product->processShellCategoryProductReindexEvent(Object(Varien_Event_Observer))

#12 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Enterprise_Catalog_Model_Index_Observer_Category_Product), 'processShellCat...', Object(Varien_Event_Observer))

#13 /var/www/mysite/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('catalog_categor...', Array)

#14 /var/www/mysite/public_html/shell/indexer.php(167): Mage::dispatchEvent('catalog_categor...')

#15 /var/www/mysite/public_html/shell/indexer.php(212): Mage_Shell_Compiler->run()

#16 {main}



Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42S02]: Basetable or view not found: 1146 Table 'mysite.catalog_category_product_index_tmp' doesn't exist' in /var/www/mysite/public_html/lib/Zend/Db/Statement/Pdo.php:234

Stack trace:

#0 /var/www/mysite/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

#1 /var/www/mysite/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)

#2 /var/www/mysite/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)

#3 /var/www/mysite/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT IGNORE I...', Array)

#4 /var/www/mysite/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT IGNORE I...', Array)

#5 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Catalog/Category/Product/Refresh.php(747): Varien_Db_Adapter_Pdo_Mysql->query('INSERT IGNORE I...')

#6 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Catalog/Category/Product/Refresh.php(396): Enterprise_Catalog_Model_Index_Action_Catalog_Category_Product_Refresh->_reindexAnchorCategories(Object(Mana_Seo_Rewrite_Store))

#7 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Catalog/Category/Product/Refresh.php(170): Enterprise_Catalog_Model_Index_Action_Catalog_Category_Product_Refresh->_reindex()

#8 /var/www/mysite/public_html/app/code/core/Enterprise/Mview/Model/Client.php(109): Enterprise_Catalog_Model_Index_Action_Catalog_Category_Product_Refresh->execute()

#9 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Observer/Category/Product.php(158): Enterprise_Mview_Model_Client->execute('enterprise_cata...')

#10 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1338): Enterprise_Catalog_Model_Index_Observer_Category_Product->processShellCategoryProductReindexEvent(Object(Varien_Event_Observer))

#11 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Enterprise_Catalog_Model_Index_Observer_Category_Product), 'processShellCat...', Object(Varien_Event_Observer))

#12 /var/www/mysite/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('catalog_categor...', Array)

#13 /var/www/mysite/public_html/shell/indexer.php(167): Mage::dispatchEvent('catalog_categor...')

#14 /var/www/mysite/public_html/shell/indexer.php(212): Mage_Shell_Compiler->run()

#15 {main}



Next exception 'Enterprise_Index_Model_Action_Exception' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mysite.catalog_category_product_index_tmp' doesn't exist' in /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Action/Catalog/Category/Product/Refresh.php:175

Stack trace:

#0 /var/www/mysite/public_html/app/code/core/Enterprise/Mview/Model/Client.php(109): Enterprise_Catalog_Model_Index_Action_Catalog_Category_Product_Refresh->execute()

#1 /var/www/mysite/public_html/app/code/core/Enterprise/Catalog/Model/Index/Observer/Category/Product.php(158): Enterprise_Mview_Model_Client->execute('enterprise_cata...')

#2 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1338): Enterprise_Catalog_Model_Index_Observer_Category_Product->processShellCategoryProductReindexEvent(Object(Varien_Event_Observer))

#3 /var/www/mysite/public_html/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Enterprise_Catalog_Model_Index_Observer_Category_Product), 'processShellCat...', Object(Varien_Event_Observer))

#4 /var/www/mysite/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('catalog_categor...', Array)

#5 /var/www/mysite/public_html/shell/indexer.php(167): Mage::dispatchEvent('catalog_categor...')

#6 /var/www/mysite/public_html/shell/indexer.php(212): Mage_Shell_Compiler->run()

#7 {main}

Catalog Search Index index was rebuilt successfully

Product Attributes index was rebuilt successfully

SEO URL Rewrites (MANAdev) index was rebuilt successfully

Tag Aggregation Data index was rebuilt successfully

SEO Schemas (MANAdev) index was rebuilt successfully

Default Values (MANAdev) index was rebuilt successfully

Answer 1

One thing is to set your indexer to run manually previous to reindexing to be sure that an edit in the backend is not putting a lock that can be released and then blocks the indexation process. You neet to set back indexer to their running state afterward.