Magento2: Unable to Import data in 2.1.0 - "Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "xxx" already exists."

[webspeaks]

Steps to reproduce

1. Upgrade to magento 2.1.0
2. Go to System -> Import

3. Select following options - http://joxi.ru/DmBNnDRuNM3Eem

   Expected result

4. The product details should get updated.

5. Product should be available in admin and frontend

   Actual result

6. Get this error in var/reports:
   a:4:{i:0;s:87:"Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "145" already exists.";i:1;s:7473:"#0 magento2\vendor\magento\module-eav\Model\Entity\Collection\AbstractCollection.php(304): Magento\Framework\Data\Collection->addItem(Object(Magento\Catalog\Model\Product\Interceptor))

7. Complete log is below:

a:4:{i:0;s:87:"Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "145" already exists.";i:1;s:7473:"#0 magento2\vendor\magento\module-eav\Model\Entity\Collection\AbstractCollection.php(304): Magento\Framework\Data\Collection->addItem(Object(Magento\Catalog\Model\Product\Interceptor))
#1 magento2\var\generation\Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor.php(830): Magento\Eav\Model\Entity\Collection\AbstractCollection->addItem(Object(Magento\Catalog\Model\Product\Interceptor))
#2 magento2\vendor\magento\module-eav\Model\Entity\Collection\AbstractCollection.php(1119): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->addItem(Object(Magento\Catalog\Model\Product\Interceptor))
#3 magento2\var\generation\Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor.php(1090): Magento\Eav\Model\Entity\Collection\AbstractCollection->_loadEntities(false, false)
#4 magento2\vendor\magento\module-eav\Model\Entity\Collection\AbstractCollection.php(910): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->_loadEntities(false, false)
#5 magento2\vendor\magento\module-catalog\Model\ResourceModel\Product\Collection.php(710): Magento\Eav\Model\Entity\Collection\AbstractCollection->load(false, false)
#6 magento2\var\generation\Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor.php(193): Magento\Catalog\Model\ResourceModel\Product\Collection->load(false, false)
#7 magento2\vendor\magento\module-catalog\Ui\DataProvider\Product\ProductDataProvider.php(68): Magento\Catalog\Model\ResourceModel\Product\Collection\Interceptor->load()
#8 magento2\vendor\magento\module-ui\Component\Listing.php(37): Magento\Catalog\Ui\DataProvider\Product\ProductDataProvider->getData()
#9 magento2\vendor\magento\framework\View\Element\UiComponent\Context.php(227): Magento\Ui\Component\Listing->getDataSourceData()
#10 magento2\vendor\magento\framework\View\Element\UiComponent\ContentType\Json.php(69): Magento\Framework\View\Element\UiComponent\Context->getDataSourceData(Object(Magento\Ui\Component\Listing))
#11 magento2\vendor\magento\module-ui\Component\AbstractComponent.php(155): Magento\Framework\View\Element\UiComponent\ContentType\Json->render(Object(Magento\Ui\Component\Listing), 'templates/listi...')
#12 magento2\vendor\magento\module-ui\Controller\Adminhtml\Index\Render.php(32): Magento\Ui\Component\AbstractComponent->render()
#13 magento2\var\generation\Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor.php(24): Magento\Ui\Controller\Adminhtml\Index\Render->execute()
#14 magento2\vendor\magento\framework\App\Action\Action.php(102): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->execute()
#15 magento2\vendor\magento\module-backend\App\AbstractAction.php(226): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))
#16 magento2\vendor\magento\framework\Interception\Interceptor.php(74): Magento\Backend\App\AbstractAction->dispatch(Object(Magento\Framework\App\Request\Http))
#17 magento2\vendor\magento\framework\Interception\Chain\Chain.php(70): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->___callParent('dispatch', Array)
#18 magento2\vendor\magento\framework\Interception\Chain\Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Ui\\Cont...', 'dispatch', Object(Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor), Array, 'adminAuthentica...')
#19 magento2\vendor\magento\module-backend\App\Action\Plugin\Authentication.php(143): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#20 magento2\vendor\magento\framework\Interception\Chain\Chain.php(67): Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch(Object(Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#21 magento2\vendor\magento\framework\Interception\Interceptor.php(138): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Ui\\Cont...', 'dispatch', Object(Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor), Array, 'adminMassaction...')
#22 magento2\vendor\magento\module-backend\App\Action\Plugin\MassactionKey.php(33): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#23 magento2\vendor\magento\framework\Interception\Interceptor.php(142): Magento\Backend\App\Action\Plugin\MassactionKey->aroundDispatch(Object(Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#24 magento2\var\generation\Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor.php(52): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->___callPlugins('dispatch', Array, Array)
#25 magento2\vendor\magento\framework\App\FrontController.php(55): Magento\Ui\Controller\Adminhtml\Index\Render\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#26 magento2\vendor\magento\framework\Interception\Interceptor.php(74): Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))
#27 magento2\vendor\magento\framework\Interception\Chain\Chain.php(70): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)
#28 magento2\vendor\magento\framework\Interception\Interceptor.php(138): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'install')
#29 magento2\vendor\magento\framework\Module\Plugin\DbStatusValidator.php(69): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#30 magento2\vendor\magento\framework\Interception\Interceptor.php(142): Magento\Framework\Module\Plugin\DbStatusValidator->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#31 magento2\var\generation\Magento\Framework\App\FrontController\Interceptor.php(26): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, Array)
#32 magento2\vendor\magento\framework\App\Http.php(135): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#33 magento2\vendor\magento\framework\App\Bootstrap.php(258): Magento\Framework\App\Http->launch()
#34 magento2\index.php(39): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http))
#35 {main}";s:3:"url";s:194:"/admin/mui/index/render/?namespace=product_listing&filters%5Bplaceholder%5D=true&paging%5BpageSize%5D=20&paging%5Bcurrent%5D=2&sorting%5Bfield%5D=entity_id&sorting%5Bdirection%5D=asc&isAjax=true";s:11:"script_name";s:10:"/index.php";}

1. Now I cannot open the admin product grid.
2. Product is not available on frontend as well.
   i have to delete the product from database to make get the site running. I am running Magento 2.1.0.

Thanks

[OnTapKristjan]

Apparently duplicated rows are inserted into cataloginventory_stock_item table (either during product import or upgrade, not sure which), that is why this error happens.
For a quick fix, remove all duplications from cataloginventory_stock_item table.

I used something like DELETE FROM cataloginventory_stock_item WHERE use_config_backorders = 0; you might need a slightly different query though.

[webspeaks]

@OnTapKristjan You are absolutely correct. I did the same and removed duplicate product entries from cataloginventory_stock_item.
I imported all products from CSV before upgrade. Not sure why there are duplicate entries after upgrade to 2.1.0.
Also the website_id in cataloginventory_stock_item and cataloginventory_stock_status was '1' before upgrade. But in a fresh install of Magento 2.1.0, it was '0'. So I changed the websites_id in both tables to 0 as well. Its working fine as of now. May be Magento guys can explain in a better way.

[sharath-kumar]

I ran into the exact same issue, when running an product image bulk import.

Here's how I went about resolving it:

-- Identify if there are any duplicate product entries in the cataloginventory_stock_item table
_select product_id , count(1) from cataloginventory_stock_item
group by product_id
order by 2 desc
;_

-- This SQL will show you a list of all the duplicate item rows in the cataloginventory_stock_item table.
-- This is needed to identify the item_id (s) that need to be deleted.
_select * from cataloginventory_stock_item
where product_id in
(select product_id from cataloginventory_stock_item
group by product_id
having count(product_id) > 1)
;_

-- Use this SQL to delete the duplicate rows.
-- Remember to only enter the duplicate item_id values that you want to delete
_delete from cataloginventory_stock_item
where item_id in (ENTER_ONLY_THE_DUPLICATE_ITEM_IDs_FROM_SQL_ABOVE_THAT_YOU_WANT_TO_DELETE)
;_

Issue resolved.

[manishparanjape]

You can use below query to get duplicate item_ids ( if you have large number of duplicate entries).

select n1.item_id from cataloginventory_stock_item n1, cataloginventory_stock_item n2 where n1.item_id > n2.item_id and n1.product_id = n2.product_id;

[shiftedreality]

Hi @arvind07

Thank you for reporting this issue.
We have created internal ticket MAGETWO-58075 to fix this issue.

[lavinachitara]

Hello,

I have also duplicate entries in table cataloginventory_stock_item and fetched duplicate entries as below
SELECT item_id, COUNT( product_id ), website_id FROM cataloginventory_stock_item where website_id <> 1 GROUP BY product_id HAVING COUNT( product_id ) >1

But both entries have different website_id where I am maintaining different stock for different stores, so I can not remove these duplicates.

Is there any other solution or Magento2 itself adding stock entries in wrong way?

[Yonn-Trimoreau]

If you don't have stock managed by store, you can use this to fix issue in one SQL request:

DELETE FROM cataloginventory_stock_item WHERE product_id IN ( SELECT * FROM( SELECT product_id FROM cataloginventory_stock_item GROUP BY product_id HAVING COUNT( product_id ) >1 )tblTMP WHERE website_id = 1 )

[siegfried-zach]

As I cannot see the state of the internal ticket MAGETWO-58075 --> does anyone know the state there? We facing the same problem and would need a fix for it.
Thx

[rattomago]

@shiftedreality any updates on this? Is MAGETWO-58075 already solved for EE customers? Does it also happen when i freshly start with latest 2.1.2?
I cannot find a fix and this really hurts us in a customer project ... appreciate a short note

thanks

[shiftedreality]

Hi @rattomago

Current issue is not fixed yet.
Related issue #5222 was fixed in develop branch.
Could you please verify it?

[younkim]

@rattomago, I'm running 2.1.2 that I installed today, and this problem is present. Duplicated entries cataloginventory_stock_item with website_id = 1 are added when you import products in the admin. In my case, all I imported were url_key with the SKUs.

[senthilengg]

Same issue with 2.1.2. Any update when can we expect the fix ?

[DavidLambauer]

Same here

[DavidLambauer]

I had to deal with this bug, so I worked on a dirty workaround an documented it here:

https://dev98.de/2017/01/23/workaround-for-magento-2-issue-5418-product-grid-does-not-work-after-import/

The Workaround is based on @Yonn-Trimoreau Solution.

Hope this helps some of you until the bug has been fixed

[Yonn-Trimoreau]

Thanks for linking me back @DavidLambauer

[webspeaks]

Still present in Magento 2.1.5.

[Mahmoud-Refaat]

This problem exist duo to difference between products "website_id" value and default value in Magneto 2

Table: cataloginventory_stock_item so first
1- check the default value for your site it will be at table cataloginventory_stock
2- then compare it to the existing products at Table: cataloginventory_stock_item
in my case it was 0 while products website_id were 1 so to solve this run the following SQL
UPDATE cataloginventory_stock_item SET website_id=0

[robbiefowler]

Has this been fixed yet post 2.1.5?

[magento-engcom-team]

@webspeaks, thank you for your report.
We were not able to reproduce this issue by following the steps you provided. If you'd like to update it, please reopen the issue.
We tested the issue on 2.3.0-dev, 2.2.0, 2.1.9

[Yonn-Trimoreau]

I love Magento team's issue management. If the issue can be closed for whatever reason, let's close it, otherwise, let's not fix it and may it die in the void. It seems like you only fix security issues.. But what about functional issues ? I'm following around 20 issues with 10+ comments confirming the issue, and Magento team is _always_ responding 2 years later saying that they cannot reproduce the issue (and obviously, the issue's author is gone for a long time). Looks like we'll still be making patchwork in a decade (or two).

Still, Magento is the only serious solution for professionnal e-commerce on PHP (to me). But that could be way better if issues were managed correctly. I'm wondering where the Magento Enterprise money goes. Certainly not into hiring a sufficient number of developers..

[DavidLambauer]

@Yonn-Trimoreau I partly agree! I also experienced quick issue closing without taking care of the actual result. Seems to be every time Magento 2 has to release something and can tell "We have closed X Issues".

A little bit more patient for the issues would be great, even if they are not solvable by the M2 Team itself, there might be someone from the community who can help.

[webscot]

Problem is ALIVE & WELL as of November 17, 2017 in v. 2.2.1. Used Save & Duplicate to create product #2. Product #2 never appeared on the frontend even after re-indexing with cache disabled. Enabled cache, and we get this error (issue 5418). We also moved one category (#43) up in position. It is this #43 that is mentioned in the error report. After re-enabling cache, product #2 is now there, but still no navigation categories. Enable cache again and the error returns. Additionally, we have no duplicate rows. Now what?