Patch for `INSERT INTO search_tmp_` search issue: https://github.com/magento/magento2/issues/15545

_README.md

Inspired by the we-are-virtua/magento2-search-fix repo and @vadim-shulyak's comment

Apply patch using composer-patches: https://www.classyllama.com/blog/create-apply-patches-magento-2#applying_patches

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.

composer.json5

{
    "name": "magento/project-community-edition",
    // …
    // After installing https://github.com/cweagans/composer-patches, make this change to your composer.json file and then 
    // run `composer install` and `composer update --lock`
    "extra": {
        "magento-force": "override",
        "composer-exit-on-patch-failure": true,
        "patches": {
            "magento/module-catalog-search": {
                "Fix hanging `INSERT INTO search_tmp_` query - Patch created by referencing http://bit.ly/2OcN8a4 and http://bit.ly/2QN3p7d": "patches/composer/module-catalog-search-fix-insert-into-hanging.patch"
            }
        }
    }
}

module-catalog-search-fix-insert-into-hanging.patch

Index: Model/Search/FilterMapper/CustomAttributeFilter.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- Model/Search/FilterMapper/CustomAttributeFilter.php	(date 1574535757476)
+++ Model/Search/FilterMapper/CustomAttributeFilter.php	(date 1574535757476)
@@ -98,8 +98,21 @@
 
             $attributes[] = $attributeId;
 
+            // BEGIN EDIT - Fix hanging `INSERT INTO search_tmp_` queries
+            // Patch created by referencing http://bit.ly/2OcN8a4 and http://bit.ly/2QN3p7d
+            $optimizedSelectQuery = 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,
+                    $this->storeManager->getStore()->getId()
+                )
+            );
+
             $select->joinInner(
-                [$filterJoinAlias => $this->resourceConnection->getTableName('catalog_product_index_eav')],
+                [$filterJoinAlias => $optimizedSelectQuery],
+                // END EDIT
                 $this->conditionManager->combineQueries(
                     $this->getJoinConditions($attributeId, $mainTableAlias, $filterJoinAlias),
                     Select::SQL_AND

Index: Model/Search/FilterMapper/VisibilityFilter.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- Model/Search/FilterMapper/VisibilityFilter.php	(date 1574535765359)
+++ Model/Search/FilterMapper/VisibilityFilter.php	(date 1574535765359)
@@ -118,9 +118,17 @@
     private function applyFilterByJoin(FilterInterface $filter, Select $select)
     {
         $mainTableAlias = $this->extractTableAliasFromSelect($select);
+
+        // BEGIN EDIT - Fix hanging `INSERT INTO search_tmp_` queries
+        // Patch created by referencing http://bit.ly/2OcN8a4 and http://bit.ly/2QN3p7d
+        $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())
+        );

         $select->joinInner(
-            ['visibility_filter' => $this->resourceConnection->getTableName('catalog_product_index_eav')],
+            ['visibility_filter' => $optimizedVisibilityQuery],
+            // END EDIT
             $this->conditionManager->combineQueries(
                 [
                     sprintf('%s.entity_id = visibility_filter.entity_id', $mainTableAlias),

I install this patch and it care of the 1205 error but now I main.CRITICAL: SQLSTATE[42S22]: Column not found: 1054 Unknown column

Every second in they system and exception logs

The patch above is for Open Source. If you're using Commerce, try replacing instances of entity_id with row_id.

Hi Erik, Thanks for the fast response. I am on open source. I tried backing it out and ending up restoring a backup. Randy From: Erik Hansen [mailto:notifications@github.com] Sent: Monday, June 29, 2020 11:05 AM To: erikhansen Cc: Randy Rubinstein; Comment Subject: Re: erikhansen/_README.md @erikhansen commented on this gist.

…

________________________________ The patch above is for Open Source. If you're using Commerce, try replacing instances of entity_id with row_id. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<https://gist.github.com/a813f8cf9fe8e9836eea467bdd205659#gistcomment-3358295>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AQDRBINQ5DSBWH26BEFPCHTRZCUQFANCNFSM4OK4I35A>.