Magento2: query INSERT INTO “search_tmp…” never ends after mass attribute update

[rsulym]

Preconditions

Magento 2.2.1, php-fpm 7.1, MariaDB 10.3.5

Steps to reproduce

Perform mass attribute values update (~30000 items)

Expected result

1) Everything works fine

Actual result

1) re-index process finished ok, but:
2) A lot of queries INSERT INTO “search_tmp…” started, until server reaches pm.max_children parameter value and PHP-fpm die, and they constantly resurrecting even after mysql restart and process kill.

[tgrantmartin]

I too am having this issue. It appears to stem from large layered navigation generated querystrings which the system treats as a search operation and thus attempts to record the terms. This is hugely problematic when crawlers are walking down the layered navigation links. I've used my webserver access logs to add deny rules to my firewall for all but the essential bots from major search engines in a temporary effort to prevent max_connection overrun issues.

[wbyrnetx]

We just launched a 2.2.3 project and got slammed by those insert queries. Appears to be this same exact issue.

Haven't done more digging that that because the site seems to have stabilized.

[ghost]

@rsulym, thank you for your report.
We've acknowledged the issue and added to our backlog.

[tgrantmartin]

I found that this was occurring as a result of an SEO crawler trying to process layered navigation links in rapid succession. Some of the combinations didn't seem to make sense either. At any rate, my solution was to set no-follows on all layered navigation links, and ultimately to remove layered nav from our all products page which listed several thousand skus.

[bilalusean]

any updates? it is a serious issue, please release a patch ASAP. We experienced this issue in Magento 2.2.2.

[Mathiu]

Same here and causes server overload up to few times a day. Would love to get a fix for this.

I can confirm what @tgrantmartin said about crawlers and layered navigation.

Magento version: 2.2.5.

Any progress on this?

[Flowlance]

Same problem. Hundreds of inserts to search_tmp_*. Production server goes down multiple times a day. Any progress here?

[Mathiu]

Not giving any priority this feels bad, not sure why there no fix for this yet as its a breaking bug and been up since about half a year.

I have updated Magento to latest 2.2.6 version which didn't fix the issue and ended up disabling Magento_Search and Magento_CatalogSearch. After disabling them, our site is running super smooth but there is no search...

[quisse]

Anyone having this issue, maybe try creating a PR? I know it can be hard but it's fastest way to solve this issue...

[koseduhemak]

same problem here. Magento 2.1.9.
Every 3rd/4th day my server is killed by an INSERT INTO search_tmp...-Query. Every website on that server stops responding. This a an absolute no go.

If I kill the search_tmp-Query server starts acting normal for few days till it happens again. The problem did not occur for 6 months and then suddenly started. Magento was not touched in that time period so nothing changed here. Of course there were some server-software / mysql / kernel updates etc. but that should not be the problem here.

I saw however, that there were some bots accessing my online shop frequently... Maybe it is like @tgrantmartin described above - the layered navigation.

Maybe it is related to dead locks? I found this via SHOW ENGINE INNODB STATUS;:

