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 #12205

@engcom-backlog-nazar - this appears to be a duplicate of #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

BEGIN
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

BEGIN
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?

[github-jira-sync-bot]

✅ Jira issue https://jira.corp.magento.com/browse/AC-1046 is successfully created for this GitHub issue.

[m2-assistant]

✅ Confirmed by @engcom-Hotel. Thank you for verifying the issue.
Issue Available: @engcom-Hotel, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

[Vasudev-22]

@magento I am working on this

[Vasudev-22]

@magento give me 2.4-develop instance

[magento-deployment-service]

Hi @Vasudev-22. Thank you for your request. I'm working on Magento instance for you.

[magento-deployment-service]

Hi @Vasudev-22, here is your Magento Instance: https://e13afae2824041bff69323cb5f4cd75e.instances-prod.magento-community.engineering
Admin access: https://e13afae2824041bff69323cb5f4cd75e.instances-prod.magento-community.engineering/admin_1e84
Login: 18a6fcdd
Password: 4d9ade691e6e

[Vasudev-22]

@erikhansen , @Pavel-Fekete ,

Hi , This issue is not reproducible in latest version (Open Source)

Attached here the video for reference, please let me know, if i missed any steps to reproduce.

Products._.Inventory._.Catalog._.Magento.Admin.mp4

[engcom-Bravo]

Hi @erikhansen,

Thanks for your reporting and collaboration.

We have verified the issue in Latest 2.4-develop instance and the issue is no more reproducible.

Please feel free to reopen the issue if you are still facing any issue.

Thanks.