SQLSTATE[42S22]: Column not found: 1054 Unknown column 'search_result.score'

[Stevan1978]

Hi all,

I hope someone can help me out here as I seem to be at a loss here.

I have 2 Magento 2 webshops. One in Magento 2.2.7 and the other in Magento 2.3.1.
Both have the Porto Theme and both use the Child theme.

The 2.2.7 had a migration from Magento 1.9.4.0 and the other a migration from M 2.2.6 to 2.3.1.

The shops seem to work but as soon as you search a product, let's say "test" I get this error:

SELECT `e`.*, 
	`stock_status_index`.`stock_status` AS `is_salable`, 
        `price_index`.`price`, `price_index`.`tax_class_id`, 
        `price_index`.`final_price`, 
        IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`,
        `price_index`.`min_price`, 
        `price_index`.`max_price`, 
        `price_index`.`tier_price`, 
        `cat_index`.`position` AS `cat_index_position` 
        FROM `catalog_product_entity` AS `e`
        INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id 
        AND stock_status_index.website_id = 0 
        AND stock_status_index.stock_id = 1
        INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id 
        AND price_index.website_id = '1' 
        AND price_index.customer_group_id = 0
        INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id 
        AND cat_index.store_id='1' 
        AND cat_index.visibility IN(3, 4)  
        AND cat_index.category_id='3' 
        WHERE (stock_status_index.stock_status = 1) 
        ORDER BY `search_result`.`score` desc LIMIT 9

It's really weird and this is giving me a headache.

If someone can point me in the right direction I would really appreciate this.

Cheers,

Steve

[Tarandeep Singh]

@Stevan1978Did you recently upgraded and noticed the issue or it's been a while for upgrade? Anything you did recently like added some attribute to search or created new, installed some extension etc after which issue appeared?

You may have possibly done this but in case not then please reindex the data and try to browse  the same page which giving you an error. In some cases indexing adds the missing column back to the indexed table.

Problem solved? Please give 'Kudos' and accept 'Answer as Solution'.

[Sunil Patel]

Hello @Stevan1978 

are you using any custom module for layer navigation or search related then try to disable it and check it.

based on error seems like one join missing with search temp table.

Hope it will help you, if works then mark as solution

[Stevan1978]

Hi Sunil,

I'm not using any custom module for layered navigation and/or search.
At the moment Mageplaza_Search module is used and the layered navigation is provided by the Porto Theme for Magento 2.

If I change the theme to Luma I also get this issue so I can say it's not related to the theme.
I did reindex the shop this morning and everything is reindexed without issues.

The search field in Magento 2 does give me products and when I click those it does show the product but as soon as I "force" the search and search for the word test, it throws this error.

@Tarandeep Singh It's only when I search in Magento it give me the error. The shops are not upgraded. I only imported data.

Br,

Steve

[Sunil Patel]

Hello @Stevan1978 

Mageplaza_Search  : can you disable this module and check it

[Stevan1978]

Already did that but no change in error.

[Stevan1978]

Maybe the problem what we have is what the error says.

Unknown column "search_result". When I look in the database I don't see this table anywhere.

Br,

Steve

[Sunil Patel]

Hello @Stevan1978 

Can you check your mysql user have permission to create temp table?

[Stevan1978]

Hi Sunil,

I really appreciate you're help in this.

The user has all the rights for the SQL and should be able to create this table.

Br,

Steve

[Stevan1978]

Maybe it would help if I show the rest of the error Magento 2 shows me:

#0 /data/web/public/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(93): Zend_Db_Statement_Pdo->_execute(Array)
#1 /data/web/public/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#2 /data/web/public/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /data/web/public/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `e`.*, `...', Array)
#4 /data/web/public/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(533): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `e`.*, `...', Array)
#5 /data/web/public/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(605): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('SELECT `e`.*, `...', Array)
#6 /data/web/public/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(737): Magento\Framework\DB\Adapter\Pdo\Mysql->query(Object(Magento\Framework\DB\Select), Array)
#7 /data/web/public/vendor/magento/framework/Data/Collection/Db/FetchStrategy/Query.php(21): Zend_Db_Adapter_Abstract->fetchAll(Object(Magento\Framework\DB\Select), Array)
#8 /data/web/public/vendor/magento/framework/Data/Collection/AbstractDb.php(774): Magento\Framework\Data\Collection\Db\FetchStrategy\Query->fetchAll(Object(Magento\Framework\DB\Select), Array)
#9 /data/web/public/vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php(1115): Magento\Framework\Data\Collection\AbstractDb->_fetchAll(Object(Magento\Framework\DB\Select))
#10 /data/web/public/generated/code/Mageplaza/LayeredNavigation/Model/ResourceModel/Fulltext/Collection/Interceptor.php(1285): Magento\Eav\Model\Entity\Collection\AbstractCollection->_loadEntities(false, false)
#11 /data/web/public/vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php(913): Mageplaza\LayeredNavigation\Model\ResourceModel\Fulltext\Collection\Interceptor->_loadEntities(false, false)
#12 /data/web/public/vendor/magento/module-catalog/Model/ResourceModel/Product/Collection.php(777): Magento\Eav\Model\Entity\Collection\AbstractCollection->load(false, false)
#13 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Catalog\Model\ResourceModel\Product\Collection->load(false, false)
#14 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(138): Mageplaza\LayeredNavigation\Model\ResourceModel\Fulltext\Collection\Interceptor->___callParent('load', Array)
#15 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(153): Mageplaza\LayeredNavigation\Model\ResourceModel\Fulltext\Collection\Interceptor->Magento\Framework\Interception\{closure}()
#16 /data/web/public/generated/code/Mageplaza/LayeredNavigation/Model/ResourceModel/Fulltext/Collection/Interceptor.php(403): Mageplaza\LayeredNavigation\Model\ResourceModel\Fulltext\Collection\Interceptor->___callPlugins('load', Array, Array)
#17 /data/web/public/vendor/magento/module-review/Observer/CatalogBlockProductCollectionBeforeToHtmlObserver.php(38): Mageplaza\LayeredNavigation\Model\ResourceModel\Fulltext\Collection\Interceptor->load()
#18 /data/web/public/vendor/magento/framework/Event/Invoker/InvokerDefault.php(72): Magento\Review\Observer\CatalogBlockProductCollectionBeforeToHtmlObserver->execute(Object(Magento\Framework\Event\Observer))
#19 /data/web/public/vendor/magento/framework/Event/Invoker/InvokerDefault.php(60): Magento\Framework\Event\Invoker\InvokerDefault->_callObserverMethod(Object(Magento\Review\Observer\CatalogBlockProductCollectionBeforeToHtmlObserver), Object(Magento\Framework\Event\Observer))
#20 /data/web/public/vendor/magento/framework/Event/Manager.php(66): Magento\Framework\Event\Invoker\InvokerDefault->dispatch(Array, Object(Magento\Framework\Event\Observer))
#21 /data/web/public/generated/code/Magento/Framework/Event/Manager/Proxy.php(95): Magento\Framework\Event\Manager->dispatch('catalog_block_p...', Array)
#22 /data/web/public/vendor/magento/module-catalog/Block/Product/ListProduct.php(471): Magento\Framework\Event\Manager\Proxy->dispatch('catalog_block_p...', Array)
#23 /data/web/public/vendor/magento/module-catalog/Block/Product/ListProduct.php(116): Magento\Catalog\Block\Product\ListProduct->initializeProductCollection()
#24 /data/web/public/vendor/magento/module-catalog/Block/Product/ListProduct.php(139): Magento\Catalog\Block\Product\ListProduct->_getProductCollection()
#25 /data/web/public/vendor/magento/module-catalog-search/Block/Result.php(182): Magento\Catalog\Block\Product\ListProduct->getLoadedProductCollection()
#26 /data/web/public/vendor/magento/module-catalog-search/Block/Result.php(206): Magento\CatalogSearch\Block\Result->_getProductCollection()
#27 /data/web/public/app/design/frontend/Smartwave/porto/Magento_CatalogSearch/templates/result.phtml(10): Magento\CatalogSearch\Block\Result->getResultCount()
#28 /data/web/public/vendor/magento/framework/View/TemplateEngine/Php.php(59): include('/data/web/publi...')
#29 /data/web/public/vendor/magento/framework/View/Element/Template.php(270): Magento\Framework\View\TemplateEngine\Php->render(Object(Magento\CatalogSearch\Block\Result), '/data/web/publi...', Array)
#30 /data/web/public/vendor/magento/framework/View/Element/Template.php(300): Magento\Framework\View\Element\Template->fetchView('/data/web/publi...')
#31 /data/web/public/vendor/magento/framework/View/Element/AbstractBlock.php(667): Magento\Framework\View\Element\Template->_toHtml()
#32 /data/web/public/vendor/magento/framework/View/Layout.php(557): Magento\Framework\View\Element\AbstractBlock->toHtml()
#33 /data/web/public/vendor/magento/framework/View/Layout.php(533): Magento\Framework\View\Layout->_renderBlock('search.result')
#34 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('search.result')
#35 /data/web/public/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('search.result')
#36 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('search.result', true)
#37 /data/web/public/vendor/magento/framework/View/Element/AbstractBlock.php(511): Magento\Framework\View\Layout\Interceptor->renderElement('search.result', true)
#38 /data/web/public/app/code/Mageplaza/AjaxLayer/view/frontend/templates/products.phtml(24): Magento\Framework\View\Element\AbstractBlock->getChildHtml()
#39 /data/web/public/vendor/magento/framework/View/TemplateEngine/Php.php(59): include('/data/web/publi...')
#40 /data/web/public/vendor/magento/framework/View/Element/Template.php(270): Magento\Framework\View\TemplateEngine\Php->render(Object(Magento\Framework\View\Element\Template), '/data/web/publi...', Array)
#41 /data/web/public/vendor/magento/framework/View/Element/Template.php(300): Magento\Framework\View\Element\Template->fetchView('/data/web/publi...')
#42 /data/web/public/vendor/magento/framework/View/Element/AbstractBlock.php(667): Magento\Framework\View\Element\Template->_toHtml()
#43 /data/web/public/vendor/magento/framework/View/Layout.php(557): Magento\Framework\View\Element\AbstractBlock->toHtml()
#44 /data/web/public/vendor/magento/framework/View/Layout.php(533): Magento\Framework\View\Layout->_renderBlock('layer.category....')
#45 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('layer.category....')
#46 /data/web/public/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('layer.category....')
#47 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('layer.category....', false)
#48 /data/web/public/vendor/magento/framework/View/Layout.php(585): Magento\Framework\View\Layout\Interceptor->renderElement('layer.category....', false)
#49 /data/web/public/vendor/magento/framework/View/Layout.php(535): Magento\Framework\View\Layout->_renderContainer('content', false)
#50 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('content')
#51 /data/web/public/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('content')
#52 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('content', false)
#53 /data/web/public/vendor/magento/framework/View/Layout.php(585): Magento\Framework\View\Layout\Interceptor->renderElement('content', false)
#54 /data/web/public/vendor/magento/framework/View/Layout.php(535): Magento\Framework\View\Layout->_renderContainer('main', false)
#55 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('main')
#56 /data/web/public/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('main')
#57 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('main', false)
#58 /data/web/public/vendor/magento/framework/View/Layout.php(585): Magento\Framework\View\Layout\Interceptor->renderElement('main', false)
#59 /data/web/public/vendor/magento/framework/View/Layout.php(535): Magento\Framework\View\Layout->_renderContainer('columns', false)
#60 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('columns')
#61 /data/web/public/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('columns')
#62 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('columns', false)
#63 /data/web/public/vendor/magento/framework/View/Layout.php(585): Magento\Framework\View\Layout\Interceptor->renderElement('columns', false)
#64 /data/web/public/vendor/magento/framework/View/Layout.php(535): Magento\Framework\View\Layout->_renderContainer('main.content', false)
#65 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('main.content')
#66 /data/web/public/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('main.content')
#67 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('main.content', false)
#68 /data/web/public/vendor/magento/framework/View/Layout.php(585): Magento\Framework\View\Layout\Interceptor->renderElement('main.content', false)
#69 /data/web/public/vendor/magento/framework/View/Layout.php(535): Magento\Framework\View\Layout->_renderContainer('page.wrapper', false)
#70 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('page.wrapper')
#71 /data/web/public/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('page.wrapper')
#72 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('page.wrapper', false)
#73 /data/web/public/vendor/magento/framework/View/Layout.php(585): Magento\Framework\View\Layout\Interceptor->renderElement('page.wrapper', false)
#74 /data/web/public/vendor/magento/framework/View/Layout.php(535): Magento\Framework\View\Layout->_renderContainer('root', false)
#75 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('root')
#76 /data/web/public/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('root')
#77 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('root', true)
#78 /data/web/public/vendor/magento/framework/View/Layout.php(954): Magento\Framework\View\Layout\Interceptor->renderElement('root')
#79 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Framework\View\Layout->getOutput()
#80 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\View\Layout\Interceptor->___callParent('getOutput', Array)
#81 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Framework\View\Layout\Interceptor->Magento\Framework\Interception\{closure}()
#82 /data/web/public/generated/code/Magento/Framework/View/Layout/Interceptor.php(494): Magento\Framework\View\Layout\Interceptor->___callPlugins('getOutput', Array, Array)
#83 /data/web/public/vendor/magento/framework/View/Result/Page.php(257): Magento\Framework\View\Layout\Interceptor->getOutput()
#84 /data/web/public/vendor/magento/framework/View/Result/Layout.php(170): Magento\Framework\View\Result\Page->render(Object(Magento\Framework\App\Response\Http\Interceptor))
#85 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Framework\View\Result\Layout->renderResult(Object(Magento\Framework\App\Response\Http\Interceptor))
#86 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\View\Result\Page\Interceptor->___callParent('renderResult', Array)
#87 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Framework\View\Result\Page\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Response\Http\Interceptor))
#88 /data/web/public/generated/code/Magento/Framework/View/Result/Page/Interceptor.php(130): Magento\Framework\View\Result\Page\Interceptor->___callPlugins('renderResult', Array, Array)
#89 /data/web/public/vendor/magento/framework/App/View.php(221): Magento\Framework\View\Result\Page\Interceptor->renderResult(Object(Magento\Framework\App\Response\Http\Interceptor))
#90 /data/web/public/app/code/Mageplaza/AjaxLayer/Controller/Search/Result/Index.php(149): Magento\Framework\App\View->renderLayout()
#91 /data/web/public/generated/code/Mageplaza/AjaxLayer/Controller/Search/Result/Index/Interceptor.php(24): Mageplaza\AjaxLayer\Controller\Search\Result\Index->execute()
#92 /data/web/public/vendor/magento/framework/App/Action/Action.php(107): Mageplaza\AjaxLayer\Controller\Search\Result\Index\Interceptor->execute()
#93 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))
#94 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(138): Mageplaza\AjaxLayer\Controller\Search\Result\Index\Interceptor->___callParent('dispatch', Array)
#95 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(153): Mageplaza\AjaxLayer\Controller\Search\Result\Index\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#96 /data/web/public/generated/code/Mageplaza/AjaxLayer/Controller/Search/Result/Index/Interceptor.php(39): Mageplaza\AjaxLayer\Controller\Search\Result\Index\Interceptor->___callPlugins('dispatch', Array, Array)
#97 /data/web/public/vendor/magento/framework/App/FrontController.php(55): Mageplaza\AjaxLayer\Controller\Search\Result\Index\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#98 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))
#99 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)
#100 /data/web/public/vendor/magento/module-store/App/FrontController/Plugin/RequestPreprocessor.php(94): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#101 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(135): Magento\Store\App\FrontController\Plugin\RequestPreprocessor->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#102 /data/web/public/vendor/magento/module-page-cache/Model/App/FrontController/BuiltinPlugin.php(69): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#103 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(135): Magento\PageCache\Model\App\FrontController\BuiltinPlugin->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#104 /data/web/public/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#105 /data/web/public/generated/code/Magento/Framework/App/FrontController/Interceptor.php(26): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, NULL)
#106 /data/web/public/vendor/magento/framework/App/Http.php(135): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#107 /data/web/public/generated/code/Magento/Framework/App/Http/Interceptor.php(24): Magento\Framework\App\Http->launch()
#108 /data/web/public/vendor/magento/framework/App/Bootstrap.php(257): Magento\Framework\App\Http\Interceptor->launch()
#109 /data/web/public/index.php(39): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http\Interceptor))
#110 {main}