0

I get the error:

Zend_Db_Statement_Exception' with message 'SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1' in lib/Zend/Db/Statement/Pdo.php:234

I run the indexing at from the command line. I have tried truncating the following tables:

truncate catalog_product_index_price_bundle_idx;
truncate catalog_product_index_price_bundle_opt_idx;
truncate catalog_product_index_price_bundle_sel_idx;
truncate catalog_product_index_price_cfg_opt_agr_idx;
truncate catalog_product_index_price_cfg_opt_idx;
truncate catalog_product_index_price_downlod_idx;
truncate catalog_product_index_price_final_idx;
truncate catalog_product_index_price_idx;
truncate catalog_product_index_price_opt_agr_idx;
truncate catalog_product_index_price_opt_idx;

I have also tried truncating the catalog_produce_flat_1

But indexing with php indexer.php --reindex catalog_product_price still doesn't work.

Here is the full insert

 INSERT INTO `magento_catalog_product_index_price_bundle_idx` SELECT `e`.`entity_id`, `cg`.`customer_group_id`, `cw`.`website_id`, IF(IF(IFNULL(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value) IS NOT NULL, IF(IFNULL(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value), 0) AS `tax_class_id`, 1 AS `price_type`, 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(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= 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(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0 AND ta_special_price.value > 0 AND ta_special_price.value < 100 , ta_special_price.value, 0) AS `special_price`, tp.min_price AS `tier_percent`, IF(ta_price.value IS NULL, 0, ta_price.value) AS `orig_price`, IF(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(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= 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(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0 AND ta_special_price.value > 0 AND ta_special_price.value < 100 , ta_special_price.value, 0) > 0, ROUND(ta_price.value * (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(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= 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(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0 AND ta_special_price.value > 0 AND ta_special_price.value < 100 , ta_special_price.value, 0)  / 100), 4), ta_price.value) AS `price`, IF(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(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= 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(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0 AND ta_special_price.value > 0 AND ta_special_price.value < 100 , ta_special_price.value, 0) > 0, ROUND(ta_price.value * (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(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= 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(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0 AND ta_special_price.value > 0 AND ta_special_price.value < 100 , ta_special_price.value, 0)  / 100), 4), ta_price.value) AS `min_price`, IF(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(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= 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(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0 AND ta_special_price.value > 0 AND ta_special_price.value < 100 , ta_special_price.value, 0) > 0, ROUND(ta_price.value * (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(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) <= 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(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) >= cwd.website_date, 1, 0)) > 0 AND ta_special_price.value > 0 AND ta_special_price.value < 100 , ta_special_price.value, 0)  / 100), 4), ta_price.value) AS `max_price`, IF(tp.min_price IS NOT NULL, ROUND(ta_price.value - (ta_price.value * (tp.min_price / 100)), 4), NULL) AS `tier_price`, IF(tp.min_price IS NOT NULL, ROUND(ta_price.value - (ta_price.value * (tp.min_price / 100)), 4), NULL) AS `base_tier` FROM `magento_catalog_product_entity` AS `e`
 CROSS JOIN `magento_customer_group` AS `cg`
 CROSS JOIN `magento_core_website` AS `cw`
 INNER JOIN `magento_core_store_group` AS `csg` ON csg.group_id = cw.default_group_id
 INNER JOIN `magento_core_store` AS `cs` ON cs.store_id = csg.default_store_id
 INNER JOIN `magento_catalog_product_website` AS `pw` ON pw.product_id = e.entity_id AND pw.website_id = cw.website_id
 INNER JOIN `magento_catalog_product_index_website` AS `cwd` ON cw.website_id = cwd.website_id
 LEFT JOIN `magento_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 `magento_catalog_product_entity_int` AS `tad_status` ON tad_status.entity_id = e.entity_id AND tad_status.attribute_id = 80 AND tad_status.store_id = 0
 LEFT JOIN `magento_catalog_product_entity_int` AS `tas_status` ON tas_status.entity_id = e.entity_id AND tas_status.attribute_id = 80 AND tas_status.store_id = cs.store_id
 LEFT JOIN `magento_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 = 81 AND tad_tax_class_id.store_id = 0
 LEFT JOIN `magento_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 = 81 AND tas_tax_class_id.store_id = cs.store_id
 INNER JOIN `magento_catalog_product_entity_int` AS `ta_price_type` ON ta_price_type.entity_id = e.entity_id AND ta_price_type.attribute_id = 446 AND ta_price_type.store_id = 0
 LEFT JOIN `magento_catalog_product_entity_decimal` AS `ta_price` ON ta_price.entity_id = e.entity_id AND ta_price.attribute_id = 60 AND ta_price.store_id = 0
 LEFT JOIN `magento_catalog_product_entity_decimal` AS `ta_special_price` ON ta_special_price.entity_id = e.entity_id AND ta_special_price.attribute_id = 61 AND ta_special_price.store_id = 0
 LEFT JOIN `magento_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 = 62 AND tad_special_from_date.store_id = 0
 LEFT JOIN `magento_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 = 62 AND tas_special_from_date.store_id = cs.store_id
 LEFT JOIN `magento_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 = 63 AND tad_special_to_date.store_id = 0
 LEFT JOIN `magento_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 = 63 AND tas_special_to_date.store_id = cs.store_id
 INNER JOIN `magento_cataloginventory_stock_status` AS `ciss` ON ciss.product_id = e.entity_id AND ciss.website_id = cw.website_id WHERE (e.type_id='bundle') AND (IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value)=1) AND (ta_price_type.value=1) AND (ciss.stock_status = 1) ON DUPLICATE KEY UPDATE `tax_class_id` = VALUES(`tax_class_id`), `price_type` = VALUES(`price_type`), `special_price` = VALUES(`special_price`), `tier_percent` = VALUES(`tier_percent`), `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`), `group_price_percent` = VALUES(`group_price_percent`)

And another related error:

 Product Prices index process unknown error:, `base_group_price` = VALUES(`base_group_price`), `group_price_percent` = VALUES(`group_price_percent`)
2
  • Are you using a module for your config products? SimpleConfigurableProducts? Commented Feb 5, 2016 at 7:56
  • No I am not using a module like that for configurable products. Commented Feb 5, 2016 at 8:34

2 Answers 2

0

if you did not install any foreign modules lately, this looks like a db upgrade issue.

If you did not update magento lately, this really looks like an external module issue which manipulated your db and which would actually need deeper insight in your magento instance

2
  • I did do an upgrade from 1.11.1.0 to 1.12.0.2 - I am just having trouble tracking down what table is the problem. Commented Feb 10, 2016 at 23:22
  • oh ok, thats enterprise edition. can't really help you with this other then saying that this upgrade seems to have changed something and some module might not have recognized that :x sry, im only using the community edition so i have no better explanation Commented Feb 10, 2016 at 23:27
0

I finally fixed this issue by:

  1. Rolling back to the previous version of Magento before this happened.
  2. Using SQLyog trial which allowed me to compare my old database before upgrade to the current database and do a Schema Synchronization which repaired the tables so the insert could work.

Hope this helps someone else.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.