MigrateCatalogInventoryNotifyStockQuantityData patch crashes when there is more then one stock in cataloginventory_stock

[hostep]

Preconditions (*)

1. Seen on Magento OS 2.4.6-p2 with MSI modules at version 1.2.6-p2

Steps to reproduce (*)

1. We have a client with this super old Magento shop on version 2.2.3 that we are finally upgrading to 2.4.6-p2
2. This old shop has a module Magestore_Webpos that years ago created a second stock in the cataloginventory_stock table, and we also have a whole bunch of entries in the cataloginventory_stock_item table that references that second stock (id: 2)
3. When we upgrade this shop to Magento 2.4.6-p2 and enable the MSI modules, then running bin/magento setup:upgrade results in the following error:

...
layout: 1
block_html: 1
full_page: 1
Unable to apply data patch Magento\InventoryLowQuantityNotification\Setup\Patch\Data\MigrateCatalogInventoryNotifyStockQuantityData for module Magento_InventoryLowQuantityNotification. Original exception message: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'default-KB001' for key 'PRIMARY', query was: INSERT INTO `inventory_low_stock_notification_configuration` (`source_code`, `sku`, `notify_stock_qty`) SELECT `source_item`.`source_code`, `source_item`.`sku`, `stock_item`.`notify_stock_qty` FROM `cataloginventory_stock_item` AS `stock_item`
 INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = stock_item.product_id
 INNER JOIN `inventory_source_item` AS `source_item` ON source_item.sku = product.sku WHERE (stock_item.use_config_notify_stock_qty = 0) AND (source_item.source_code = 'default')

Running the SELECT part of the query results in:

mysql> SELECT `source_item`.`source_code`, `source_item`.`sku`, `stock_item`.`notify_stock_qty` FROM `cataloginventory_stock_item` AS `stock_item`
 INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = stock_item.product_id
 INNER JOIN `inventory_source_item` AS `source_item` ON source_item.sku = product.sku WHERE (stock_item.use_config_notify_stock_qty = 0) AND (source_item.source_code = 'default')
+-------------+-------+------------------+
| source_code | sku   | notify_stock_qty |
+-------------+-------+------------------+
| default     | KB001 |           0.0000 |
| default     | KB001 |           0.0000 |
+-------------+-------+------------------+
2 rows in set (0.00 sec)

And indeed, there are 2 rows in cataloginventory_stock_item that correspond with this SKU (entity_id: 9158)

mysql> SELECT item_id, product_id, stock_id, use_config_notify_stock_qty FROM cataloginventory_stock_item WHERE product_id = '9158';
+---------+------------+----------+-----------------------------+
| item_id | product_id | stock_id | use_config_notify_stock_qty |
+---------+------------+----------+-----------------------------+
|    9158 |       9158 |        1 |                           0 |
|   18830 |       9158 |        2 |                           0 |
+---------+------------+----------+-----------------------------+
2 rows in set (0.00 sec)

So it sounds like this MigrateCatalogInventoryNotifyStockQuantityData patch can't deal with databases that have more then a single stock before MSI got installed.

Expected result (*)

1. No errors

Actual result (*)

1. See above

[m2-assistant]

Hi @hostep. Thank you for your report.
To speed up processing of this issue, make sure that you provided sufficient information.
Add a comment to assign the issue: @magento I am working on this

---

Join Magento Community Engineering Slack and ask your questions in #github channel.

[hostep]

I can work around the problem by executing this, but I don't know if that's the best way forward:

UPDATE cataloginventory_stock_item SET use_config_notify_stock_qty = 1 WHERE product_id = '9158' AND stock_id = 2;