Magento2: Duplicated product is out of stock when index set to UPDATE BY SCHEDULE

[erikhansen]

Preconditions

1. Install Magento Open Source 2.2.4 with sample data
2. Setup CRON
3. Change all indexes to "UPDATE BY SCHEDULE"

Steps to reproduce

1. Locate a simple product in the backend
2. Click "Save & Duplicate"
3. Set the following attribute values on the newly created product and then click "Save":
   
   
   
   • Enabled: True
   
   
   • Quantity: 1
   
   
   • Stock Status: In Stock
   
   
4. Load the product on the frontend (either by loading the url key or by accessing the product directly via /catalog/product/view/)

Expected result

1. The product should show with an "Add to Cart" button:
   

Actual result

1. The product shows as "Out of Stock":
   
2. If I run these commands, then the product shows as in stock:

bin/magento indexer:reindex cataloginventory_stock
bin/magento cache:flush

[ghost]

Hi @erikhansen Which versions your tested ?

[erikhansen]

@engcom-backlog-nazar I tested it on Magento 2.2.4. I edited the task description just now to clarify that.

[amanuni]

I am also facing same issue.

[matthew-muscat]

This looks to have been fixed in release v2.2.5 - the change can be found in https://github.com/magento/magento2/issues/12205

@engcom-backlog-nazar - this appears to be a duplicate of https://github.com/magento/magento2/issues/15984 — is it assumed merchants need to update to v2.2.5 to get the fix?

[ghost]

@erikhansen This commit, doesn't fix the issue.

[matthew-muscat]

@engcom-backlog-nazar yep, looks like #12205 does not actually fix this particular issue... apologies.

Upon investigating this one further - this looks to be due to an omission in the way the stock indexer changelog is triggered on INSERT / UPDATES / DELETES...

For example...
1) Updating a product's details relating to inventory (ie: qty or stock status), will result in the item being scheduled for update in the stock index - as such, the product becomes available for sale after the schedule update is completed.

2) Updating a product's attribute details (ie: enabling / disabling a product's status), will NOT result in the item being scheduled for an update in the stock index — this can result in the item becoming available in Magento's frontend, but in a state where it will appear as "Out Of Stock"

The problem would also appear to be related to "disabled" products not containing an entry in the cataloginventory_stock_status table. As such, once it's enabled, it never gets this entry.

For example — updates to the catalog_product_entity and catalog_product_entity_int tables does not trigger updates to the stock indexer changelog.

catalog_product_entity triggers

INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (OLD.`entity_id`);
INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (OLD.`entity_id`);
END

catalog_product_entity_int triggers

IF (NEW.`value_id` != OLD.`value_id` OR NEW.`attribute_id` != OLD.`attribute_id` OR NEW.`store_id` != OLD.`store_id` OR NEW.`entity_id` != OLD.`entity_id` OR NEW.`value` != OLD.`value`) THEN INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NEW.`value_id` != OLD.`value_id` OR NEW.`attribute_id` != OLD.`attribute_id` OR NEW.`store_id` != OLD.`store_id` OR NEW.`entity_id` != OLD.`entity_id` OR NEW.`value` != OLD.`value`) THEN INSERT IGNORE INTO `catalog_product_category_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NEW.`value_id` != OLD.`value_id` OR NEW.`attribute_id` != OLD.`attribute_id` OR NEW.`store_id` != OLD.`store_id` OR NEW.`entity_id` != OLD.`entity_id` OR NEW.`value` != OLD.`value`) THEN INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NEW.`value_id` != OLD.`value_id` OR NEW.`attribute_id` != OLD.`attribute_id` OR NEW.`store_id` != OLD.`store_id` OR NEW.`entity_id` != OLD.`entity_id` OR NEW.`value` != OLD.`value`) THEN INSERT IGNORE INTO `catalog_product_attribute_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
IF (NEW.`value_id` != OLD.`value_id` OR NEW.`attribute_id` != OLD.`attribute_id` OR NEW.`store_id` != OLD.`store_id` OR NEW.`entity_id` != OLD.`entity_id` OR NEW.`value` != OLD.`value`) THEN INSERT IGNORE INTO `catalogrule_product_cl` (`entity_id`) VALUES (NEW.`entity_id`); END IF;
END

[matthew-muscat]

Locating the triggers in place for the Stock Index changelog can be found using the following query...

SHOW TRIGGERS WHERE `Statement` LIKE '%cataloginventory_stock_cl%';

For me, this results in only the following triggers being present...

Trigger | Table | Action
------- | ----- | ------
trg_cataloginventory_stock_item_after_insert | cataloginventory_stock_item | INSERT
trg_cataloginventory_stock_item_after_update | cataloginventory_stock_item | UPDATE
trg_cataloginventory_stock_item_after_delete | cataloginventory_stock_item | DELETE

[sjb9774]

@matthew-muscat do you have a suggested fix for this issue? I'm currently running into this on 2.2.6

[matthew-muscat]

@sjb9774 — we changed the index mode for stock to "update on save" — which works around the issue, since it's only the "update on schedule" mode that is affected here.

[sheshagiriAnvekar]

Is there any fix available for this issue? we are finding the same issue on 2.2.5

[sjb9774]

@engcom-backlog-nazar Do you know if there's any solution in a future version or patch that addresses this problem? I'm currently dealing with it for a client who cannot reasonably change the index mode to "update on save".

[hostep]

I have the feeling this issue might have been solved by MC-19407: Number of the rows increase very fast in changelog table, but I'm not sure.

Could somebody try to test this?

[Pavel-Fekete]

Still facing this issue on latest 2.4-develop

Preconditions

1. Magento 2.4-develop
2. Setup CRON
3. Change all indexes to "UPDATE BY SCHEDULE"
   

Steps to Rerpoduce:

1. Select existing product in backend or create new
2. Click "Save & Duplicate"
3. Set the following attribute values on the newly created product and then click "Save":
   Enabled: True
   Quantity: 1
   Stock Status: In Stock
   
4. Load the product on the frontend

Actual Result: The product shows as "Out of Stock":