*** (1) TRANSACTION:
TRANSACTION 422112337225568, ACTIVE 0 sec starting index read
mysql tables in use 6, locked 6
LOCK WAIT 16 lock struct(s), heap size 1136, 46 row lock(s)
MySQL thread id 191529, OS thread handle 140632223483648, query id 21039562 localhost myMysqlUser Sending data
INSERT INTO `search_tmp_5bf5904d2b9f17_29881944` SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT `search_index`.`entity_id`, (((0) + (0)) * 1) AS `score` FROM `catalogsearch_fulltext_scope1` AS `search_index`
 LEFT JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
 INNER JOIN `catalog_category_product_index` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id
 LEFT JOIN `catalog_product_index_eav` AS `farbe_filter` ON search_index.entity_id = farbe_filter.entity_id AND farbe_filter.attribute_id = 163 AND farbe_filter.store_id = 1
 LEFT JOIN `cataloginventory_stock_status` AS `farbe_filter_stock` ON farbe_filter_stock.product_id = farbe_filter.source_id
 LEFT JOIN `cataloginventory_stock_status` AS `stock_index` ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = 0 WHERE (stock_index.stock_
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10961 page no 20 n bits 368 index PRIMARY of table `myDatabase`.`catalog_category_product_index` trx id 422112337225568 lock mode S waiting
Record lock, heap no 209 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 00000005; asc     ;;
 1: len 4; hex 00000b7b; asc    {;;
 2: len 2; hex 0001; asc   ;;
 3: len 6; hex 0000086f6e36; asc    on6;;
 4: len 7; hex c5000003ce24d4; asc      $ ;;
 5: len 4; hex 80002711; asc   ' ;;
 6: len 2; hex 0000; asc   ;;
 7: len 2; hex 0004; asc   ;;

*** (2) TRANSACTION:
TRANSACTION 141520438, ACTIVE 0 sec inserting
mysql tables in use 12, locked 12
516 lock struct(s), heap size 90320, 54189 row lock(s), undo log entries 1349
MySQL thread id 191515, OS thread handle 140632223082240, query id 21039512 localhost myMysqlUser Sending data
INSERT INTO `catalog_category_product_index` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, ccp.position + 10000 AS `position`, 0 AS `is_parent`, 1 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
 INNER JOIN `temp_catalog_category_tree_index_bf36ab68` AS `cc2` ON cc2.parent_id = cc.entity_id AND cc.entity_id NOT IN (1)
 INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child_id
 INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
 INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
 INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = cpe.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 87
 LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = c
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10961 page no 20 n bits 336 index PRIMARY of table `myDatabase`.`catalog_category_product_index` trx id 141520438 lock_mode X locks rec but not gap
Record lock, heap no 81 PHYSICAL RECORD: n_fields 8; compact format; info bits 0

I also asked for help at magento.stackexchange: https://magento.stackexchange.com/questions/250395/sql-query-kills-entire-server

[vetrivel163]

@Mathiu How you disabled "Magento_Search and Magento_CatalogSearch"? It has dependency.

root@44fffe3e7f6e:/var/www/html# bin/magento mo:dis Magento_Search Magento_CatalogSearch
Unable to change status of modules because of the following constraints:
Cannot disable Magento_Search because modules depend on it:
Magento_CatalogImportExportStaging: Magento_CatalogImportExportStaging->Magento_CatalogStaging->Magento_Search
Magento_AdvancedSearch: Magento_AdvancedSearch->Magento_Search
Magento_CatalogStaging: Magento_CatalogStaging->Magento_Search
Magento_ConfigurableProductStaging: Magento_ConfigurableProductStaging->Magento_CatalogStaging->Magento_Search
Magento_Elasticsearch: Magento_Elasticsearch->Magento_Search
Magento_Solr: Magento_Solr->Magento_Search
Magento_BundleStaging: Magento_BundleStaging->Magento_CatalogStaging->Magento_Search
Magento_DownloadableStaging: Magento_DownloadableStaging->Magento_CatalogStaging->Magento_Search
Cannot disable Magento_CatalogSearch because modules depend on it:
Magento_CatalogImportExportStaging: Magento_CatalogImportExportStaging->Magento_CatalogStaging->Magento_CatalogSearch
Magento_AdvancedSearch: Magento_AdvancedSearch->Magento_CatalogSearch
Magento_CatalogStaging: Magento_CatalogStaging->Magento_CatalogSearch
Magento_ConfigurableProductStaging: Magento_ConfigurableProductStaging->Magento_CatalogStaging->Magento_CatalogSearch
Magento_Elasticsearch: Magento_Elasticsearch->Magento_CatalogSearch
Magento_Solr: Magento_Solr->Magento_CatalogSearch
Magento_BundleStaging: Magento_BundleStaging->Magento_CatalogStaging->Magento_CatalogSearch
Magento_DownloadableStaging: Magento_DownloadableStaging->Magento_CatalogStaging->Magento_CatalogSearch

[Mathiu]

@vetrivel163 I don't have any of these modules installed.

[trantuyen7372]

any updates?

[lefte]

FYI in case anyone else ends up at this search result with the same symptoms we had, while running Magento 2.2.1 we saw dozens of temp tables being created that ended up crashing our database in production, similar to the following:
CREATE TEMPORARY TABLE `search_tmp_5c5c5102169513_69975582` ( `entity_id` int UNSIGNED NOT NULL COMMENT 'Entity ID' , `score` decimal(32,16) UNSIGNED NOT NULL COMMENT 'Score' , PRIMARY KEY (`entity_id`) ) COMMENT='search_tmp_5c5c5102169513_69975582' ENGINE=memory charset=utf8 COLLATE=utf8_general_ci

We ended up solving the symptoms ourselves by overriding the quick order search auto completion to remove the fulltext search and only match on direct SKU results, as every character being typed was generating a new full text search temp table to get and score the results. Because we are a B2B store, we only needed SKU matching, not general text results, so this ended up resolving the symptoms for us.

[tzyganu]

@engcom-backlog-pb Can you explain in 50 words or less what exactly is causing this problem? Maybe we can bypass it until the official fix is released.
This is causing real problems and some people cannot afford to wait for the fix.
Thanks in advance

[asebille]

HI,

We faced the same issue with query like:

INSERT INTO search_tmp_5c94f755eee4a1_99238049
SELECT search_index.entity_id, (((0) + (0) + (0) + (0)) * 1) AS score
FROM catalogsearch_fulltext_scope2 AS search_index
LEFT JOIN catalog_eav_attribute AS cea ON search_index.attribute_id = cea.attribute_id
INNER JOIN catalog_category_product_index AS category_ids_index ON search_index.entity_id = category_ids_index.product_id
LEFT JOIN catalog_product_index_eav AS zzmarque_filter ON search_index.entity_id = zzmarque_filter.entity_id AND zzmarque_filter.attribute_id = 244 AND zzmarque_filter.store_id = 1
LEFT JOIN cataloginventory_stock_status AS zzmarque_filter_stock ON zzmarque_filter_stock.product_id = zzmarque_filter.source_id
LEFT JOIN catalog_product_index_eav AS systeme_exploitation_filter ON search_index.entity_id = systeme_exploitation_filter.entity_id AND systeme_exploitation_filter.attribute_id = 534 AND systeme_exploitation_filter.store_id = 1
LEFT JOIN cataloginventory_stock_status AS systeme_exploitation_filter_stock ON systeme_exploitation_filter_stock.product_id = systeme_exploitation_filter.source_id
LEFT JOIN cataloginventory_stock_status AS stock_index ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = 0
WHERE (stock_index.stock_status = 1) AND (category_ids_index.category_id = 4283) AND (zzmarque_filter.value IN ('818','4429') AND zzmarque_filter_stock.stock_status = 1) AND (search_index.entity_id IN (
SELECT entity_id
FROM 
(
SELECT e.entity_id, IFNULL(current_store.value, main_table.value) AS taille_ecran_pouces
FROM catalog_product_entity AS e
INNER JOIN catalog_product_entity_varchar AS main_table ON main_table.entity_id = e.entity_id
LEFT JOIN catalog_product_entity_varchar AS current_store ON current_store.attribute_id = main_table.attribute_id AND current_store.store_id = 2
WHERE (main_table.attribute_id = '265') AND (main_table.store_id = 0)
HAVING (taille_ecran_pouces IN ('13230'))) AS filter
)) AND (systeme_exploitation_filter.value IN ('9908') AND systeme_exploitation_filter_stock.stock_status = 1)

The most process burning part of this query type was the subquery:

SELECT e.entity_id, IFNULL(current_store.value, main_table.value) AS taille_ecran_pouces
FROM catalog_product_entity AS e
INNER JOIN catalog_product_entity_varchar AS main_table ON main_table.entity_id = e.entity_id
LEFT JOIN catalog_product_entity_varchar AS current_store ON current_store.attribute_id = main_table.attribute_id AND current_store.store_id = 2
WHERE (main_table.attribute_id = '265') AND (main_table.store_id = 0)
HAVING (taille_ecran_pouces IN ('13230')) AS filter

This subquery is build in

/vendor/magento/module-catalog-search/Model/Adapter/Mysql/Filter/Preprocessor.php (line 162 to 192)

private function processQueryWithField(FilterInterface $filter, $isNegation, $query)
    {
        /** @var Attribute $attribute */
        $attribute = $this->config->getAttribute(Product::ENTITY, $filter->getField());
        $linkIdField = $this->getMetadataPool()->getMetadata(ProductInterface::class)->getLinkField();
        if ($filter->getField() === 'price') {
            $resultQuery = str_replace(
                $this->connection->quoteIdentifier('price'),
                $this->connection->quoteIdentifier('price_index.min_price'),
                $query
            );
        } elseif ($filter->getField() === 'category_ids') {
            return 'category_ids_index.category_id = ' . (int) $filter->getValue();
        } elseif ($attribute->isStatic()) {
            $alias = $this->aliasResolver->getAlias($filter);
            $resultQuery = str_replace(
                $this->connection->quoteIdentifier($attribute->getAttributeCode()),
                $this->connection->quoteIdentifier($alias . '.' . $attribute->getAttributeCode()),
                $query
            );
        } elseif (
            $filter->getType() === FilterInterface::TYPE_TERM &&
            in_array($attribute->getFrontendInput(), ['select', 'multiselect'], true)
        ) {
            $resultQuery = $this->processTermSelect($filter, $isNegation);
        } elseif (
            $filter->getType() === FilterInterface::TYPE_RANGE &&
            in_array($attribute->getBackendType(), ['decimal', 'int'], true)
        ) {
            $resultQuery = $this->processRangeNumeric($filter, $query, $attribute);
        } else {
#######################FROM HERE
            $table = $attribute->getBackendTable();
            $select = $this->connection->select();
            $ifNullCondition = $this->connection->getIfNullSql('current_store.value', 'main_table.value');

            $currentStoreId = $this->scopeResolver->getScope()->getId();

            $select->from(['e' => $this->resource->getTableName('catalog_product_entity')], ['entity_id'])
                ->join(
                    ['main_table' => $table],
                    "main_table.{$linkIdField} = e.{$linkIdField}",
                    []
                )
                ->joinLeft(
                    ['current_store' => $table],
                    'current_store.attribute_id = main_table.attribute_id AND current_store.store_id = '
                    . $currentStoreId,
                    null
                )
                ->columns([$filter->getField() => $ifNullCondition])
                ->where(
                    'main_table.attribute_id = ?',
                    $attribute->getAttributeId()
                )
                ->where('main_table.store_id = ?', Store::DEFAULT_STORE_ID)
                ->having($query);

            $resultQuery = 'search_index.entity_id IN (
                select entity_id from  ' . $this->conditionManager->wrapBrackets($select) . ' as filter
            )';
#######################TOHERE
        }

        return $resultQuery; 
}

In our case, we were importing all attributes and in a short timelpase some of them where in frontend_type=text (instead of frontend_type=multiselect). Thoses attributes states could be cached then the condition in_array($attribute->getFrontendInput(), ['select', 'multiselect'], true) is not true and we build the sub-query as long as the cache isn't cleared.

Attributes frontend_type are cached in mage---d2b_EAV_ENTITY_ATTRIBUTESCATALOG_PRODUCT cache file.

We should never execute this query in our case, so we will bypass this subquery building everytime.

I hope this will help some of you.

[acceleratemedia]

Also having this issue on 2.3.1 where the server is going down daily due to these queries.

[joshbourke]

Also having this issue on 2.3.1 where the server is going down daily...

[raidoa]

Having that issue as well. Unfortunately we haven't yet found a clean solution.
Yet currently we just kill the queries automatically after 30 seconds to keep the site running - so far it has been OK and has been the only way to keep the site up. For that we use "pt-kill".

Here's how we kill those INSERT search_tmp_* queries, maybe helps someone:
pt-kill -h hostname -u username -p password --busy-time 30 --match-state 'Sending data' --kill-busy-commands 'Query' --wait-after-kill 3 --print --kill

What to keep in mind - it's just a patch for the wound, and it might kill some other important long running queries.
Just run it initially without the --kill parameter to see what queries would get killed...monitor the behaviour for some time, tweak if required. And when you are sure you understand what's going to happen, add the --kill to actually start killing the queries.
Hopefully fix will be rolled out soon.

[tzyganu]

Additional info.
The problem (as @asebille says) it's not the actual insert, but the select to get the data. The large nubmer of joins with catalog_product_index_eav table is a real performance killer.
There is one join for every filter applied on the collection.
A solution that seems to work (actually hide the problem) was to use an integration with elasticsearch. So basically the product ids that need to go into the search_tmp table now come from elasticsearch and not a db query.

[raidoa]

@tzyganu in our case if we run any of those queries without the INSERT (so just the SELECT), they return results around 0.0460 seconds.
Thanks for the idea regarding Elasticsearch, we'll try that too.

[pmcmanaman]

If you are seeing this issue, one thing that may help is to run an OPTIMIZE on the fulltext table. There are some extra steps to optimize fulltext tables which are :

• innodb_optimize_fulltext_only needs to be enabled before https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_optimize_fulltext_only

• The number of rows optimized per run is limited by innodb_ft_num_word_optimize, so you may need to run multiple OPTIMIZE commands to cover all the rows in the full text tables.

https://dev.mysql.com/doc/refman/5.6/en/fulltext-fine-tuning.html#fulltext-optimize

[matutetandil]

I know is not a solution and is not considered to be so. But maybe could be a workaround. Since the main issue is regarding the MySQL connector for the catalog search, what we are doing is to use Elasticsearch.
As I said, this is not a solution, but for someone could be a workaround.

[shiftone242]

We have a number of stores one of which is a fairly large store containing some fairly lage configurable products (thousands of child product combination) and a number of filters. We have been experiencing problems with the above query never ending and eating up server resources when users are applying multiple filters.

Doing some digging I recorded an SQL query evaluating a large number of records

# Time: 190508 10:36:13
# User@Host: ***[****] @ localhost []
# Thread_id: 65002  Schema: ******_co_uk_m2  QC_hit: No
# Query_time: 656.043989  Lock_time: 0.000086  Rows_sent: 0  Rows_examined: 1019562170
SET timestamp=1557308173;
INSERT INTO `search_tmp_5cd2a07da8e2b4_83367814` SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT  `search_index`.`entity_id`, (((0) + (0) + (0)) * 1) AS `score` FROM `catalog_product_index_eav` AS `search_index`
 INNER JOIN `catalog_product_index_eav` AS `brands_filter` ON `search_index`.`entity_id` = `brands_filter`.`entity_id` AND `brands_filter`.`attribute_id` = 222 AND `brands_filter`.`store_id` = 1
 INNER JOIN `catalog_product_index_eav` AS `fuel_filter_filter` ON `search_index`.`entity_id` = `fuel_filter_filter`.`entity_id` AND `fuel_filter_filter`.`attribute_id` = 469 AND `fuel_filter_filter`.`store_id` = 1
 INNER JOIN `cataloginventory_stock_status` AS `stock_index` ON stock_index.product_id = search_index.entity_id AND `stock_index`.`website_id` = 0 AND `stock_index`.`stock_id` = 1
 INNER JOIN `cataloginventory_stock_status` AS `sub_products_stock_index` ON sub_products_stock_index.product_id = search_index.source_id AND `sub_products_stock_index`.`website_id` = 0 AND `sub_products_stock_index`.`stock_id` = 1
 INNER JOIN `catalog_category_product_index_store1` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (category_ids_index.category_id = 105) AND (brands_filter.value = '563') AND (fuel_filter_filter.value = '893')) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
 LIMIT 10000;

