Magento – Can’t force reindex in ssh for catalog search index

magento-1.9reindex

Can't seem to reindex in back end and so thought I would give it a go in terminal

Navigated to the appropriate bits and still it won't do it.

I'm a bit of a novice at shh so wondered if someone could point me in the right direction

-bash-4.2$ php indexer.php --status

Product Attributes: Pending
Product Prices: Pending
Catalog URL Rewrites: Pending
Product Flat Data: Pending
Category Flat Data: Pending
Category Products: Pending
Catalog Search Index: Require Reindex
Stock Status: Pending
Tag Aggregation Data: Pending
Algolia Search Products: Pending
Algolia Search Categories: Pending
Algolia Search Pages: Pending
Algolia Search Suggestions: Pending
Algolia Search Additional autocomplete sections: Pending
Algolia Search Queue Runner: Pending

php indexer.php --reindex catalog_search_index

After running them I get this

Warning: Unknown indexer with code catalog_search_index

Any ideas where I am going wrong? would it be ok to do a force reindex for all the indexes?

I tried this solution:

php indexer.php --reindex catalogsearch_fulltext

Which gave me this result:

Catalog Search Index index process unknown error:
exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created' in 'field list'' in /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `od...', Array)
#5 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `od...', Array)
#6 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Zend/Db/Adapter/Abstract.php(576): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `od...', Array)
#7 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/community/Algolia/Algoliasearch/Model/Queue.php(61): Zend_Db_Adapter_Abstract->insert('odb2sdoyhealgol...', Array)
#8 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/community/Algolia/Algoliasearch/Model/Resource/Engine.php(47): Algolia_Algoliasearch_Model_Queue->add('algoliasearch/o...', 'saveSettings', Array, 1)
#9 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/community/Algolia/Algoliasearch/Model/Resource/Engine.php(337): Algolia_Algoliasearch_Model_Resource_Engine->addToQueue('algoliasearch/o...', 'saveSettings', Array, 1)
#10 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/community/Algolia/Algoliasearch/Model/Resource/Engine.php(173): Algolia_Algoliasearch_Model_Resource_Engine->saveSettings(true)
#11 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/community/Algolia/Algoliasearch/Model/Resource/Fulltext.php(67): Algolia_Algoliasearch_Model_Resource_Engine->rebuildProducts(NULL)
#12 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/core/Mage/CatalogSearch/Model/Fulltext.php(84): Algolia_Algoliasearch_Model_Resource_Fulltext->rebuildIndex(NULL, NULL)
#13 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/core/Mage/CatalogSearch/Model/Indexer/Fulltext.php(455): Mage_CatalogSearch_Model_Fulltext->rebuildIndex()
#14 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/core/Mage/Index/Model/Process.php(212): Mage_CatalogSearch_Model_Indexer_Fulltext->reindexAll()
#15 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#16 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#17 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/shell/indexer.php(216): Mage_Shell_Compiler->run()
#18 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created' in 'field list', query was: INSERT INTO `odb2sdoyhealgoliasearch_queue` (`created`, `class`, `method`, `data`, `data_size`, `pid`) VALUES (?, ?, ?, ?, ?, ?)' in /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `od...', Array)
#4 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `od...', Array)
#5 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/lib/Zend/Db/Adapter/Abstract.php(576): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `od...', Array)
#6 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/community/Algolia/Algoliasearch/Model/Queue.php(61): Zend_Db_Adapter_Abstract->insert('odb2sdoyhealgol...', Array)
#7 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/community/Algolia/Algoliasearch/Model/Resource/Engine.php(47): Algolia_Algoliasearch_Model_Queue->add('algoliasearch/o...', 'saveSettings', Array, 1)
#8 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/community/Algolia/Algoliasearch/Model/Resource/Engine.php(337): Algolia_Algoliasearch_Model_Resource_Engine->addToQueue('algoliasearch/o...', 'saveSettings', Array, 1)
#9 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/community/Algolia/Algoliasearch/Model/Resource/Engine.php(173): Algolia_Algoliasearch_Model_Resource_Engine->saveSettings(true)
#10 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/community/Algolia/Algoliasearch/Model/Resource/Fulltext.php(67): Algolia_Algoliasearch_Model_Resource_Engine->rebuildProducts(NULL)
#11 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/core/Mage/CatalogSearch/Model/Fulltext.php(84): Algolia_Algoliasearch_Model_Resource_Fulltext->rebuildIndex(NULL, NULL)
#12 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/core/Mage/CatalogSearch/Model/Indexer/Fulltext.php(455): Mage_CatalogSearch_Model_Fulltext->rebuildIndex()
#13 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/core/Mage/Index/Model/Process.php(212): Mage_CatalogSearch_Model_Indexer_Fulltext->reindexAll()
#14 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#15 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#16 /var/www/vhosts/ashtondiscount.co.uk/httpdocs/shell/indexer.php(216): Mage_Shell_Compiler->run()
#17 {main}

