reindex catalog_category_product issues after migration from M1 to M2

Question

After migration from M1 to M2 we get the following error when we run the following command

bin/magento index:reindex catalog_category_product 

How can we solve these issues?

.........58486', '58487', '58488', '58489', '58490', '58491', '58492', '58493', '58494', '58495', '58496', '58497',
'58498', '58499', '58500', '58501', '58502', '58503', '58504', '58505', '58506', '58507', '58508', '58509', '58510', '58511', '58512', '58513', '58514', '585 16', '58517', '58518', '58519', '58520', '58521', '58522', '58523', '58524', '58525', '58526', '58527', '58528', '58529', '58530', '58531', '58532', '58533', '58534', '58535', '58536', '58537', '58539', '58540', '58541', '58542', '58543', '58544', '58545', '58546', '58547', '58548', '58549', '58550', '58551', '5855
2', '58553', '58554', '58555', '58556', '58557', '58558', '58559', '58560', '58561', '58562', '58563', '58565', '58566', '58567', '58568')) GROUP BY cp.ent ity_id ON DUPLICATE KEY UPDATE category_id = VALUES(category_id), product_id = VALUES(product_id), position = VALUES(position), is_parent = VAL UES(is_parent), store_id = VALUES(store_id), visibility = VALUES(visibility)

Answer 1

So far my understanding,You have some products ids at catalog_category_product or catalog_category_product_index, catalog_category_product_index_replica and these tables Magento save categories and products relation.

1. Issue not existing Product at category and products relation table

which has some product ids does not exist at catalog_product_entity but does not exist at those categories and products relation tables. So you have to remove/delete the product from categories and products relation table.

You need to run below:

delete from catalog_category_product where product_id not in (select entity_id from catalog_product_entity)

2. Issue Duplicate Index key:

Magento has created an index key combine of CATALOG_CATEGORY_PRODUCT_CATEGORY_ID_PRODUCT_ID on catalog_category_product table which has some duplicate record.

catalog_category_product_index has an database index key CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY combine of field product_id,store_id,category_id,visibility, You have to find out those record and remove from respective tables.

Answer 2

Check .log, may you have innodb_buffer_pool_size problem, you can fix this by server settings.