Inspecting the catalog_product_index_eav table we have multiple rows which contain the required data (due to individual child products). While i'm not sure this is the best fix for all it was a simple fix us at this time, insert a subquery into the Inner Join statements for catalog_product_index_eav reduces the number of rows and execution time drastically.

Old SQL

INNER JOIN `catalog_product_index_eav` AS `brands_filter` ON `search_index`.`entity_id` = `brands_filter`.`entity_id` AND `brands_filter`.`attribute_id` = 222 AND `brands_filter`.`store_id` = 1

New SQL

INNER JOIN (SELECT `entity_id`, `store_id`, `attribute_id`, `value` FROM `catalog_product_index_eav` WHERE `attribute_id` = 222 AND `store_id` = 1 GROUP BY CONCAT(`entity_id`,"-",`attribute_id`,"-",`store_id`,"-",`value`)) AS `brands_filter` ON `search_index`.`entity_id` = `brands_filter`.`entity_id` AND `brands_filter`.`attribute_id` = 222 AND `brands_filter`.`store_id` = 1

On my store this has cut the query time from 10min seconds to 0.09 seconds

Code changes are as follows;

Magento\CatalogSearch\Model\Search\FilterMapper\CustomAttributeFilter::apply

           $store_id = (int) $this->storeManager->getStore()->getId();
           $removeExtraChildProducts = new \Zend_Db_Expr('(SELECT `entity_id`, `store_id`, `attribute_id`, `value` FROM ' . $this->resourceConnection->getTableName('catalog_product_index_eav') . sprintf(' WHERE `attribute_id`=%s AND `store_id`=%s GROUP BY CONCAT(`entity_id`," ",`attribute_id`," ",`store_id`," ",`value`) )', $attributeId, $store_id));

            $select->joinInner(
                //[$filterJoinAlias => $this->resourceConnection->getTableName('catalog_product_index_eav')],
                [$filterJoinAlias =>  $removeExtraChildProducts],
                $this->conditionManager->combineQueries(
                    $this->getJoinConditions($attributeId, $mainTableAlias, $filterJoinAlias),
                    Select::SQL_AND
                ),
                []
);

I added this as a quick core hack for now as I dont have the time to create an override (and some methods in the parent class are marked as private!)

[social-assassin]

I've been struggling with this issue for a few weeks as well. But, good news, I come bearing a much easier fix.

This is a related issue: https://github.com/magento/magento2/issues/22587

Apply the following patch to
/Model/Search/FilterMapper/CustomAttributeFilter.php

https://bitbucket.org/muntzamersfoort/magento2-patches/raw/0ac032f30a6ab8b3f56b63d06db4c1649923f499/layered-navigation-performance.patch

[joshbourke]

Update - can confirm configuring ElasticSearch fixed the issue. It's a relatively painless process.

[SeeniBorn]

We have Magento EE 2.2.8 version with Elastic search 6.4
Recently we have upgraded from EE2.2.2 to 2.2.8 and Elastic search 2.x to 6.4
Suddenly we are facing issue with Elastic search and leads to site down issue.
NR says the following code part,

