search

magento / magento2

Prior to making any Submission(s), you must sign an Adobe Contributor License Agreement, available here at: https://opensource.adobe.com/cla.html. All Submissions you make to Adobe Inc. and its affiliates, assigns and subsidiaries (collectively “Adobe”) are subject to the terms of the Adobe Contributor License Agreement.
http://www.magento.com
Open Software License 3.0
11.48k stars 9.29k forks source link

catalogrule_product and catalogrule_rule indexers stuck processing -- big query running 8+ hours now #38976

Open timothyfisherdev opened 1 month ago

timothyfisherdev commented 1 month ago

Preconditions and environment

Recently ran a delete query to remove tier pricing for a specific customer group. This removed a lot of rows and is the only thing I can think of that has changed recently.

DELETE FROM catalog_product_entity_tier_price WHERE customer_Group_id = 6

Steps to reproduce

  1. Delete tier pricing for customer group ~200k rows
  2. Reset and run indexers

Expected result

Indexers finish

Actual result

catalogrule_product and catalogrule_rule get stuck processing and never finish.

Additional information

When looking into the mysql process list with show full processlist\G;, I see two queries that have, at the time of writing, been running for 8+ hours. If I kill either one of them, the process just gets immediately gets replaced with another one running the same query. This is locking up the tables and prevents setup:upgrade from being ran due to table locks I think.

This is the query. "hasmap" is a product attribute that we have, I'm not sure why it's prefixed with `at`.

SELECT `e`.*, IF(at_has_map.value_id > 0, at_has_map.value, at_has_map_default.value) AS `has_map` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id AND product_website.website_id IN(1)
 LEFT JOIN `catalog_product_entity_int` AS `at_has_map_default` ON (`at_has_map_default`.`entity_id` = `e`.`entity_id`) AND (`at_has_map_default`.`attribute_id` = '425') AND `at_has_map_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_has_map` ON (`at_has_map`.`entity_id` = `e`.`entity_id`) AND (`at_has_map`.`attribute_id` = '425') AND (`at_has_map`.`store_id` = 1) WHERE ((e.entity_id IN((SELECT `ca_has_map`.`entity_id` FROM `catalog_product_entity_int` AS `ca_has_map` WHERE (ca_has_map.attribute_id = '425') AND (ca_has_map.value IN('0')))) AND (e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(647, 1278, 2698, 2699, 2700, 1446, 2662, 2663, 2664, 1453, 2659, 2660, 2661, 1454, 2656, 2657, 2658, 1455, 2652, 2653, 2654, 1456, 1457, 1458, 2649, 2650, 2651, 1539, 2701, 2702, 2703, 314, 2672, 2673, 2675, 320, 2680, 2681, 2682, 321, 2689, 2690, 2691, 322, 2692, 2693, 2694, 668, 2665, 2666, 2667, 669, 2668, 2669, 2670, 670, 2671, 2674, 2676, 671, 2677, 2678, 2679, 672, 2683, 2684, 2685, 673, 2686, 2687, 2688, 674, 2704, 2705, 2706)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(315, 1079, 1080, 948, 950, 951, 952, 953, 954, 955, 957, 958)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(317)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(900, 1293, 1294, 651, 655, 659, 901, 1432, 1434, 1435, 1436, 1444, 902, 1437, 904, 905)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(319, 1000, 1953, 999)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(345, 1255, 1315, 1465, 1466, 1316)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(334, 1001, 1298, 1297, 652, 657, 1247, 662, 1022)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(343, 1105, 1493, 1494, 1430, 1495, 1496, 1497, 1451, 654, 1317, 1318)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(346, 1075, 1076, 1077, 1078, 964, 1189, 1190, 967, 1181, 1182, 1183, 972, 975, 977)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(304, 1032, 1033, 1034, 1037, 1039, 1040, 1041, 1042, 1043, 1053, 980)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(1940)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(300, 1148, 1275, 1276, 1299, 912, 1253, 1254, 1413, 1414, 1415, 1416, 1417, 913, 914, 915, 1086)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(294, 3130, 916, 1090, 1096, 1097, 1100, 1101, 1300, 917, 1301, 3121)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(306, 1012, 1094, 1095, 1108, 1109, 1102, 1104, 1106, 1107, 1110, 1111)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(287, 1084, 1112)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(1085)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(1016, 1687, 1688, 1689, 1690, 1691, 1692, 1693, 1694, 1695, 1696)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(1543, 1566, 1567, 1568, 1569, 1570)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(1612, 1613, 1614, 1615, 3131)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(1942)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(1949)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(1951, 3120)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(1963)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(2610, 3025, 3026)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(2632, 1944, 1945, 2608)))) AND e.entity_id IN((SELECT `cat`.`product_id` FROM `catalog_category_product` AS `cat` WHERE (cat.category_id IN(2648, 1946, 3122, 3123, 1947, 1948)))))))

The only thing that I have done recently is delete tier pricing for a customer group using SQL as mentioned above.

The query above has been running for almost 9 hours now. I've also disconnected all connections to the database temporarily and removed all public web traffic, so Magento is the only thing touching it, which is why I'm pretty sure the query is coming from the indexers.

Release note

No response

Triage and priority

m2-assistant[bot] commented 1 month ago

Hi @timothyfisherdev. Thank you for your report. To speed up processing of this issue, make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, Add a comment to the issue: