Search not working

apiuser · ‎08-30-2016

Feature request from mc388, posted on GitHub Nov 27, 2015

Hi,

I just installed magento 2 with sample data and try to search for a product. After starting the search, mysql has a query which now is running over 27 minutes and it is still running.

Here the query:

INSERT INTO `search_tmp_5658146c0b72c8_28632618` SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT `search_index`.`entity_id`, ((0) + LEAST((MATCH (data_index) AGAINST ('back*' IN BOOLEAN MODE)), 1000000) * POW(2, search_weight)) AS `score` FROM `catalogsearch_fulltext_scope1` AS `search_index`
 LEFT JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
 LEFT JOIN `cataloginventory_stock_status` AS `stock_index` ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = '1' WHERE (stock_index.stock_status = 1) AND (MATCH (data_index) AGAINST ('back*' IN BOOLEAN MODE))) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC LIMIT 10000 /* statement may be truncated */

apiuser · ‎08-30-2016

apiuser · ‎08-30-2016

Comment from jamescowie, posted on GitHub Nov 27, 2015

Hello @mc388 Are you able to provide any more information such as what version you are using, MySQL version etc. I will try and replicate the issue and report back

apiuser · ‎08-30-2016

Comment from mc388, posted on GitHub Nov 27, 2015

Magento 2 EE, MySQL 5.7.x, PHP 5.6.x-fpm

Currently no redis and no solr

apiuser · ‎08-30-2016

Comment from choukalos, posted on GitHub Nov 29, 2015

That's odd - looks like the query that builds the catalog search index. Can you provide some insight on the timing of that query - i.e. when did the search fire relative to cron running to build the catalog search index would be helpful for us to understand if there's a race condition during index creation.

Also please try killing that query in mysql ( i.e. show process list; kill {process#} }, run cron twice and then try searching and let us know if your still seeing an issue.

Thanks!

apiuser · ‎08-30-2016

Comment from kandy, posted on GitHub Nov 29, 2015

Looks like MySql 5.7 related issue. Can you try reproduce it on MySql 5.6?

apiuser · ‎08-30-2016

Comment from mc388, posted on GitHub Nov 30, 2015

Yes, it is a MySql 5.7 issue. I changed the docker container to 5.6, reinstall Magento and now it works fine.

Please fix the search for MySql 5.7

apiuser · ‎08-30-2016

Comment from choukalos, posted on GitHub Nov 30, 2015

Created issue - MAGETWO-46201 to track internally. Lower priority as MySQL 5.6 is still in the process of being adopted by major distributions.

apiuser · ‎08-30-2016

Comment from robfico, posted on GitHub Feb 03, 2016

fyi, seeing this same issue with PHP 7.0.2 and MySQL 5.7.10. MySQL 5.7 is GA, and recommended version by MySQL. Cannot search as original author stated. Is this slated to be fixed? Any quick workaround?

apiuser · ‎08-30-2016

Comment from kandy, posted on GitHub Feb 27, 2016

As workaround you can apply patch

Index: lib/internal/Magento/Framework/Search/Adapter/Mysql/Mapper.php
===================================================================
--- lib/internal/Magento/Framework/Search/Adapter/Mysql/Mapper.php  (date 1456520676000)
+++ lib/internal/Magento/Framework/Search/Adapter/Mysql/Mapper.php  (revision )
@@ -159,7 +159,7 @@
     {
         $parentSelect = $this->getConnection()->select();
         $parentSelect->from(
-            ['main_select' => $select],
+            ['main_select' => $select->limit(10000000000)],
             [
                 $this->entityMetadata->getEntityId() => 'entity_id',
                 'relevance' => sprintf('MAX(%s)', $scoreBuilder->getScoreAlias())

apiuser · ‎08-30-2016

Comment from EmilyPepperman, posted on GitHub Apr 25, 2016

Hi Kandy... I am a bit new to M2 can you help with a little more instruction on how to apply your patch for this issue?