indexer.php reindexall fails with same SQL syntax...

lindap2 · ‎06-29-2017

This command:-

sudo php /var/www/shell/indexer.php -reindexall

fails on 2 indexes with the same sql syntax error

Catalog Search Index

And later

Lucene Search Index

On catalogue Search Index, the condition ‘ciss.website_id = <total blank>’ looks like the syntax error. Here is the offending script:-

SELECT `main`.`linked_product_id`

FROM `catalog_product_link` AS `main`

INNER JOIN `cataloginventory_stock_status` AS `ciss`

ON ciss.product_id = main.product_id

AND ciss.website_id = WHERE (main.product_id = '5336')

AND (link_type_id=3) AND (ciss.stock_status = 1)'

If I run this next script in MySQL Workbench with ‘ciss.website_id = 1’ instead instead of ‘ciss.website_id = <blank>’ it runs OK! (in order to test in workbench, I also removed the single inverted commas that magento seems to have in all SQL ).

SELECT main.linked_product_id

FROM catalog_product_link AS main

INNER JOIN cataloginventory_stock_status AS ciss

ON ciss.product_id = main.product_id

AND ciss.website_id = 1 WHERE (main.product_id = '5336')

AND (link_type_id=3) AND (ciss.stock_status = 1);

The question is why does it fail to pick up a valid website_id (we have 1 and only 1 for our one shop).

Can anyone throw any light on this?

We recently upgraded from magento 1.7.0.2 to 1.9.3.2 everything else is working OK.

Do we need to drop and re-create these two indexes?

See stack trace below

thanks!!

HERE’S THE WHOLE STACK TRACE FOR THE INDEXER ERROR

with bold for the bits that worked:-

prompt>sudo php /var/www/shell/indexer.php -reindexall

Product Attributes index was rebuilt successfully in 00:00:00

Product Prices index was rebuilt successfully in 00:00:01

Catalog URL Rewrites index was rebuilt successfully in 00:02:17

Category Products index was rebuilt successfully in 00:00:00

Catalog Search Index index process unknown error:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (main.product_id = '5336') AND (link_type_id=3) AND (ciss.stock_status = 1' at line 2' in /var/www/lib/Zend/Db/Statement/Pdo.php:228

Stack trace:

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

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

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

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

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

#5 /var/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `main`.`...', Array)

#6 /var/www/lib/Zend/Db/Adapter/Abstract.php(794): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)

#7 /var/www/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(609): Zend_Db_Adapter_Abstract->fetchCol(Object(Varien_Db_Select))

#8 /var/www/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(624): Mage_CatalogSearch_Model_Resource_Fulltext->_getProductChildrenIds('5336', 'grouped')

#9 /var/www/app/code/community/Php4u/BlastLuceneSearch/Model/Mysql4/Lucene.php(19) : eval()'d code(1) : eval()'d code(1) : eval()'d code(18): Mage_CatalogSearch_Model_Resource_Fulltext->_getProductChildIds('5336', 'grouped')

#10 /var/www/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(117): Php4u_BlastLuceneSearch_Model_Mysql4_Lucene->_rebuildStoreIndex(1, NULL)

#11 /var/www/app/code/core/Mage/CatalogSearch/Model/Fulltext.php(84): Mage_CatalogSearch_Model_Resource_Fulltext->rebuildIndex(NULL, NULL)

#12 /var/www/app/code/core/Mage/CatalogSearch/Model/Indexer/Fulltext.php(455): Mage_CatalogSearch_Model_Fulltext->rebuildIndex()

#13 /var/www/app/code/core/Mage/Index/Model/Process.php(212): Mage_CatalogSearch_Model_Indexer_Fulltext->reindexAll()

#14 /var/www/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()

#15 /var/www/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()

#16 /var/www/shell/indexer.php(216): Mage_Shell_Compiler->run()

#17 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (main.product_id = '5336') AND (link_type_id=3) AND (ciss.stock_status = 1' at line 2, query was: SELECT `main`.`linked_product_id` FROM `catalog_product_link` AS `main`

INNER JOIN `cataloginventory_stock_status` AS `ciss` ON ciss.product_id = main.product_id AND ciss.website_id = WHERE (main.product_id = '5336') AND (link_type_id=3) AND (ciss.stock_status = 1)' in /var/www/lib/Zend/Db/Statement/Pdo.php:235

Stack trace:

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

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

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

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

#4 /var/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `main`.`...', Array)

#5 /var/www/lib/Zend/Db/Adapter/Abstract.php(794): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)

#6 /var/www/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(609): Zend_Db_Adapter_Abstract->fetchCol(Object(Varien_Db_Select))

#7 /var/www/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(624): Mage_CatalogSearch_Model_Resource_Fulltext->_getProductChildrenIds('5336', 'grouped')

#8 /var/www/app/code/community/Php4u/BlastLuceneSearch/Model/Mysql4/Lucene.php(19) : eval()'d code(1) : eval()'d code(1) : eval()'d code(18): Mage_CatalogSearch_Model_Resource_Fulltext->_getProductChildIds('5336', 'grouped')

#9 /var/www/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(117): Php4u_BlastLuceneSearch_Model_Mysql4_Lucene->_rebuildStoreIndex(1, NULL)

#10 /var/www/app/code/core/Mage/CatalogSearch/Model/Fulltext.php(84): Mage_CatalogSearch_Model_Resource_Fulltext->rebuildIndex(NULL, NULL)

#11 /var/www/app/code/core/Mage/CatalogSearch/Model/Indexer/Fulltext.php(455): Mage_CatalogSearch_Model_Fulltext->rebuildIndex()

#12 /var/www/app/code/core/Mage/Index/Model/Process.php(212): Mage_CatalogSearch_Model_Indexer_Fulltext->reindexAll()

#13 /var/www/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()

#14 /var/www/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()

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

#16 {main}

Stock Status index was rebuilt successfully in 00:00:00

Tag Aggregation Data index was rebuilt successfully in 00:00:00

Lucene Search Index index process unknown error: