Magento 2: Category Products Indexer Unnecessarily Triggered on Akeneo Sync Without Changes

[rbouma]

The primary issue arises when initiating a category synchronization in Magento 2, even without making any modifications, which unexpectedly triggers the Category Products indexer to register changes. This occurs when the category sync job is executed, leading to the catalog_category_product_cl changelog being populated twice. This duplication is caused by a trigger, despite no alterations being made in Akeneo. A closer examination reveals that only categories with subcategories are marked as altered in the changelog.

This issue is particularly problematic for our client, who manages extensive categories, resulting in prolonged indexing times. Additionally, other indexes dependent on the Category Products index are forced to wait, exacerbating the delay.

The problematic trigger is identified as follows:

BEGIN

IF (
	NOT(NEW.`entity_id` <=> OLD.`entity_id`) 
	OR NOT(NEW.`attribute_set_id` <=> OLD.`attribute_set_id`) 
	OR NOT(NEW.`parent_id` <=> OLD.`parent_id`) 
	OR NOT(NEW.`created_at` <=> OLD.`created_at`) 
	OR NOT(NEW.`path` <=> OLD.`path`) 
	OR NOT(NEW.`position` <=> OLD.`position`) 
	OR NOT(NEW.`level` <=> OLD.`level`) 
	OR NOT(NEW.`children_count` <=> OLD.`children_count`)
	
) THEN INSERT IGNORE INTO `catalog_category_product_cl` (`entity_id`) VALUES (NEW.`entity_id`); 
END IF;

END

This trigger was identified using the command SHOW TRIGGERS WHERE Statement LIKE "%catalog_category_product_cl%";.

Further investigation into the sync process revealed that the children_count attribute was reset to 0 and then recalculated, resulting in the changelog being updated twice - once for the reset and once for the recount. A pull request addressing this issue is forthcoming. Created in #666

This PR changes how the children_count attribute is managed by relocating its processing to the update code, which wil updates this attribute for new categories only. With this adjustment, if there are no updates, the category's children_count will no longer be reset to 0.

It will still be recalculated if there is an update with category in Akeneo >

magento2-connector-community/Job/Category.php

Lines 707 to 722 in 6003d33

	public function updateChildrenCount()
	{
	/** @var AdapterInterface $connection */
	$connection = $this->entitiesHelper->getConnection();
	$connection->query(
	'
	UPDATE `' . $this->entitiesHelper->getTable('catalog_category_entity') . '` c SET `children_count` = (
	SELECT COUNT(`parent_id`) FROM (
	SELECT * FROM `' . $this->entitiesHelper->getTable('catalog_category_entity') . '`
	) tmp
	WHERE tmp.`path` LIKE CONCAT(c.`path`,\'/%\')
	)
	'
	);
	}

This did create 2 unnecessary changes in the catalog_category_product_cl changelog table.

Environment and Configuration:

• The system is currently running Akeneo 5, although tests on Akeneo 7 confirmed the issue persists.
• The database in use is MariaDB, version 10.6.12-MariaDB-0ubuntu0.22.04.1.

Steps to Reproduce:

1. Create several categories and subcategories.
2. Execute the category job sync to Magento 2 using the connector.
3. Reindex Category Products.
4. Initiate a new sync with the Akeneo connector for the category job, without making any changes.

Expected Result:

1. The indexer should not register any changes.

Actual Result:

1. The "Category Products" indexer is again populated with changes, despite no actual modifications being made.

[rbouma]

Fix for this in PR #666

[rbouma]

@magentix Can you have a look? and merge this because we still experience this. I have fixed this with a composer patch but it will be really nice if this is fixed in the official repo.