Best Answer

For the catalog search index i think the below command is what is needed to run from root directory:

php shell/indexer.php --reindex catalogsearch_fulltext

There is a good answer here about indexes:

Re-index magento via ssh / putty

Related Solutions

Magento – Magento 1.8 reindex all

The catalog_product_index_eav table has a foreign key contraint named

FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID

Looking at this table's definition

CREATE TABLE `catalog_product_index_eav` (
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `value` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Value',
  PRIMARY KEY (`entity_id`,`attribute_id`,`store_id`,`value`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID` (`entity_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE` (`value`),
  CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product EAV Index Table';

we can see the foreign key definition is

CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` 
FOREIGN KEY (`entity_id`) 
REFERENCES `catalog_product_entity` (`entity_id`) 
ON DELETE CASCADE ON UPDATE CASCADE

This means for every entity_id row in catalog_product_index_eav, there needs to be an identical, corresponding entity_id value in catalog_product_entity.

The root of your problem is for some reason (either a rouge extension, errors caused by randomly typing in SQL from the internet, or performing data updates with disabled indexes), Magento's indexing attempts to update data in catalog_product_index_eav that violates this rule. The next step is identifying what Magento's doing so you can fix the data.

If we look at your call stack, this looks like a good place to start debugging

#10 app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): 
Mage_Index_Model_Resource_Abstract->syncData()

Jumping to that source file, we see the following bit of code

public function syncData()
{
    $this->beginTransaction();
    try {
        /**
         * Can't use truncate because of transaction
         */
        $this->_getWriteAdapter()->delete($this->getMainTable());
        $this->insertFromTable($this->getIdxTable(), $this->getMainTable(), false);
        $this->commit();
    } catch (Exception $e) {
        $this->rollBack();
        throw $e;
    }
    return $this;
}

As part of its indexing process, Magento tries to sync data from an "index table" (getIdxTable), to a "source" (getMainTable) table.

public function insertFromTable($sourceTable, $destTable, $readToIndex = true)
{
    //...
}

For this particular index, the index table is catalog_product_index_eav_idx, and the source table is catalog_product_index_eav.

Note: Be careful with your terminology around here, things are confusingly named. The "Source" table is the table we're copying to. (I believe it's called the source table because it's the "source" a normal Magento system will query from when it needs information)

So, Magento is trying to sync a row from catalog_product_index_eav_idx to the table catalog_product_index_eav. However, this causes the previously mentioned foreign key error. This leads us to two possible conclusions

The catalog_product_index_eav_idx has entity_id rows that do not exist in catalog_product_entity.
The catalog_product_index_eav table has (through previous manipulation with index checks turned off) entity_id rows that do not exist in catalog_product_entity.

So, your mission here is to figure out which entity_id rows in catalog_product_index_eav and catalog_product_index_eav_idx don't exist in catalog_product_entity, and manually delete said rows (from catalog_product_index_eav and catalog_product_index_eav_idx).

If it were me, and my catalog_product_entity table wasn't too large, I'd start with the following queries (these are untested, as I don't have any Magento tables with the above invalid data states)

SELECT * 
FROM catalog_product_index_eav_idx 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

SELECT * FROM catalog_product_index_eav 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

Good luck!

Magento 1.9 – Invalid Block Type Error

Try to find this block in content of the cms page with id toronto_homepage and remove it (in the admin panel).

Or try to find it directly in mysql:

SELECT * FROM `cms_page` WHERE `content` LIKE '%blog/blog%' OR `layout_update_xml` LIKE '%blog/blog%' OR `custom_layout_update_xml` LIKE '%blog/blog%'

In addition try to search 'blog/blog' in layouts.

Best Answer

Related Solutions

Magento – Magento 1.8 reindex all

Magento 1.9 – Invalid Block Type Error

Related Topic