Product is showing as greyed out in shop, clearly has Inventory available

[kirstenalarsen]

Description

A product has stock available both in products and in inventory. It has been added to the order cycle and it appears in the shop - but it is greyed out and customers cannot purchase it

Expected Behavior

When a product has stock in Inventory and is in the order cycle it should be available for purchase in the shop

Actual Behaviour

The product is greyed out

Steps to Reproduce

Not sure yet. The product page suggests that there are 3 overrides in the system, so I'm wondering if another shop has 'hidden' it and that is interfering somehow? Thomaz is investigating this hunch

Shop is https://www.openfoodbrasil.com.br/instituto-acacia-estoque/shop#/shop

1. 2. 3. 4.

Workaround

TBC

Severity

S3 for now - unless we get clearer that there is no workaround and/or it starts affecting lots of people

Your Environment

Currently only on Brazil instance

Possible Fix

[kirstenalarsen]

some possible clues from dev tools?

[luisramos0]

I dont get any of those errors on the console Kirsten :man_shrugging: I dont think they are related to this issue... would be good to know what that "GET about:blank net::.... is.

I can see this problem on this shop. I can confirm this is a server side problem, the frontend code is just fetching the data and graying out the variant because it comes out of stock from the server: on_demand = false on_hand = 0

I think this should be tagged S2 until we find a workaround. I mean, it needs to be investigated now. If a core dev gets super admin access or looks at the DB they will be able to see what's going on. Without access to the server it's not so easy.

Could the Brasil team grant access to the global sysadmins? This way they can get support in these cases. this is what is needed in host config in ofn-install: users_sysadmin:

• "{{ core_devs }}"

[luisramos0]

ok, I am tagging this S2, if this was on one of the main instances this would obviously be an S2, right? I am tagging it blocked until we get access to the BR server to debug it.

[luisramos0]

Now I see we were granted access to BR :tada:

There's a data consistency problem in the database with more than one override for the same hub_id and same variant_id:

select hub_id, variant_id, count(*) from variant_overrides group by hub_id, variant_id having count(*) > 1;

The user of the inventory page must be seeing one of the duplicate overrides and updating it's on_hand value but in the DB there's a override with on_hand zero that gets used in the shopfront...

There are 219 duplicate overrides in the BR db. In FR live db there are 40 overrides like this. There are 57 overrides like this in AUS and 18 in the UK.

Some edge case when using the inventory page maybe? I dont have any clues? What could be making Brasil have more of these? :man_shrugging:

I wonder what happens in terms of stock management when orders are placed! what override gets the stock decrease? I think this is quite a serious issue with shopfront availability as well as issues with stock management. This is an S2 I'd say.

[luisramos0]

To confirm my theories I manually deleted the variant_override with count_on_hand zero in the BR live database and the original Nectar de Caju problem is now fixed here: https://www.openfoodbrasil.com.br/instituto-acacia-estoque/shop#/shop

[lin-d-hop]

This is now creating a problem in UK production.

I can see for enterprise Cambridge Food Hub we have some products greyed out in the shopfront: You can see this on Cambridge Food Hub (note the OCs are member only so you'll need to be a membership tagged user or manager of the enterprise to use this) ID: 201177 Products: Beetroot - Dynamic Organics, Cambs, UK (organic) Apples (Cripps Pink) - Chile (organic) Broad Beans - Waterland Organics, Cambs, UK (organic) to name the first three in the shop.

This is definitely related to inventory in some way. All of these products are inventory products.

There is a lot of weird stuff going on that I havent yet uncurled :-/

[lin-d-hop]

Hrm, unlike the original issue some of the greyed out products for this enterprise do not have inventory available. Products should just not appear as greyed out in the shopfront.... right?

[luisramos0]

This issue 5384 is happening in uk live for 41 products and Cambridge Food Hub has 8 of them (10 variants) in this category:

These products have at least a variant with multiple variant_overrides in the DB.

.

some of the greyed out products for this enterprise do not have inventory available.

@lin-d-hop can you share the product names so I can check the DB?

The workaround I used in Brasil is just to delete extra entries from the database. Shall I do it manually in the uk live DB and also prepare a PR so this gets cleaned up in all instances?

Regarding the root cause, I guess this will be a bug in the inventory page code. Maybe we should brainstorm? What kind of user action could trigger 2 entries to be on the database? I can think of:

• double clicks on buttons
• users with different permissions, one user creates inventory and the next user cant see that entry but is able to create the second one?
• what else could it be?

[luisramos0]

We dont have timestamps on variant_overrides, something we can add in the PR. I see the ids of the repeated entries are, not always, but mostly sequential. That could be an indication they were created in the same user session.

[luisramos0]

I just went for it in the live uk DB and run the DB fix, here's the SQL statement I ran delete from variant_overrides where id in (select vo.id from variant_overrides vo, (select hub_id, variant_id from variant_overrides group by hub_id, variant_id having count(*) > 1) repeats where vo.hub_id = repeats.hub_id and repeats.variant_id = vo.variant_id and (vo.hub_id, vo.variant_id, vo.id) not in (select hub_id, variant_id, max(id) from variant_overrides group by hub_id, variant_id having count(*) > 1));

This deleted 74 repeated overrides but kept the latest override for each of the repeated entries (54 overrides).

@lin-d-hop can you verify? I checked cambridge food hub and this greyed out product you shared on slack is no longer there:

[lin-d-hop]

@luisramos0 Thanks for acting so quickly and autonomously on this!

I can verify the greyed out products in the shopfront have gone so this is definitely the cause. Hooray! Thank you!

It does sound likely that these duplicates were created in the same session.

One thought in the brainstorm is importing to inventory - but I do not think that was the case for Cambridge Food Hub.

I'm not sure how a product could be created in inventory twice as a user. Unless it is related to the page being slow or timing out half way through and the user stopping the page and repeating some actions. But the UI doesn't want to allow this.

When I was looking at the products in question I noticed that the user had selected to override stock settings for all of them. Might not be related, but saving changes like this to inventory is very slow and as a user I want to stop the page and refresh while I'm using it.

[lin-d-hop]

Not seen this for a while. I'm going to close.

gitcull2021

[lin-d-hop]

As we are removing inventory on the roadmap and this issue is very rare (one UK hub, twice in two years) it is unlikely worthwhile to invest in a proper fix to this. Therefore this is something @AdamOFN can do as per Luis' solution above.

[BethanOFN]

Reopening as this issue is again happening for Cambridge Food Hub e.g. @AdamOFN can you run the sql above?

[adamjhol]

@BethanOFN I've run the query and 42 entries were removed