Hi allen,

Ik heb een probleem met het herindexeren van de producten in Magento. Het probleem is ontstaan sinds het verwijderen van producten via een SQLQuery. Tabellen als catalog_product_index_price zijn nog gevuld, maar legen ervan verhelpt niet het probleem.

De error die hij geeft is als volgt:

Product Prices index process unknown error:
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`nutritionr_db`.`catalog_product_index_group_pric e`, CONSTRAINT `FK_CAT_PRD_IDX_GROUP_PRICE_ENTT_ID_CAT_PRD_ENTT_E NTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity)' in /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#5 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#6 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(498): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#7 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(380): Mage_Catalog_Model_Resource_Product_Indexer_Price->_prepareGroupPriceIndex()
#8 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindexAll()
#9 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#11 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#12 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/shell/indexer.php(216): Mage_Shell_Compiler->run()
#13 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`nutritionr_db`.`catalog_product_index_group_pric e`, CONSTRAINT `FK_CAT_PRD_IDX_GROUP_PRICE_ENTT_ID_CAT_PRD_ENTT_E NTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity), query was: 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`)' in /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ca...', Array)
#4 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ca...', Array)
#5 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(498): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ca...')
#6 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(380): Mage_Catalog_Model_Resource_Product_Indexer_Price->_prepareGroupPriceIndex()
#7 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindexAll()
#8 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
#9 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#10 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#11 /home/nutritionr/domains/nutritionreloaded.brancomdev.nl/public_html/shell/indexer.php(216): Mage_Shell_Compiler->run()
#12 {main}

Iemand die hierbij kan helpen / adviseren? :)

Bij voorbaat dank,

Niels

Welke query heb je uitgevoerd en waarom?
Je kunt de producten toch ook verwijderen vanuit de admin...

Beste R van Breugel,

Bedankt voor je antwoord. Ik heb het zelf niet uitgevoerd maar een collega van mij. Hij geeft aan dat hij het via een query heeft uitgevoerd omdat hij dan niet overige tabellen truncate.

PS: Het indexatie probleem gaat om productprijzen die hij niet kan indexeren.

Hieronder de query:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `rating_option_vote`;
TRUNCATE TABLE `rating_option_vote_aggregated`;
TRUNCATE TABLE `review`;
TRUNCATE TABLE `review_detail`;
TRUNCATE TABLE `review_entity_summary`;
TRUNCATE TABLE `review_store`;
INSERT INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sel l'),(5,'cross_sell');
INSERT INTO `catalog_product_link_attribute`(`product_link_att ribute_id`,`link_type_id`,`product_link_attribute_ code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4, 'position','int'),(4,5,'position','int'),(6,1,'qty ','decimal'),(7,3,'position','int'),(8,3,'qty','de cimal');
INSERT INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');
TRUNCATE TABLE `catalog_product_entity`;
SET FOREIGN_KEY_CHECKS = 1;

Opgelost. In een tabel stond nog een verwijzing naar een product die niet bestaat... Topic mag gesloten / verwijderd worden!

@admin,

Kan dit bericht verwijderd worden? Hij word nu ge-indexeert in Google..