Error on search ( Magento CE 1.9.0.1 )

[hidonet]

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 'AS query_id, s.product_id, 0 AS relevance FROM catalogsearch_fulltext ' at line 1
#0 /home/dev1????/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/dev1????/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/dev1????/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/dev1????/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO ca...', Array) #4 /home/dev1????/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO ca...', Array)
#5 /home/dev1????/public_html/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php(407): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...', Array)
#6 /home/dev1????/public_html/app/code/core/Mage/CatalogSearch/Model/Fulltext.php(136): Mage_CatalogSearch_Model_Resource_Fulltext->prepareResult(Object(Mage_CatalogSearch_Model_Fulltext), 'kalabak', Object(Mage_CatalogSearch_Model_Query))
#7 /home/dev1????/public_html/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext/Collection.php(55): Mage_CatalogSearch_Model_Fulltext->prepareResult()
#8 /home/dev1????/public_html/app/code/core/Mage/CatalogSearch/Model/Layer.php(58): Mage_CatalogSearch_Model_Resource_Fulltext_Collection->addSearchFilter('kalabak')
#9 /home/dev1????/public_html/app/code/core/Mage/CatalogSearch/Model/Layer.php(42): Mage_CatalogSearch_Model_Layer->prepareProductCollection(Object(Mage_CatalogSearch_Model_Resource_Fulltext_Collection))
#10 /home/dev1????/public_html/app/code/local/Mage/CatalogSearch/Block/Result.php(164): Mage_CatalogSearch_Model_Layer->getProductCollection()
#11 /home/dev1????/public_html/app/code/local/Mage/CatalogSearch/Block/Result.php(141): Mage_CatalogSearch_Block_Result->_getProductCollection()
#12 [internal function]: Mage_CatalogSearch_Block_Result->setListCollection()
#13 /home/dev1????/public_html/app/code/core/Mage/Core/Model/Layout.php(349): call_user_func_array(Array, Array)
#14 /home/dev1????/public_html/app/code/core/Mage/Core/Model/Layout.php(214): Mage_Core_Model_Layout->_generateAction(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#15 /home/dev1????/public_html/app/code/core/Mage/Core/Model/Layout.php(206): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#16 /home/dev1????/public_html/app/code/core/Mage/Core/Model/Layout.php(210): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#17 /home/dev1????/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(344): Mage_Core_Model_Layout->generateBlocks()
#18 /home/dev1????/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(269): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#19 /home/dev1????/public_html/app/code/community/Pocketphp/Elasticsearch/controllers/SearchController.php(31): Mage_Core_Controller_Varien_Action->loadLayout()
#20 /home/dev1????/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Pocketphp_Elasticsearch_SearchController->indexAction()
#21 /home/dev1????/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('index')
#22 /home/dev1????/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#23 /home/dev1????/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#24 /home/dev1????/public_html/app/Mage.php(685): Mage_Core_Model_App->run(Array)
#25 /home/dev1????/public_html/index.php(111): Mage::run('', 'store')
#26 {main}

[ctasca]

What layout file are you using?

[hidonet]

What Layout ? Design layout or something else ?

[hidonet]

I've found problem but not fixed yet...

/app/code/core/Mage/CatalogSearch/Model/Resource/Fulltext.php on line 360 $query->getId() returning null.

        $fields = array(
            'query_id' => new Zend_Db_Expr($query->getId()),
            'product_id',
        );

[hidonet]

I've fixed it...

[ctasca]

Nice! Was the error in the core model of Magento?

[hidonet]

Check this out : https://github.com/hidonet/magento-elasticsearch-module/blob/master/app/code/community/Pocketphp/Elasticsearch/controllers/SearchController.php

[ctasca]

Can you please create a pull request with your fix?

[aglipanci]

Hey @ctasca @hidonet any suggestion on how to solve this? It's very annoying. It throws this error even when the category is empty, and even if the pagination number is more than the results.

[ctasca]

@aglipanci Sorry about late reply, I wasn't actively maintaining this module anymore but I have some time now to look and fix a few things. I think there is way to use a different cluster while re-indexing. I'll look if this can be integrated