Magento2: Database Schema: Incorrect Unique Indexes

[SchumacherFM]

Regarding my database inspections for migrating Mage1->2 I've found this thing:

The unique key which conflicts is in table cataloginventory_stock_item with the name

UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_WEBSITE_ID for (product_id,website_id).

IMHO: This does not make sense because of the column stock_id. The unique key must be on (product_id,stock_id). This newly introduced column website_id is totally wrong. In table cataloginventory_stock you also have added the column website_id which doesn't make sense.
You can still have the same stock_id associated to multiple website_ids or vice versa; so a third table would be necessary for assigning stock_ids to website_ids.
The current solution in table cataloginventory_stock looks like that for each website you must create its own stock_id ... :-1:

If I'm absolutely wrong with my IMHO a short explanation would be great on what kind of "multiwarehouse" feature you are working on!

[IvanChepurnyi]

I totally support your point concerning website_id in stock tables. Even if it is a part of multiwarehouse feature, website_id should not be in any of the following tables, it is easier to make a relation between website and stock via configuration.

Let's imagine we have a company that sells to US/CA market and EU market, they have a website for each country. But there is only 2 warehouses: EU warehouse in Germany and US/CA warehouse in US. Hope it will clarify the idea of separation stock scope from table level to a configuration, that is IMO much more flexible and has less performance/data size penalty.

[kokoc]

Thank you for reporting the issue. This issue will be fixed in context of other inventory improvements.
Internal reference: MAGETWO-33564

[hoanglan87]

From I see, the website_id is now 1 for all created product, right?

[maderlock]

Given that the default website = 0 for stock id =1 in table cataloginventory_stock, this suggests that all website IDs should be 0.

@hoanglan87 believes that all website_ids should be 1, and the code in module-catalog-inventory/Model/ResourceModel/Indexer/Stock/DefaultStock.php equates website_id to stock_id so that seems to make it always 1.

If I set all website_ids to 0 in cataloginventory_stock_item and reindex then I get a mixture of entries for website_id 0 and 1. This may explain some anomalous results with configurable products going in and out of stock.

Please could there be a definitive statement on this "functionality".

[SchumacherFM]

Any updates?

[maghamed]

Hi,

Regarding unique key on (product_id,website_id) in cataloginventory_stock_item table it's legacy, and in the scope of Multi Location Inventory track has already been fixed, but it's still not in mainline. Correct index is (product_id,stock_id) as you mentioned in your post.
Having (product_id,website_id) index you will stuck with conflicts implementing Multi Location Inventory (MLI).

Knowledge about Website_Id are already eliminated from CatalogInventory data interfaces both from
https://github.com/magento/magento2/blob/develop/app/code/Magento/CatalogInventory/Api/Data/StockInterface.php
and
https://github.com/magento/magento2/blob/develop/app/code/Magento/CatalogInventory/Api/Data/StockItemInterface.php

You can still have the same stock_id associated to multiple website_ids or vice versa

It's true, so in the scope of MLI we will deliver Link interface which describe the relationship between Source (MLI interface which describes physical Stock extending basic Stock interface from CatalogInventory module) and Website.

<?php
/**
 * Copyright © 2016 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Magento\MultipleLocationInventory\Api\Data;
/**
 * @api
 */
interface SourceWebsiteLinkInterface extends \Magento\Framework\Api\ExtensibleDataInterface
{
    /**
     * @return int
     */
    public function getSourceId();
    /**
     * @param int $sourceId
     * @return $this
     */
    public function setSourceId($sourceId);
    /**
     * Get website id
     *
     * @return int
     */
    public function getWebsiteId();
    /**
     * Set website id
     *
     * @param int $websiteId
     * @return $this
     */
    public function setWebsiteId($websiteId);
    /**
     * Retrieve existing extension attributes object.
     *
     * @return \Magento\MultipleLocationInventory\Api\Data\SourceWebsiteLinkExtensionInterface|null
     */
    public function getExtensionAttributes();
    /**
     * Set extension attributes object.
     *
     * @param \Magento\MultipleLocationInventory\Api\Data\SourceWebsiteLinkExtensionInterface $extensionAttributes
     * @return $this
     */
    public function setExtensionAttributes(
        \Magento\MultipleLocationInventory\Api\Data\SourceWebsiteLinkExtensionInterface $extensionAttributes
    );
}

There is also Repository interface to manage this entity.

All the links belonging to Source (Stock) would be available through the Source DTO

<?php
/**
 * Copyright © 2016 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Magento\MultipleLocationInventory\Api\Data;
use Magento\MultipleLocationInventory\Model\Source;
/**
 * @api
 */
interface SourceInterface extends \Magento\Framework\Api\ExtensibleDataInterface
{
...

    /**
     * @return \Magento\MultipleLocationInventory\Api\Data\SourceWebsiteLinkInterface[]
     */
    public function getWebsiteLinks()

