We are recently upgraded our Magento 2 website from 2.1.5 to 2.1.10. Now when we try to cancel the order from admin, we are facing “catalog_product_index_price_cfg_opt_agr_tmp is full” issue. What is the use of this table?. We have 2 lakhs of product in our site.
SQLSTATE[HY000]: General error: 1114 The table 'ya_catalog_product_index_price_cfg_opt_agr_tmp' is full, query was: INSERT INTO `ya_catalog_product_index_price_cfg_opt_agr_tmp` SELECT `sub`.`parent_id`, `sub`.`entity_id`, `sub`.`customer_group_id`, `sub`.`website_id`, `sub`.`price`, `sub`.`tier_price` FROM (SELECT `e`.`entity_id`, `cg`.`customer_group_id`, `cw`.`website_id`, IF(IFNULL(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value) AS `tax_class_id`, IFNULL((ta_price.value), 0) AS `orig_price`, IFNULL((IF(IF(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= DATE(cwd.website_date), 1, 0)) > 0 AND IF(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= DATE(cwd.website_date), 1, 0)) > 0 AND ta_special_price.value < ta_price.value, ta_special_price.value, ta_price.value)), 0) AS `price`, IFNULL((IF(IF(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= DATE(cwd.website_date), 1, 0)) > 0 AND IF(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= DATE(cwd.website_date), 1, 0)) > 0 AND ta_special_price.value < ta_price.value, ta_special_price.value, ta_price.value)), 0) AS `min_price`, IFNULL((IF(IF(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= DATE(cwd.website_date), 1, 0)) > 0 AND IF(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= DATE(cwd.website_date), 1, 0)) > 0 AND ta_special_price.value < ta_price.value, ta_special_price.value, ta_price.value)), 0) AS `max_price`, tp.min_price AS `tier_price`, tp.min_price AS `base_tier`, `le`.`entity_id` AS `parent_id` FROM `ya_catalog_product_entity` AS `e` CROSS JOIN `ya_customer_group` AS `cg` CROSS JOIN `ya_store_website` AS `cw` INNER JOIN `ya_catalog_product_index_website` AS `cwd` ON cw.website_id = cwd.website_id INNER JOIN `ya_store_group` AS `csg` ON csg.website_id = cw.website_id AND cw.default_group_id = csg.group_id INNER JOIN `ya_store` AS `cs` ON csg.default_store_id = cs.store_id AND cs.store_id != 0 INNER JOIN `ya_catalog_product_website` AS `pw` ON pw.product_id = e.entity_id AND pw.website_id = cw.website_id LEFT JOIN `ya_catalog_product_index_tier_price` AS `tp` ON tp.entity_id = e.entity_id AND tp.website_id = cw.website_id AND tp.customer_group_id = cg.customer_group_id INNER JOIN `ya_catalog_product_entity_int` AS `tad_status` ON tad_status.entity_id = e.entity_id AND tad_status.attribute_id = 98 AND tad_status.store_id = 0 LEFT JOIN `ya_catalog_product_entity_int` AS `tas_status` ON tas_status.entity_id = e.entity_id AND tas_status.attribute_id = 98 AND tas_status.store_id = cs.store_id LEFT JOIN `ya_catalog_product_entity_int` AS `tad_tax_class_id` ON tad_tax_class_id.entity_id = e.entity_id AND tad_tax_class_id.attribute_id = 149 AND tad_tax_class_id.store_id = 0 LEFT JOIN `ya_catalog_product_entity_int` AS `tas_tax_class_id` ON tas_tax_class_id.entity_id = e.entity_id AND tas_tax_class_id.attribute_id = 149 AND tas_tax_class_id.store_id = cs.store_id LEFT JOIN `ya_catalog_product_entity_decimal` AS `ta_price` ON ta_price.entity_id = e.entity_id AND ta_price.attribute_id = 78 AND ta_price.store_id = 0 LEFT JOIN `ya_catalog_product_entity_decimal` AS `ta_special_price` ON ta_special_price.entity_id = e.entity_id AND ta_special_price.attribute_id = 79 AND ta_special_price.store_id = 0 LEFT JOIN `ya_catalog_product_entity_datetime` AS `tad_special_from_date` ON tad_special_from_date.entity_id = e.entity_id AND tad_special_from_date.attribute_id = 80 AND tad_special_from_date.store_id = 0 LEFT JOIN `ya_catalog_product_entity_datetime` AS `tas_special_from_date` ON tas_special_from_date.entity_id = e.entity_id AND tas_special_from_date.attribute_id = 80 AND tas_special_from_date.store_id = cs.store_id LEFT JOIN `ya_catalog_product_entity_datetime` AS `tad_special_to_date` ON tad_special_to_date.entity_id = e.entity_id AND tad_special_to_date.attribute_id = 81 AND tad_special_to_date.store_id = 0 LEFT JOIN `ya_catalog_product_entity_datetime` AS `tas_special_to_date` ON tas_special_to_date.entity_id = e.entity_id AND tas_special_to_date.attribute_id = 81 AND tas_special_to_date.store_id = cs.store_id INNER JOIN `ya_catalog_product_super_link` AS `l` ON l.product_id = e.entity_id INNER JOIN `ya_catalog_product_entity` AS `le` ON le.entity_id = l.parent_id WHERE (IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value)=1)) AS `sub` ON DUPLICATE KEY UPDATE `price` = VALUES(`price`), `tier_price` = VALUES(`tier_price`)