CODEWITHSUNDEEP
phpmysqlmagentoindexingmagento-1.7
Alex
Alex

Reputation: 25

Magento Catalog Search Index, Cannot initialize the indexer process

I dyer need of some help here, Magento 1.7.0.1 is throwing the error 'Cannot initialize the indexer process.' when we try to reindex the catalog search index. All the other indexes are working just fine.

2013-11-28T21:32:38+00:00 DEBUG (7): Exception message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.category_ids' in 'field list'
Trace: #0 /path_to_root/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /path_to_root/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /path_to_root/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /path_to_root/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT STRAIGHT...', Array)
#4 /path_to_root/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECT STRAIGHT...', Array)
#5 /path_to_root/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 /path_to_root/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(265): Zend_Db_Adapter_Abstract->fetchAll(Object(Varien_Db_Select))
#7 /path_to_root/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(157): Mage_CatalogSearch_Model_Resource_Fulltext->_getSearchableProducts(1, Array, NULL, 0)
#8 /path_to_root/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(116): Mage_CatalogSearch_Model_Resource_Fulltext->_rebuildStoreIndex(1, NULL)
#9 /path_to_root/app/code/core/Mage/CatalogSearch/Model/Fulltext.php(84): Mage_CatalogSearch_Model_Resource_Fulltext->rebuildIndex(NULL, NULL)
#10 /path_to_root/app/code/core/Mage/CatalogSearch/Model/Indexer/Fulltext.php(446): Mage_CatalogSearch_Model_Fulltext->rebuildIndex()
#11 /path_to_root/app/code/core/Mage/Index/Model/Process.php(209): Mage_CatalogSearch_Model_Indexer_Fulltext->reindexAll()
#12 /path_to_root/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#13 /path_to_root/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(178): Mage_Index_Model_Process->reindexEverything()
#14 /path_to_root/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Index_Adminhtml_ProcessController->massReindexAction()
#15 /path_to_root/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('massReindex')
#16 /path_to_root/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#17 /path_to_root/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#18 /path_to_root/app/Mage.php(683): Mage_Core_Model_App->run(Array)
#19 /path_to_root/index.php(87): Mage::run('', 'store')
#20 {main}

We've tried the db repair tool and disabling all extensions but we're not getting any response to that.

I'm pretty sure it's looking for e.category_ids in a table somewhere but don't know which.

Any help here would be hot!

Upvotes: 1

Views: 2076

Answers (3)

Jimmy Pelton
Jimmy Pelton

Reputation: 11

You get this error when you have any attribute set to 'static' as the backend_type in eav_attribute (category_ids is one of those) and that same attributeID (108 for category_ids) has 'is_searchable' set to true in catalog_eav_attribute.

The only attributes that can be 'static' and 'is_searchable' have to exist in the catalog_product_entity table (like sku for instance).

I can't tell you how "is_searchable" got set to '1' in catalog_eav_attribute for attribute_id 108, but setting it to '0' should fix this error.

Upvotes: 1

Alex
Alex

Reputation: 25

This may be a bit hacky but I've fixed this simply by adding a blank column into the table catalog_product_entity as an integer titled category_ids I'm pretty sure this was originally caused by the installation of the 'Advanced Custom Product Options Plugin' although I'm unsure of any more details.

Thanks to Alan, that helped alot in getting there! :D

Upvotes: 0

Alana Storm
Alana Storm

Reputation: 166066

PHP/Magento have given you all the information you need to debug this yourself. Specifically, line #7 in the call-stack — the one immediately preceding the call to fetchAll

#7 /path_to_root/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(157): 
Mage_CatalogSearch_Model_Resource_Fulltext->_getSearchableProducts(1, Array, NULL, 0)

It appears the query built by _getSearchableProducts is your culprit. In a standard Magento system, this query looks like the following

SELECT STRAIGHT_JOIN `e`.`entity_id`, `e`.`type_id`, `e`.`sku`, `stock_status`.`stock_status` AS `in_stock` 
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_website` AS `website` 
    ON website.product_id=e.entity_id AND website.website_id='1'
INNER JOIN `cataloginventory_stock_status` AS `stock_status` 
    ON stock_status.product_id=e.entity_id AND stock_status.website_id='1' 
WHERE (e.entity_id>0) ORDER BY `e`.`entity_id` ASC LIMIT 100

That is, there's no mention of a category_ids field. This means there's custom code in your specific system (core hack, listener, rewrite, etc.) somewhere that has added an additional WHERE or ON clause to this query.

You can debug this in your own system by temporarily adding the following debugging to your system 

#File: app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php
protected function _getSearchableProducts($storeId, array $staticFields, $productIds = null, $lastProductId = 0,
    $limit = 100)
{
    //...
    echo (string) $select;
    exit;
    $result = $writeAdapter->fetchAll($select);        
}

This will output the SQL query generated by your system, and you may backtrack from there.

Upvotes: 1

Related Questions