Getting error in catalog product price indexing

Question

I am facing below error while running indexing for catalog product price.

Command:

php bin/magento indexer:reindex catalog_product_price

Error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (m2ec_oct.catalog_product_index_price, CONSTRAINT CAT_PRD_IDX_PRICE_ENTT_ID_SEQUENCE_PRD_SEQUENCE_VAL FOREIGN KEY (entity_id) REFERENCES sequence_product (sequence_value) ON DELETE ), query was: INSERT INTO catalog_product_index_price (entity_id, customer_group_id, website_id, tax_class_id, price, final_price, min_price, max_price, tier_price) SELECT catalog_product_index_price_idx.entity_id, catalog_product_index_price_idx.customer_group_id, catalog_product_index_price_idx.website_id, catalog_product_index_price_idx.tax_class_id, catalog_product_index_price_idx.price, catalog_product_index_price_idx.final_price, catalog_product_index_price_idx.min_price, catalog_product_index_price_idx.max_price, catalog_product_index_price_idx.tier_price FROM catalog_product_index_price_idx ON DUPLICATE KEY UPDATE entity_id = VALUES(entity_id), customer_group_id = VALUES(customer_group_id), website_id = VALUES(website_id), tax_class_id = VALUES(tax_class_id), price = VALUES(price), final_price = VALUES(final_price), min_price = VALUES(min_price), max_price = VALUES(max_price), tier_price = VALUES(tier_price)

Please let me know if anyone has faced this issue before.

Thanks in advance.

Answer 1

Try resetting index using below command

php bin/magento indexer:reset catalog_product_price

Then try

php bin/magento indexer:reindex catalog_product_price

Answer 2

For me the sequence_product table had some missing values for some reason

mysql> insert into sequence_product (sequence_value) select entity_id from catalog_product_entity where entity_id not in(select sequence_value from sequence_product);
Query OK, 29 rows affected (0.06 sec)
Records: 29  Duplicates: 0  Warnings: 0

Running the above query added in missing entity IDs and the reindex completed successfully.