Once we switch back search to Mysql then load becomes normal.
What is cause of this kind of issue? Whether we reached any index limit in Elastic search or connection issue?

[elioermini]

very worrying database overload from 2.2.7 to 2.2.8.

[elioermini]

I've readded sprintf('%s.source_id=%s.source_id', $mainTable, $joinTable), after line 143 of \Magento\CatalogSearch\Model\Search\FilterMapper\CustomAttributeFilter so like it was on 2.2.7, monitoring now the status.

[SeeniBorn]

@elioermini Whether the issue getting resolve when you add that line?

[spleen1334]

That fix creates problems for configurable attribute filtering. Returns 0 products on some combinations.

[elioermini]

@spleen1334 I'll check the configurable issues however having a website that goes down is more critical, I usually set the attributes available for filtering as follows: Use in Layered Navigation "Filtrable with Result" and Used in Product Listing "Yes" and don't recall issues but I could be wrong. @SeeniBorn it's currently better. It's interesting to know if your indexer for Product EAV if is set update on Schedule or on save, could be affecting as well.

[SeeniBorn]

@elioermini My setting has update on Schedule for catalog search and indexer for Product EAV is Yes.
No clue how the Elastic search overloaded and site get affected.

[GardeEuro]

Anyone has news about this issue?
Upgrading magento from 2.2.3 to 2.2.8 my installation crashes when customers browse categories!

Debugging I found terrible INSERT query execution time on "search_tmp_XXXXX tables"!

[cabunar]

These commands fix the problem..

php bin/magento cache:clean
php bin/magento indexer:reindex

You can add a crontab ...
0 0 * * * /usr/bin/php /var/www/myproject/bin/magento cache:clean && /usr/bin/php /var/www/myproject/bin/magento indexer:reindex

The bin/magento setup:cron:run does not work properly..

[magento-engcom-team]

Hi @rsulym.

Thank you for your report and collaboration!

The related internal Jira ticket MAGETWO-94602 was closed as non-reproducible in 2.2.x.
It means that Magento team either unable to reproduce this issue using provided _Steps to Reproduce_ from the _Description section_ on clean or the issue has been already fixed in the scope of other tasks.

But if you still run into this problem please update or provide additional information/steps/preconditions in the _Description section_ and reopen this issue.

[pmathbliss]

This is still a problem.
If the first select is changed to a distinct sub query, everything is fixed. I am working on fixing the select in
vendor/magento/module-catalog-search/Model/Adapter/Mysql/BaseSelectStrategy/BaseSelectFullTextSearchStrategy.php

Otherwise the sub query on this site gets 350k records and then mysql sorts it and returns the top 10000(which is actually just 35.)

`

SELECT
main_select.entity_id,
SUM(score) AS relevance
FROM
(SELECT DISTINCT
search_index.entity_id,
(((0) + (0) + (0)) * 1) AS score
FROM
(select distinct entity_id, store_id, source_id FROM catalog_product_index_eav) AS search_index
INNER JOIN catalog_product_index_eav AS size_group_filter
ON search_index.entity_id = size_group_filter.entity_id
AND size_group_filter.attribute_id = 203
AND size_group_filter.store_id = 1

AND (size_group_filter.value = '1546')
INNER JOIN catalog_product_index_eav AS web_color_filter 
  ON search_index.entity_id = web_color_filter.entity_id 
  AND web_color_filter.attribute_id = 215 
  AND web_color_filter.store_id = 1 
AND (web_color_filter.value = '1931')

INNER JOIN cataloginventory_stock_status AS stock_index 
  ON stock_index.product_id = search_index.entity_id 
  AND stock_index.website_id = 0 
  AND stock_index.stock_id = 1 
INNER JOIN cataloginventory_stock_status AS sub_products_stock_index 
  ON sub_products_stock_index.product_id = search_index.source_id 
  AND sub_products_stock_index.website_id = 0 
  AND sub_products_stock_index.stock_id = 1 
INNER JOIN catalog_category_product_index_store1 AS category_ids_index 
  ON search_index.entity_id = category_ids_index.product_id 
  AND category_ids_index.store_id = '1'
  AND (
  category_ids_index.category_id = 16
)

WHERE (search_index.store_id = '1')
) AS main_select
GROUP BY entity_id
ORDER BY relevance DESC,
entity_id DESC
LIMIT 10000

`

[pmathbliss]

The fix worked perfectly. I had the wrong file.
_This does not appear to fix 2.2 and can break a search._
After the fix, I no longer see queries waiting to insert into the temp. I think this has been a performance issue for a while.

module-catalog-search/Model/Adapter/Mysql/BaseSelectStrategy/BaseSelectAttributesSearchStrategy.php

Replace the following method:

public function createBaseSelect(SelectContainer $selectContainer)
    {
        $select = $this->resource->getConnection()->select();
        $mainTableAlias = $selectContainer->isFullTextSearchRequired() ? 'eav_index' : 'search_index';

        $tableName = $this->resource->getTableName('catalog_product_index_eav');
        if($mainTableAlias != "search_index")
        {
            $tableName = new \Zend_Db_Expr("(SELECT DISTINCT  entity_id, store_id, source_id  FROM `{$tableName}`)");
        }

        $select->distinct()
            ->from(
                [$mainTableAlias => $tableName],
                ['entity_id' => 'entity_id']
            )->where(
                $this->resource->getConnection()->quoteInto(
                    sprintf('%s.store_id = ?', $mainTableAlias),
                    $this->storeManager->getStore()->getId()
                )
            );

        if ($selectContainer->isFullTextSearchRequired()) {
            $tableName = $this->scopeResolver->resolve(
                $selectContainer->getUsedIndex(),
                $selectContainer->getDimensions()
            );

            $select->joinInner(
                ['search_index' => $tableName],
                'eav_index.entity_id = search_index.entity_id',
                []
            )->joinInner(
                ['cea' => $this->resource->getTableName('catalog_eav_attribute')],
                'search_index.attribute_id = cea.attribute_id',
                []
            );
        }

        $selectContainer = $selectContainer->updateSelect($select);
        return $selectContainer;
    }

[dverkade]

The fix worked perfectly. I had the wrong file.
_This does not appear to fix 2.2 and can break a search._
After the fix, I no longer see queries waiting to insert into the temp. I think this has been a performance issue for a while.

module-catalog-search/Model/Adapter/Mysql/BaseSelectStrategy/BaseSelectAttributesSearchStrategy.php

Replace the following method:

public function createBaseSelect(SelectContainer $selectContainer)
    {
        $select = $this->resource->getConnection()->select();
        $mainTableAlias = $selectContainer->isFullTextSearchRequired() ? 'eav_index' : 'search_index';

      $tableName = $this->resource->getTableName('catalog_product_index_eav');
      if($mainTableAlias != "search_index")
      {
          $tableName = new \Zend_Db_Expr("(SELECT DISTINCT  entity_id, store_id, source_id  FROM `{$tableName}`)");
      }

        $select->distinct()
            ->from(
                [$mainTableAlias => $tableName],
                ['entity_id' => 'entity_id']
            )->where(
                $this->resource->getConnection()->quoteInto(
                    sprintf('%s.store_id = ?', $mainTableAlias),
                    $this->storeManager->getStore()->getId()
                )
            );

        if ($selectContainer->isFullTextSearchRequired()) {
            $tableName = $this->scopeResolver->resolve(
                $selectContainer->getUsedIndex(),
                $selectContainer->getDimensions()
            );

            $select->joinInner(
                ['search_index' => $tableName],
                'eav_index.entity_id = search_index.entity_id',
                []
            )->joinInner(
                ['cea' => $this->resource->getTableName('catalog_eav_attribute')],
                'search_index.attribute_id = cea.attribute_id',
                []
            );
        }

        $selectContainer = $selectContainer->updateSelect($select);
        return $selectContainer;
    }

We're running a customer on Magento 2.3.1 which seems to have the same issue. The change above doens't resolve the issue and actually brakes the search function with the following error:

Column not found: 1054 Unknown column 'eav_index.attribute_id' in 'where clause'

[pmathbliss]

This only works if search is not needed. It could be adjusted to make work
but it the source of the problem.

On Thu, Jul 18, 2019, 6:01 AM Danny Verkade - Cream <
[email protected]> wrote:

The fix worked perfectly. I had the wrong file.
This does not appear to fix 2.2 and can break a search.
After the fix, I no longer see queries waiting to insert into the temp. I
think this has been a performance issue for a while.

module-catalog-search/Model/Adapter/Mysql/BaseSelectStrategy/BaseSelectAttributesSearchStrategy.php

Replace the following method:

public function createBaseSelect(SelectContainer $selectContainer)
{
$select = $this->resource->getConnection()->select();
$mainTableAlias = $selectContainer->isFullTextSearchRequired() ? 'eav_index' : 'search_index';

  $tableName = $this->resource->getTableName('catalog_product_index_eav');
  if($mainTableAlias != "search_index")
  {
      $tableName = new \Zend_Db_Expr("(SELECT DISTINCT  entity_id, store_id, source_id  FROM `{$tableName}`)");
  }

    $select->distinct()
        ->from(
            [$mainTableAlias => $tableName],
            ['entity_id' => 'entity_id']
        )->where(
            $this->resource->getConnection()->quoteInto(
                sprintf('%s.store_id = ?', $mainTableAlias),
                $this->storeManager->getStore()->getId()
            )
        );

    if ($selectContainer->isFullTextSearchRequired()) {
        $tableName = $this->scopeResolver->resolve(
            $selectContainer->getUsedIndex(),
            $selectContainer->getDimensions()
        );

        $select->joinInner(
            ['search_index' => $tableName],
            'eav_index.entity_id = search_index.entity_id',
            []
        )->joinInner(
            ['cea' => $this->resource->getTableName('catalog_eav_attribute')],
            'search_index.attribute_id = cea.attribute_id',
            []
        );
    }

    $selectContainer = $selectContainer->updateSelect($select);
    return $selectContainer;
}

We're running a customer on Magento 2.3.1 which seems to have the same
issue. The change above doens't resolve the issue and actually brakes the
search function with the following error:

Column not found: 1054 Unknown column 'eav_index.attribute_id' in 'where clause'

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/magento/magento2/issues/15545?email_source=notifications&email_token=AAHIDOGRDRC7SCKAFAH73ADQABSTNA5CNFSM4FB4D7Y2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2IMOZA#issuecomment-512804708,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAHIDODHAHP6BJUVG2ZSNPDQABSTNANCNFSM4FB4D7YQ
.

[Adel-Magebinary]

This issue is still there in 2.3.2

[maheshWebkul721]

@magento-engcom-team , its not fixed yet?

[pmathbliss]

There needs to be a better way to tune these. There isnt going to be a one
fits all solution.

On Thu, Aug 8, 2019, 6:09 AM Mahesh Singh [e-mail] wrote:

@magento-engcom-team https://github.com/magento-engcom-team , its not
fixed yet?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/magento/magento2/issues/15545?email_source=notifications&email_token=AAHIDOCOEM6P4EUWHCNZYXTQDQLHZA5CNFSM4FB4D7Y2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD33RSVY#issuecomment-519510359,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAHIDOF5AOINPQORR7YCQNDQDQLHZANCNFSM4FB4D7YQ
.

[Adel-Magebinary]

BTW, switched to elastic search brings more problems after the full index.

1. Nav breaks
2. Filter anything by attribute goes to full shutdown.
3. Performance for index.

[pradeepnew]

Hi,
I have checked for slow queries and below query is found on slow query log. Please suggest any solution for this. I am having magento version 2.3.2

INSERT INTO search_tmp_5d50e7d779c7f7_98698615 SELECT main_select.entity_id, SUM(score) AS relevance FROM (SELECT DISTINCT search_index.entity_id, (((0) + (0) + (0)) * 1) AS score FROM catalog_product_index_eav AS search_index
INNER JOIN catalog_product_index_eav AS brand_filter ON search_index.source_id = brand_filter.source_id AND brand_filter.attribute_id = 194 AND brand_filter.store_id = 1
INNER JOIN catalog_product_index_eav AS gender_filter ON search_index.source_id = gender_filter.source_id AND gender_filter.attribute_id = 139 AND gender_filter.store_id = 1
INNER JOIN cataloginventory_stock_status AS stock_index ON stock_index.product_id = search_index.entity_id AND stock_index.website_id = 0 AND stock_index.stock_status = 1 AND stock_index.stock_id = 1
INNER JOIN cataloginventory_stock_status AS sub_products_stock_index ON sub_products_stock_index.product_id = search_index.source_id AND sub_products_stock_index.website_id = 0 AND sub_products_stock_index.stock_status = 1 AND sub_products_stock_index.stock_id = 1
INNER JOIN catalog_category_product_index_store1 AS category_ids_index ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (category_ids_index.category_id IN (47)) AND (brand_filter.value = '490') AND (gender_filter.value = '84')) AS main_select GROUP BY entity_id ORDER BY relevance DESC, entity_id DESC
LIMIT 10000;

[jaromirmuller]

We have same issue on 2.3.2 with sample data. It appears when you have 8 and more filters enabled.

[mertgokceimam]

We've been hit with this issue also today. We will apply following patch https://github.com/magento/magento2/issues/22587#issuecomment-505895318 and install use elastic search to make sure we are not effected with this in future

Magento team pushing changes that effect performance on Enterprise solution is not acceptable

[anatolij-p]

Klevu search is one viable alternative.

When active and setup properly it completely bypasses the Magento database for product listing page & search results. All data is coming from API (initial load, filtering, etc...)

[mertgokceimam]

@anatolij-p Klevu doesn't help resolving this issue we already use Klevu as front end search.

I received a patch from Magento team for this issue , we are going to test it on our staging.

[anatolij-p]

Did you configure it properly? It must use their template, then all data is via api.
Also can you share that patch from magento here?

[jaromirmuller]

I received a patch from Magento team for this issue , we are going to test it on our staging.

@mertgokceimam I'm interested in patch. Can you share it?

[mertgokceimam]

Did you configure it properly? It must use their template, then all data is via api.
Also can you share that patch from magento here?

The issue is mainly related on slow queries within layered navigation. The only way Klevu can help you is if you allow Klevu to take over your categories and layered navigation i assume.

We need to test the patch before sharing.

[R-K-Shrivastaw]

I am also facing the same problem with the Magento 2.3.0
INSERT INTOsearch_tmp_5d5af23c3c8911_81047940SELECTmain_select.entity_id, SUM(score) ASrelevanceFROM (SELECT DISTINCTsearch_index.entity_id, (((0)) * 1) ASscoreFROMcatalog_product_index_eavASsearch_index INNER JOINcatalog_product_entityASproductON product.entity_id = search_index.entity_id INNER JOINinventory_stock_1ASstock_indexON stock_index.sku = product.sku INNER JOINcatalog_category_product_index_store1AScategory_ids_indexON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (category_ids_index.category_id IN (75&&SlEEp(3)))) ASmain_selectGROUP BYentity_idORDER BYrelevanceDESC,entity_idDESC LIMIT 10000

