0

I have an issue on reindexing product prices I was able to catch this

string(727) "INSERT INTO catalog_product_index_group_price SELECT gp.entity_id, cg.customer_group_id, cw.website_id, MIN(IF(gp.website_id = 0, ROUND(gp.value * cwd.rate, 4), gp.value)) FROM catalog_product_entity_group_price AS gp INNER JOIN customer_group AS cg ON gp.all_groups = 1 OR (gp.all_groups = 0 AND gp.customer_group_id = cg.customer_group_id) INNER JOIN core_website AS cw ON gp.website_id = 0 OR gp.website_id = cw.website_id INNER JOIN catalog_product_index_website AS cwd ON cw.website_id = cwd.website_id WHERE (cw.website_id != 0) GROUP BY gp.entity_id, cg.customer_group_id, cw.website_id ON DUPLICATE KEY UPDATE price = VALUES(price), value_type = VALUES(value_type)"

In catalog_product_index_group_price there are standard 4 columns, but now I have 5 5 value_type varchar(48) utf8_general_ci No fixed

I do not know where it appeared from or what generates it. I have tested all modules on a new Magento installation and all is ok. No module upgrades database.

Speedy
  • 11
  • 2

2 Answers2

0

Remove the LOCKS directory from the var/ folder Remove the cache directory in the var/ folder

If you still are having a problem you can truncate your index tables for price Some examples are : (You can view your index tables and add or remove)

  • 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;
Anas Mansuri
  • 159
  • 1
  • 2
  • 13
  • Sorry but nothing helped me. I have cleared all var folder before posting here. I have installed a new magento store with all extensions but the column value_type in catalog_product_index_group_price is not present. OK. I can delete that but the SQL still transmits it as a component of sql command line. So the question is who is sending that variable to ON DUPLICATE KEY UPDATE. – Speedy Aug 04 '15 at 16:40
0

I came across this post because I was facing a reindex of 'catalog_product_price' too. I've fixed it by doing:

1. Enable db logging

edit lib/Varien/Db/Adapter/Pdo/Mysql.php

change protected $_debug to true;

2. Reindex the corrupted index again and pull the whole SQL query + error from var/debug/pdo_mysql.log

You'll see probably something like this

EXCEPTION exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (db.catalog_product_index_group_price, CONSTRAINT FK_M_CAT_PRD_IDX_GROUP_PRICE_ENTT_ID_M_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (entity_id) REFERENCES catalog_product_entity )' in /www/sites/example.com/files/html/lib/Zend/Db/Statement/Pdo.php:228

3. Get more information about the failing constraint by looking at the table structure:

run show create table catalog_product_index_group_price;

You'll see probably something like this

CONSTRAINT FK_M_CAT_PRD_IDX_GROUP_PRICE_ENTT_ID_M_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (entity_id) REFERENCES catalog_product_entity (entity_id) ON DELETE CASCADE ON UPDATE CASCADE,

4. Find out which entity_id is missing in the catalog_product_entity table

run SELECT cpigp.* FROM catalog_product_entity_group_price cpigp LEFT JOIN catalog_product_entity p ON cpigp.entity_id = p.entity_id WHERE ISNULL(p.entity_id);

5. Backup the corrupted table and delete the missing product

run DELETE FROM catalog_product_entity_group_price WHERE entity_id=XXXXX;

6. Reindex

Stefan
  • 8,456
  • 3
  • 29
  • 38