Open hazzy opened 4 years ago
It seems the problem is caused, because you have duplicates in cataloginventory_stock_status
. If you check that table, my guess is you will see 2 rows per product. One for website_id = 0
and one for website_id = 1
.
Check your website_id in cataloginventory_stock
and then afterwards delete all rows in cataloginventory_stock_status
with wrong website_id
.
Somehow cataloginventory_stock_status
gets populated with wrong website_id
when you update stock using Magmi.
Thanks for the idea @puntable . I will check it out and report back
Doing a reindex on cataloginventory_stock
immediately after Magmi update is completed seems to solve the problem.
Yes - i noted in the original post that a re-index stops the Error reports happening. But it seems to take forever to run (well a long time with the website not working properly so it seems like forever!)
I am getting these numbers.
Updated 8023 products. Execution time: 95.651278972626 seconds
Reindex of ONLY cataloginventory_stock
included. I am using Magmi datapump.
I can only recommend digging deeper, maybe profiling with blackfire and sharing your findings here :)
Ok @tmotyl I have done some more testing.
I start with a working M2 website. The CATALOGINVENTORY_STOCK_STATUS has 15761 rows and no duplicates. All rows follow this pattern :-
product_id website_id stock_id qty stock_status
235 0 1 100.0000 1
I start the Magmi CLI job to update all products using an SQL datasource. I include the Magmi Magmi_ReindexingPlugin in the job.
The website returns error report screens with the report id when browsing the catalog.
Now while Magmi job is running the CATALOGINVENTORY_STOCK_STATUS table is growing in size bit by bit or the course of the run. Looking at the table I find row duplicates thus:-
product_id website_id stock_id qty stock_status
235 0 1 100.0000 1
235 3 1 100.0000 1
235 4 1 100.0000 1
The CATALOG_INVENTORY_STOCK_STATUS grows to about 47500 rows i.e. each product_id is triplicated with a row for website_ids 0, 3, 4.
Then suddenly the row count of CATALOGINVENTORY_STOCK_STATUS drops to 15761 again and the website starts working. I think this happens when the Magmi_ReindexingPlugin kicks in. Looking at the CATALOGINVENTORY_STOCK_STATUS table shows the rows with website_ids 3 and 4 have been deleted by something.
The CATALOGINVENTORY_STOCK table only has 1 row in it. Maybe that's significant?
stock_id website_id stock_name
1 0 Default
Does anyone understand what is going on here?
the only place where I see cataloginventory_stock_status table being updated is: https://github.com/macopedia/magmi-m2/blob/9681a4e2afac5e74cd07c4050c1ad645a954d8c4/magmi/engines/magmi_productimportengine.php#L1905-L1905
where it's joining with store_website table, thus might result in row per website.
Do you think the logic is wrong here? What would be the correct way?
@tmotyl As a test i updated magmi_productimportengine.php code and commented out the JOIN with "store_website" and re-ran the Magmi CLI job.
The CATALOGINVENTORY_STOCK_STATUS stayed at 17561 rows throughout the run and the website remained operational. So that's good.
I don't really understand the logic of the script though so can't say if the line is critical for some other unknown reason.
I guess the real issue comes down to why Magento triggers the error in the first place if it finds duplicate data in that table. If it was using the website_id as part of the key then i don't think it should consider the rows to be duplicated anyway. But as i say i don't really understand what's going on here.
If anyone finds this comment when searching for this error, I was experiencing the same problem on Magento 2.4 using Magmi. Based on the comments above, I figured out the error was due to multiple rows in the _cataloginventory_stockstatus table after running a simple import in Magmi with SKU,QTY.
The duplicate rows were removed after a re-index, but the Magmi quantity import ran so often that the error would persist until the next re-index.
The error in system.log was
[2021-10-15 02:34:20] main.ERROR: Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "1307" already exists. [] []
The fix was changing line 1904 in _magmi\engines\magmiproductimportengine.php
from
$sql = "INSERT INTO `$css` SELECT '$pid' as product_id,ws.website_id,cis.stock_id,'$qty' as qty,? as stock_status
to
$sql = "INSERT INTO `$css` SELECT '$pid' as product_id,cis.website_id,cis.stock_id,'$qty' as qty,? as stock_status
After the change, the website id is being pulled from the _cataloginventorystock table instead of the _storewebsite table. Not sure if this change will affect anything else, but it seems to have fixed the issue for me.
Hi All,
I’m testing Magmi-m2 on Ubuntu server running Apache 2.4.41, php 7.3, 10.2.31-MariaDB-1:10.2.31. I have migrated the Magmi scripts from the current live M1 setup with required changes.
I get a problem with running a Magmi CLI script in CREATE mode (but it also happens in UPDATE too). The website stops working and the following REPORT error found (sometimes the product id is different)
php magmi.cli.php -profile=profile_all -mode=create
Most products will be pre-existing in the database so Magmi should update them. A few new ones may be present in the SQL datasource.
Once the Magento re-indexing is finished then the error seems to go away but the re-index for the Product EAV index takes 18.5 mins. All others are quick. So website is unusable over that time period.
Does anyone have an idea of what the issue might be? TIA