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
When looking into the mysql process list with show full processlistG;, 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. “has_map” 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 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.