3

I'm searching on the internet for an answer but no luck so far.

I have 2 Magento 2 webshops. One is version 2.2.7 and one is 2.3.1. Both have the latest Porto theme installed and both work with the child theme.

With both webshops I did some product imports and when you try to search for a product you get this error:

Column not found: 1054 Unknown column 'search_result.score' in 'order clause'. query was:

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

The only time I saw someone with this issue was in another question: magento2 search_tmp join leads to empty results in category listing from Gabriel but no answer so far.

What is it that is causing this?

Cheers,

Steve

  • did you find any solution on this? I'm getting this error on M232(upgraded from M231) with ES 6 setup on multi-stores. – sandip Jul 12 at 13:19
0

I was finally able to fix this issue by changing the catalog search settings in Magento 2.3.1 from MySQL to Elasticsearch6+. Note, this has to be supported by your webhosting provider. I am with Webdomain.com and they do support it on the shared hosting plans.

New contributor
sher khan is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

Wow, I CAN'T believe it was THAT simple! I could have sworn I tried changing the search engine from MySQL to Elasticsearch6+ in the past, but nothing had worked.. I finally disabled and removed Mirasvit Elasticsearch Ultimate Extension, and I disabled the Mageplaza search extension that came with my Porto theme, and after disabling both of those, switching from MySql to Elasticsearch 6+ worked like a charm!

I'm going to try to reinstall Mirasvit Elasticsearch Ultimate again to see if it breaks the site again.

Thank you again for your response! This was driving me CRAZY!

FYI: This started happening AFTER I upgraded from 2.2.x to 2.3.. and I had Mirasvit Elasticsearch Ultimate, and Porto Theme.

New contributor
DommyG is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.