Everytime that we run php /var/www/html/bin/magento indexer:reindex catalog_category_product after 40 or maybe 160 minutes the command return:
Category Products indexer process unknown 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 `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, ccp.position + 10000 AS `position`, 0 AS `is_parent`, 3 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
INNER JOIN `temp_catalog_category_tree_index_307e15d9` AS `cc2` ON cc2.parent_id = cc.entity_id AND cc.entity_id NOT IN (1)
INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child_id
INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id AND (cpe.created_in <= '1577761200' AND cpe.updated_in > '1577761200')
INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.row_id = cpe.row_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 97
LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.row_id = cpe.row_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 3
INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.row_id = cpe. row_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 99
LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.row_id = cpe.row_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 3
INNER JOIN `catalog_category_entity_int` AS `ccad` ON ccad.row_id = cc.row_id AND ccad.store_id = 0 AND ccad.attribute_id = 54
LEFT JOIN `catalog_category_entity_int` AS `ccas` ON ccas.row_id = cc.row_id AND ccas.attribute_id = ccad.attribute_id AND ccas.store_id = 3
LEFT JOIN `catalog_product_relation` AS `relation` ON cpe.row_id = relation.parent_id
LEFT JOIN `catalog_product_entity` AS `relation_product_entity` ON relation.child_id = relation_product_entity.entity_id AND (relation_product_entity.created_in <= '1577761200' AND relation_product_entity.updated_in > '1577761200')
LEFT JOIN `catalog_product_entity_int` AS `child_cpsd` ON child_cpsd.row_id = relation_product_entity.row_id AND child_cpsd.store_id = 0 AND child_cpsd.attribute_id = 97
LEFT JOIN `catalog_product_entity_int` AS `child_cpss` ON child_cpss.row_id = relation_product_entity.row_id AND child_cpss.attribute_id = child_cpsd.attribute_id AND child_cpss.store_id = 3 WHERE ((cpw.website_id = '3') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (IFNULL(ccas.value, ccad.value) = 1) AND (relation.child_id IS NULL OR IFNULL(child_cpss.value, child_cpsd.value) = 1)) AND (cc.created_in <= '1577761200') AND (cc.updated_in > '1577761200') GROUP BY `cc`.`entity_id`,
`ccp`.`product_id`,
`visibility` 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`)
Do you have any clue where could be the problem or what we can do ?
we have try to increase the variable innodb_lock_wait_timeout to 1500 in our data base but still with the same problem.
