Lately I'm experiencing some issues when trying to reindex catalog_product_category and/or catalog_category_product (most of the issues seem to be on this last one). The reindex process fails to execute successfully throwing the following error:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: INSERT INTO `catalog_category_product_index` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `catalog_category_product_index`.* FROM `catalog_category_product_index` ORDER BY `catalog_category_product_index`.`category_id` ASC
LIMIT 500 OFFSET 500 ON DUPLICATE KEY UPDATE `category_id` = VALUES(`category_id`), `product_id` = VALUES(`product_id`), `position` = VALUES(`position`), `is_parent` = VALUES(`is_parent`), `store_id` = VALUES(`store_id`), `visibility` = VALUES(`visibility`)

What I've tried to do to workaround this issue

I've killed the locked transactions, reset the indexer using the php bin/magento indexer:reset catalog_product_category catalog_category_product command and manually reindexed running the php bin/magento indexer:reindex catalog_product_category catalog_category_product command but from time to time I still get the error. I've also tried to execute those separately but I still got the error on catalog_category_product.

I've recreated the catalog_category_product_index, catalog_category_product_index_tmp and chron_schedule tables (drop and recreate from create syntax) - some of the solutions I've found on the internet but non of them work.

I've set the mode of catalog_category_product to execute on schedule and set it to execute via cron but it also failed - this seems to be the recommended fix on most of the Github bug reports.

Any thoughts on how can this issue be mitigated/fixed?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.