0

I am mid-build of a new project (mag v1.9.1.1) and a primary key error occurred whilst adding a new test product. Since then I am unable to reindex the prices. I have now deleted ALL products but this error still occurs when calling reindex for prices. "Cannot initialize the indexer process". I have not modified the indexing function in any way. All other indexes work fine. Here is the log error. There are 4 store ids (3,5,6,7), in addition to the default store (1).

Anyone have any quick ideas on what table to tweak/truncate to get this working?

Exception message: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1,

query was:

INSERT INTO `catalog_product_index_price_final_idx` 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`,
IF(IFNULL(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value) AS `orig_price`, 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 IF(IFNULL(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < IF(IFNULL(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value), IF(IFNULL(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), IF(IFNULL(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)) AS `price`, 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 IF(IFNULL(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < IF(IFNULL(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value), IF(IFNULL(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), IF(IFNULL(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)) AS `min_price`, 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 IF(IFNULL(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value) < IF(IFNULL(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value), IF(IFNULL(tas_special_price.value_id, -1) > 0, tas_special_price.value, tad_special_price.value), IF(IFNULL(tas_price.value_id, -1) > 0, tas_price.value, tad_price.value)) AS `max_price`, `tp`.`min_price` AS `tier_price`, `tp`.`min_price` AS `base_tier`, cs.store_id AS `store_id` FROM `catalog_product_entity` AS `e`
 CROSS JOIN `customer_group` AS `cg`
 CROSS JOIN `core_website` AS `cw`
 INNER JOIN `catalog_product_index_website` AS `cwd` ON cw.website_id = cwd.website_id
 INNER JOIN `core_store_group` AS `csg` ON csg.website_id = cw.website_id
 INNER JOIN `core_store` AS `cs` ON csg.group_id = cs.group_id AND cs.store_id != 0
 INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = e.entity_id AND pw.website_id = cw.website_id
 LEFT JOIN `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 `catalog_product_entity_int` AS `tad_status` ON tad_status.entity_id = e.entity_id AND tad_status.attribute_id = 96 AND tad_status.store_id = 0
 LEFT JOIN `catalog_product_entity_int` AS `tas_status` ON tas_status.entity_id = e.entity_id AND tas_status.attribute_id = 96 AND tas_status.store_id = cs.store_id
 LEFT JOIN `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 = 121 AND tad_tax_class_id.store_id = 0
 LEFT JOIN `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 = 121 AND tas_tax_class_id.store_id = cs.store_id
 LEFT JOIN `catalog_product_entity_decimal` AS `tad_price` ON tad_price.entity_id = e.entity_id AND tad_price.attribute_id = 75 AND tad_price.store_id = 0
 LEFT JOIN `catalog_product_entity_decimal` AS `tas_price` ON tas_price.entity_id = e.entity_id AND tas_price.attribute_id = 75 AND tas_price.store_id = cs.store_id
 LEFT JOIN `catalog_product_entity_decimal` AS `tad_special_price` ON tad_special_price.entity_id = e.entity_id AND tad_special_price.attribute_id = 76 AND tad_special_price.store_id = 0
 LEFT JOIN `catalog_product_entity_decimal` AS `tas_special_price` ON tas_special_price.entity_id = e.entity_id AND tas_special_price.attribute_id = 76 AND tas_special_price.store_id = cs.store_id
 LEFT JOIN `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 = 77 AND tad_special_from_date.store_id = 0
 LEFT JOIN `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 = 77 AND tas_special_from_date.store_id = cs.store_id
 LEFT JOIN `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 = 78 AND tad_special_to_date.store_id = 0
 LEFT JOIN `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 = 78 AND tas_special_to_date.store_id = cs.store_id
 INNER JOIN `cataloginventory_stock_status` AS `ciss` ON ciss.product_id = e.entity_id AND ciss.website_id = cw.website_id WHERE (e.type_id='downloadable') AND (IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value)=1) AND (ciss.stock_status = 1) ON DUPLICATE KEY UPDATE `tax_class_id` = VALUES(`tax_class_id`), `orig_price` = VALUES(`orig_price`), `price` = VALUES(`price`), `min_price` = VALUES(`min_price`), `max_price` = VALUES(`max_price`), `tier_price` = VALUES(`tier_price`), `base_tier` = VALUES(`base_tier`), `group_price` = VALUES(`group_price`), `base_group_price` = VALUES(`base_group_price`)

UPDATE: I have seen that there are two fields in the DB table "catalog_product_index_price_final_idx" that appear to not be in the INSERT statement: "group_price" and "base_group_price". Now what is the solution..?

  • I do understand the issue with "insert value list does not match column list" but, as this functionality is part of the Mage core I am wondering why and what can be done to fix it. – Jon Holland Aug 12 '15 at 8:31
  • Your problem has already been solved here magento.stackexchange.com/questions/69025/… – medina Feb 4 '16 at 3:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.