I am wander that on a serious note, no satisfactory answer found till date. Magento Team please release a patch for the CE 2.3.0

[an4rei]

We're having the same issue with never ending INSERT QUERIES and CPU USAGE to 200% by mysqld. In order to fix it temporally, we have to either kill the query or just restart the process.
Site's TTFB increases by alot for any page and then crashes(every 18 hours or so).
Running on 2.3.2.

[euriskotech]

I'm having the same issue on a newly migrated Magento 2.3.2 install. I had everything ready for go live this week and then the database started maxing out the CPU, finally traced it to the INSERT INTO “search_tmp…” issue and reading all your comments have decided to hold off on go live and leave the Magento 1.x site running.

I guess there isn't any update on this issue being fixed by Magento?

[s00071609]

Any solution found so far?
Version 2.3.1 & 2.3.2 seem to have this issue. The serach_temp_xxxx query never completes.

[pradeepnew]

I am using magento 2.3.2 and I have removed query in mysql using kill process but after some time its showing again query search_tmp_xxxx.
Please update any solution for this

[Adel-Magebinary]

Looks like this has something to do with price index running.

1587 192.168.130.76:44296 Query 259 Sending data INSERT INTO catalog_product_index_price (entity_id, customer_group_id, website_id

It some locks things up.

After killing the process from the mysql processlist, it releases the lock.

This issue happens at 2.3.2.

[s00071609]

Hi,
I think its being caused due to search_tmp table being stuck. Rest of the pending or missed issue is due to this as this process never completes. Some in the thread has suggested that its due to Search Bots, like google bot crawling the layered navigation with urls ............com?color=red which never end and runs in loop. I have blocked bots from crawling these pages, via robot.txt file. I did this yesterday and so far i haven't had any issue. I cant say 100% but give it a try. Google doe snot need to crawl these paginated canonical pages, so you can DISALLOW in robot.txt file. Please do confirm if it solves the issue or makes it better.

Its a bug in magento 2.3 and there is no doubt about it. As it can take long time for magento to fix anything, if above helps then at least you can run your store.

[an4rei]

@s00071609 which pages exactly have you disallowed? Thanks!

[s00071609]

I have put these in robot.txt,

It will disallow all the urls created by selecting items on layered navigation, eg colour, size, brand etc.

[Adel-Magebinary]

Changing MySQL transaction isolation to READ-COMMITTED does fix the issue.

transaction-isolation = READ-COMMITTED

[euriskotech]

Does anyone know of any progress regarding this issue, I'm holding back a M1 migration to M2 because of this issue during testing, but under pressure from the client to migrate. Thanks

[mertgokceimam]

@euriskotech Magento team supplied us a patch. If you a Magento EE client , ask them the following patch : MDVA-20708_EE_2.2.9_v1.composer.patch

[euriskotech]

@euriskotech Magento team supplied us a patch. If you a Magento EE client , ask them the following patch : MDVA-20708_EE_2.2.9_v1.composer.patch

@mertgokceimam I'm running community, it's about time they released this into community edition

[an4rei]

500% cpu usage by mysql is really something I don't like seeing when I check the resources of my server. Any public fix yet?

[euriskotech]

@an4rei Just noticed that 2.3.3 has been released, scanning through release notes at the moment to see if issue addressed.

[an4rei]

@euriskotech doesn't look like they did anything regarding this issue

[euriskotech]

How can they provide a patch for EE and still be ignoring the issue in the latest 2.3.3 CE version? This makes no sense.

[vadim-shulyak]

Probably it would be useful for someone who is still fighting with the issue. I've applied it as a hotfix until the patch will be available.
Applied for Magento 2.2.3 CE

I've made the database copy of the production, and then manually performed execution of the problematic query without INSERT INTO "search_tmp..." (just took the SELECT part and try to execute it). In my case, it takes me a few minutes. So, I removed filter by filter and found the weak spot, in my case, it was INNER JOIN for visibility_filter
(since the catalog_product_index_eav table has 11 millions records).
So, I've tried to optimize the query, thanks to @shiftone242, I used your solution but for a different place.

vendor/magento/module-catalog-search/Model/Search/FilterMapper/VisibilityFilter.php:

add after: $mainTableAlias = $this->extractTableAliasFromSelect($select);

    $optimizedVisibilityQuery = new \Zend_Db_Expr('(SELECT `entity_id`, `store_id`, `attribute_id`, `value` FROM '
        . $this->resourceConnection->getTableName('catalog_product_index_eav')
        . sprintf(' WHERE `attribute_id`=%s AND `store_id`=%s GROUP BY CONCAT(`entity_id`," ",`attribute_id`," ",`store_id`," ",`value`) )', $this->getVisibilityAttributeId(), $this->storeManager->getStore()->getId())
    );

and use:
['visibility_filter' => $optimizedVisibilityQuery],

instead of:
['visibility_filter' => $this->resourceConnection->getTableName('catalog_product_index_eav')],

For me, the issue is gone. I hope this could help with your cases as well.

[anatolij-p]

@vadim-shulyak
Looks like that might actually fixed the issue

[LIXMLN]

@vadim-shulyak
Your fix doesn't work with Magento 2.3.3 CE. Search doesn't show any results

[jorgb90]

@LIXMLN Its not only add after but also replace as far as I can see and in 2.3.3 there are two occurrences for $mainTableAlias = $this->extractTableAliasFromSelect($select);

I don't know if that helps.

[LIXMLN]

@jorgb90 I removed the character from the last line (`). I had mistaken it for a dust on the screen :' )
For now it seems to work. Thanks

[LIXMLN]

@vadim-shulyak the problem persists...

| 300135 | mg2_mage476 | localhost | mg2_mage476 | Query | 5901 | Creating sort index | INSERT INTO mgdb_search_tmp_5da86051c8f898_79461193 SELECT main_select.entity_id, SUM(score) A | 0 | 14155 |
| 300353 | mg2_mage476 | localhost | mg2_mage476 | Query | 5500 | Creating sort index | INSERT INTO mgdb_search_tmp_5da861e288d5d9_60328026 SELECT main_select.entity_id, SUM(score) A | 0 | 14155 |
| 300549 | mg2_mage476 | localhost | mg2_mage476 | Query | 5131 | Creating sort index | INSERT INTO mgdb_search_tmp_5da86353dbcbf8_15765315 SELECT main_select.entity_id, SUM(score) A | 0 | 14155 |

[mertgokceimam]

@vadim-shulyak the problem persists...

| 300135 | mg2_mage476 | localhost | mg2_mage476 | Query | 5901 | Creating sort index | INSERT INTO mgdb_search_tmp_5da86051c8f898_79461193 SELECT main_select.entity_id, SUM(score) A | 0 | 14155 |
| 300353 | mg2_mage476 | localhost | mg2_mage476 | Query | 5500 | Creating sort index | INSERT INTO mgdb_search_tmp_5da861e288d5d9_60328026 SELECT main_select.entity_id, SUM(score) A | 0 | 14155 |
| 300549 | mg2_mage476 | localhost | mg2_mage476 | Query | 5131 | Creating sort index | INSERT INTO mgdb_search_tmp_5da86353dbcbf8_15765315 SELECT main_select.entity_id, SUM(score) A | 0 | 14155 |

Make sure to upgrade Amasty Layered Navigation ;)

[sebastian-virtua]

Hey guys, since the fix for CE is not there yet we've created a module that fixes this issue. It uses the idea that @vadim-shulyak posted but we overwrite one more class - in our case the custom attributes were the bigger problem. We used it on m2.2.10 and m2.3.1 and it solved our problems. If you want you can find the module here -> https://packagist.org/packages/we-are-virtua/magento2-search-fix

[Beowulf891]

@vadim-shulyak Lifesaver. Worked like a charm.

[erikhansen]

Just applying the patch from @vadim-shulyak didn't work for me. However the extension that @sebastian-virtua posted about _did_ fix my issue, as it edits one additional method.

I prefer to apply changes like this as a patch, as if the underlying code ever changes, the patch will fail to apply and you can remedy the issue. So I converted the we-are-virtua/magento2-search-fix extension to a patch, and you can find that patch here: https://gist.github.com/erikhansen/a813f8cf9fe8e9836eea467bdd205659

[unxsist]

@erikhansen Thanks! Your patch fixed the issue at once!

[alexandrosk]

I need the patch for enterprise! if anyone has it can he please share it with me is really important! Thanks!

[erikhansen]

@alexandrosk Did you try applying the patch I linked to in my previous comment? If so, did it fail to apply, or did it apply, but not work?

[alexandrosk]

@Erik-ID-89 Actually it does apply, I'm just thinking that enterprise uses all the row_ids instead of entity_ids but I'm trying to find a way to test it right now and get back to you soon.

[erikhansen]

In case it's helpful to anyone else, here is how I reproduced the issue (which was necessary to confirm my patch solved the issue):

1. I ran SHOW FULL PROCESSLIST; to see all of the hung INSERT INTO queries.
2. I inspected the queries and found the search terms that were hung (most of them appeared to be generated by bots, Bing and others)
3. I then searched my Nginx access logs for those queries.

4. I found that not all queries easily reproduced the hung queries, so I grabbed all related queries from the access logs and put them in a file, and then ran all of them against the server using siege (the Authorization header was because I was testing a stage site behind HTTP auth):

   siege -c 10 --no-parser --header="Authorization:Basic eGNoYWlyOnhjaGFpcjEyMw==" -f PATH_TO_FILE_CONTAINING_URLS.txt

5. Of all of the ~471 queries in my file, only a few dozen of them reproduced the hung queries.

[toan-tam]

@erikhansen I applied the patch that you suggested on a Magento 2.3.2 instance, but I couldn't filter because of this issue

I was able to fix that issue by adding the source_id field here

I'm adding my patch here in case someone needs it.
https://gist.github.com/toanthree/26273f83c1ccb786c311ae21dabf566c

[erikhansen]

@toanthree Thanks for sharing. I noticed the Amasty_Shopby extension in the backtrace of your first screenshot. That may have had something to do with why you had to edit your patch? Regardless, I'm glad you got it working.

[nitishsigma]

Can anyone know how to apply this fix in Magento 2.1.8 EE version? Because I have checked the above patch but those files are not available in 2.1.8 EE version.

Thanks in advance.

[kunzi]

I too have this kind of problem. Neverending similar mysql processes are making the server super slow many times a day. I have to manually kill the processes. Tried the extension provided here, but no luck. We do have plenty of attributes in use. Just upgraded to 2.3.3. Did not have this problem with 2.2.3. Already had to disable Amasty's Improved Layered Navigation as the result was immediate rise of server resource consumption , but this still is a problem many times a day.

[xeontcs]

None of the solutions worked for me as well. We have one attribute called Sale (Yes/No) which cause the issue. Removing this attribute temporary solved the issue.
Magento 2.2.8

[aholovan]

@erikhansen,
Thanks for the work on this issue. We have applied the code You provided and got the issue resolved in Magento 2.3.3 (including new versions of Amasty extensions installed).
With the patch queries like INSERT INTO search_tmp_... don't freeze any more.
But using Mysql 5.7 to run that optimized queries we have to disable ONLY_FULL_GROUP_BY sql mode. (http://johnemb.blogspot.com/2014/09/adding-or-removing-individual-sql-modes.html)

[jamie-selesti]

Does anyone know if this issue has been resolved in 2.3.4?

[acmetech2009]

Hi jamie-selesti
Seems not, I've got 2.3.4 and the same problem for over one week since upgrade from 2.2.7.
This night I'm going to apply erikhansen patch to check it.

[acmetech2009]

Hi, erikhansen's path works on 2.3.4
Thank You.

[teme12]

Gros mal

[teme12]

Bonjour

[teme12]

Good

[seffparker]

We noticed this error after upgrading MySQL to 5.7. Simply disabling the SQL mode ONLY_FULL_GROUP_BY via my.cnf permanently fixed the issue. I know it's a work-around. This is a bug with Magento2 and should be fixed by the devs.

[zw1111]

@erikhansen I applied the patch that you suggested on a Magento 2.3.2 instance, but I couldn't filter because of this issue

I was able to fix that issue by adding the source_id field here

I'm adding my patch here in case someone needs it.
https://gist.github.com/toanthree/26273f83c1ccb786c311ae21dabf566c

@toanthree Thanks! I also encountered the search filter issue and your adjustment to the @erikhansen patch worked for me!

I did not have this problem previously... I think I was using Magento OS 2.3.3, but started having the problem immediately after upgrading to Magento OS 2.3.5-p1.

[trapchik]

Just applying the patch from @vadim-shulyak didn't work for me. However the extension that @sebastian-virtua posted about _did_ fix my issue, as it edits one additional method.

I prefer to apply changes like this as a patch, as if the underlying code ever changes, the patch will fail to apply and you can remedy the issue. So I converted the we-are-virtua/magento2-search-fix extension to a patch, and you can find that patch here: https://gist.github.com/erikhansen/a813f8cf9fe8e9836eea467bdd205659

anything else need to be done after applying your patch?

thanks

[feavilan04]

We had same issue, we found problem on magento 2.3.4. Issue was fixed by using AWS ElasticSearch as catalog_search system https://devdocs.magento.com/guides/v2.4/config-guide/elasticsearch/configure-magento.html

[luckyduck]

Same issue here. Thanks to @erikhansen and @sebastian-virtua , it could be fixed. Should be fixed in the core asap.

[euriskotech]

I assume now that M2.4 requires ElasticSearch this issue is resolved?

[gilbertsohal]

I m getting same in 2.3.4 below query killing my whole server

INSERT INTO search_tmp_5f733915d1be65_45326344 SELECT main_select.entity_id, SUM(score) AS `re

Can someone help me please..

[mnatsakanyan]

I m getting same in 2.3.4 below query killing my whole server

INSERT INTO search_tmp_5f733915d1be65_45326344 SELECT main_select.entity_id, SUM(score) AS `re

Can someone help me please..

the same problem we have - any solution?

[magento-engcom-team]

The report related to technology or functionality that was removed(or not supported) from the Magento code-base.
Or cannot be fixed and delivered in the scope of this public repository.

Examples:

• Removed payment methods. Magento Commerce 2.4.0 Release Notes, Deprecation of Magento core payment integrations. All questions, issue reports, and fix for them should be addressed to the corresponding extension owners(support) on the Magento Market place page
• Mysql search. Replaced with Elasticsearch
• Not Supported browsers
• Not supported Operating systems
• Web Wizard install
• Not a bug
• etc
  

[bhaveshbalar]

INNER JOIN qz8v_cataloginventory_stock_status AS stock_index ON stock_index.product_id = search_index.entity_id AND stock_index.website_id = 0 AND stock_index.stock_status = 1 AND stock_index.stock_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=187 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS merkmale_filter ON search_index.entity_id = merkmale_filter.entity_id AND merkmale_filter.attribute_id = 187 AND merkmale_filter.store_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=169 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS stoff_filter ON search_index.entity_id = stoff_filter.entity_id AND stoff_filter.attribute_id = 169 AND stoff_filter.store_id = 1
INNER JOIN qz8v_cataloginventory_stock_status AS merkmale_filter_stock_index ON merkmale_filter_stock_index.product_id = merkmale_filter.source_id AND merkmale_filter_stock_index.website_id = 0 AND merkmale_filter_stock_index.stock_status = 1 AND merkmale_filter_stock_index.stock_id = 1
INNER JOIN qz8v_cataloginventory_stock_status AS stoff_filter_stock_index ON stoff_filter_stock_index.product_id = stoff_filter.source_id AND stoff_filter_stock_index.website_id = 0 AND stoff_filter_stock_index.stock_status = 1 AND stoff_filter_stock_index.stock_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=99 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS visibility_filter ON search_index.entity_id = visibility_filter.entity_id AND visibility_filter.attribute_id = 99 AND visibility_filter.value in ('2', '4') AND visibility_filter.store_id = '1'
INNER JOIN qz8v_catalog_category_product_index_store1 AS category_ids_index ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (category_ids_index.category_id in ('8')) AND (merkmale_filter.value IN ('5526')) AND (stoff_filter.value IN ('5448'))) AS main_select GROUP BY entity_id ORDER BY relevance DESC, entity_id DESC
LIMIT 100000 [] []
INNER JOIN qz8v_cataloginventory_stock_status AS stock_index ON stock_index.product_id = search_index.entity_id AND stock_index.website_id = 0 AND stock_index.stock_status = 1 AND stock_index.stock_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=187 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS merkmale_filter ON search_index.entity_id = merkmale_filter.entity_id AND merkmale_filter.attribute_id = 187 AND merkmale_filter.store_id = 1
INNER JOIN qz8v_cataloginventory_stock_status AS merkmale_filter_stock_index ON merkmale_filter_stock_index.product_id = merkmale_filter.source_id AND merkmale_filter_stock_index.website_id = 0 AND merkmale_filter_stock_index.stock_status = 1 AND merkmale_filter_stock_index.stock_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=99 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS visibility_filter ON search_index.entity_id = visibility_filter.entity_id AND visibility_filter.attribute_id = 99 AND visibility_filter.value in ('2', '4') AND visibility_filter.store_id = '1'
INNER JOIN qz8v_catalog_category_product_index_store1 AS category_ids_index ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (search_index.attribute_id = 187) AND (search_index.value in ('5526')) AND (category_ids_index.category_id in ('8')) AND (merkmale_filter.value IN ('5526'))) AS main_select GROUP BY entity_id ORDER BY relevance DESC, entity_id DESC
LIMIT 100000 [] []
INNER JOIN qz8v_cataloginventory_stock_status AS stock_index ON stock_index.product_id = search_index.entity_id AND stock_index.website_id = 0 AND stock_index.stock_status = 1 AND stock_index.stock_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=189 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS gewicht_filter ON search_index.entity_id = gewicht_filter.entity_id AND gewicht_filter.attribute_id = 189 AND gewicht_filter.store_id = 1
INNER JOIN qz8v_cataloginventory_stock_status AS gewicht_filter_stock_index ON gewicht_filter_stock_index.product_id = gewicht_filter.source_id AND gewicht_filter_stock_index.website_id = 0 AND gewicht_filter_stock_index.stock_status = 1 AND gewicht_filter_stock_index.stock_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=99 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS visibility_filter ON search_index.entity_id = visibility_filter.entity_id AND visibility_filter.attribute_id = 99 AND visibility_filter.value in ('2', '4') AND visibility_filter.store_id = '1'
INNER JOIN qz8v_catalog_category_product_index_store1 AS category_ids_index ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1'
INNER JOIN qz8v_catalog_product_index_price AS price_index ON search_index.entity_id = price_index.entity_id AND price_index.website_id = '1' WHERE (search_index.store_id = '1') AND (search_index.attribute_id = 189) AND (search_index.value in ('5549')) AND (category_ids_index.category_id in ('8')) AND (price_index.min_price >= '1000' AND price_index.customer_group_id = 0) AND (gewicht_filter.value IN ('5549'))) AS main_select GROUP BY entity_id ORDER BY relevance DESC, entity_id DESC
LIMIT 100000 [] []
INNER JOIN qz8v_cataloginventory_stock_status AS stock_index ON stock_index.product_id = search_index.entity_id AND stock_index.website_id = 0 AND stock_index.stock_status = 1 AND stock_index.stock_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=188 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS grosse_filter ON search_index.entity_id = grosse_filter.entity_id AND grosse_filter.attribute_id = 188 AND grosse_filter.store_id = 1
INNER JOIN qz8v_cataloginventory_stock_status AS grosse_filter_stock_index ON grosse_filter_stock_index.product_id = grosse_filter.source_id AND grosse_filter_stock_index.website_id = 0 AND grosse_filter_stock_index.stock_status = 1 AND grosse_filter_stock_index.stock_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=99 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS visibility_filter ON search_index.entity_id = visibility_filter.entity_id AND visibility_filter.attribute_id = 99 AND visibility_filter.value in ('2', '4') AND visibility_filter.store_id = '1'
INNER JOIN qz8v_catalog_category_product_index_store1 AS category_ids_index ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1'
INNER JOIN qz8v_catalog_product_index_price AS price_index ON search_index.entity_id = price_index.entity_id AND price_index.website_id = '1' WHERE (search_index.store_id = '1') AND (search_index.attribute_id = 188) AND (search_index.value in ('5538')) AND (category_ids_index.category_id in ('8')) AND (price_index.min_price <= '999.999' AND price_index.customer_group_id = 0) AND (grosse_filter.value IN ('5538'))) AS main_select GROUP BY entity_id ORDER BY relevance DESC, entity_id DESC
LIMIT 100000 [] []
INNER JOIN qz8v_cataloginventory_stock_status AS stock_index ON stock_index.product_id = search_index.entity_id AND stock_index.website_id = 0 AND stock_index.stock_status = 1 AND stock_index.stock_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=169 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS stoff_filter ON search_index.entity_id = stoff_filter.entity_id AND stoff_filter.attribute_id = 169 AND stoff_filter.store_id = 1
INNER JOIN qz8v_cataloginventory_stock_status AS stoff_filter_stock_index ON stoff_filter_stock_index.product_id = stoff_filter.source_id AND stoff_filter_stock_index.website_id = 0 AND stoff_filter_stock_index.stock_status = 1 AND stoff_filter_stock_index.stock_id = 1
INNER JOIN (SELECT entity_id, store_id, attribute_id, value FROM qz8v_catalog_product_index_eav WHERE attribute_id=99 AND store_id=1 GROUP BY CONCAT(entity_id," ",attribute_id," ",store_id," ",value) ) AS visibility_filter ON search_index.entity_id = visibility_filter.entity_id AND visibility_filter.attribute_id = 99 AND visibility_filter.value in ('2', '4') AND visibility_filter.store_id = '1'
INNER JOIN qz8v_catalog_category_product_index_store1 AS category_ids_index ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (search_index.attribute_id = 169) AND (search_index.value in ('5452')) AND (category_ids_index.category_id in ('8')) AND (stoff_filter.value IN ('5452'))) AS main_select GROUP BY entity_id ORDER BY relevance DESC, entity_id DESC
LIMIT 100000 [] []

I am facing same problem when I applying multiple filter in catalog page and I have used amasty shopby extension.

[jamie-selesti]

I am facing same problem when I applying multiple filter in catalog page and I have used amasty shopby extension.

We have the exact same issue now too and also have Amasty Shopby extension installed