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: 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
I've recreated the
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?