    /**
     * @param \Magento\MultipleLocationInventory\Api\Data\SourceWebsiteLinkInterface[]
     * @return $this
     */
    public function setWebsiteLinks(array $websiteLinks)
}

[mbrinton01]

Still tracking internally as MAGETWO-33564 for mainline fix

[SchumacherFM]

Has this now been fixed on the 16th of Dec 2016?

[mbrinton01]

@SchumacherFM fixed in _develop_, not yet backported to _2.0_ and _2.1_.

[magento-engcom-team]

The fix MAGETWO-33564 will be available with release 2.2.0 soon

[korostii]

Hi @magento-engcom-team,

The fix MAGETWO-33564 will be available with release 2.2.0 soon

While such an announcement is mildly reassuring, it raises a couple follow-up questions.
1) Should we assume there will be a backport to older versions planned for a fix whenever it's marked as Reproduced on 2.0.x or Reproduced on 2.1.x ? Or does Fixed in 2.2.x label mean than only versions 2.2 and older will receive the fix?
2) Is there a more precise publicly announced timeline for 2.2 already? "Soon" sounds is a bit fuzzy.

Best regards & would be glad if someone could answer these

[magento-engcom-team]

Hi @korostii
We are not able to backport all fixed issues in 2.2.0 release to 2.1 and 2.0 releases. We will backport the most critical issues based on PO decision or the most up voted issues from public GitHub. Also, if we will get a pull request from Magento Community with the backport, we will accept it and deliver with upcoming patch releases.

[korostii]

Hi @magento-engcom-team, thanks for the general information.
I know that your resources are limited and only some of the issues will be backported.
Unfortunately, that's not what I asked.

What I am asking is: how do I find out whether you plan to backport _this exact_ issue based on its state and labels?
To me, closing an issue seems like a way to say "the job is done, nothing more to do here", which is different from "we will backport this to 2.1 ourselves", which is different from "we aren't gonna backport ourselves, feel free to make backport PRs".

There's obviously more job to be done here, yet it's "closed", so state doesn't help much here. Does the label help? I don't see the up for grabs label - does that mean that this issue is discouraged from posting backport PRs or that you'll backport this one yourselves or what?

[magento-engcom-team]

Hi @korostii
If we have related internal ticket for the fix or backport, the issue will be added to the project(s):

• Issues in 2.0
• Issues in 2.1
• Issues in 2.2
• Issues in develop

Project columns will reflect the internal ticket status on public GitHub
If the issue is not assigned to the project, means we don't have any plans for now about this issue. So feel free create the pull request in such case and we will be happy to process it!

[korostii]

Ok, that's something already. Thanks for this valuable piece of information.
However, the project description seems to contradict your words by saying that "This board reflects the progress we’ve made on acknowledged public issues that have been reported against the 2.1-develop branch."
It would be nice if you could mention in that description that some acknowledged issues are missing from that project because you don't have any plans for fixingbackporting them. Otherwise it's a bit confusing.

So now I can search for #1002 on project branch [2.1-develop]. Suppose that means that you don't have any plans for it and it's okay to grab it.

Still, how should I know whether this issue is "the job is done, nothing more to do here" or "we aren't gonna backport ourselves, feel free to make backport PRs" without looking inside it? just from the issue list?
The issue is Closed. Most people won't even click on it let alone read through the discussion.
It should be possible to determine the actual issue status _from the issue list_.

Do you see my point? Closing _unresolved acknowledged issues_ is harmful for the overall progress of the project. Closing the issue will get the issue ignored, and then reported again (but later) and the bug will stay for longer.

Please don't close unresolved issues, we've got enough mess in this issue tracker as it is.

[korostii]

To re-iterate: currently there is no way to filter out _unresolved acknowledged issues_ (like this one) on the issue list.

I seriously don't understand how do you expect people to help you resolving issues while at the same time you're not even able to provide them with a list of such issues.
I mean, it's no rocket science: just add an up for grabs label near the acknowledged one and don't close an acknowledged issue unless it is resolved on all branches. Is that too much to ask? Geez.

Please reopen this issue. Please keep it open unless it resolved: I think it's reasonable and the least you could do. Does that make sense for you?

If you disagree, with this approach, please explain why at least one. I'm tired of seeeing this happen over and over again with zero justification and I don't understand why you guys do it.

[magento-engcom-team]

Hi @SchumacherFM. Thank you for your report.
The issue has been fixed in magento/magento2#27399 by @serhiyzhovnir in 2.4-develop branch
Related commit(s):

• 9c5528ff66f77990dd05370c8ddade8e522a4d0e
• e4280560511769ad3145e6f3880c6ae3a3007d5f
• 9735426c8d2f127b4c9bc373dccc3051fe21c9c8
• c83fbc6f4b505ff2d9ded16b1bce11ccc761d535
• 8e4a197b11cde6e4b3d60d3fff34e70c0fd28682

The fix will be available with the upcoming 2.4.0 release.