Category error : Item (Magento\Catalog\Model\Category\Interceptor) with the same ID "191" already exists

Question

I know there is a similar issue regarding a product, but now I've got this error with a category.

I can't recall that I did something different, it just all of a sudden started throwing this error as soon as I got on a category page on the frontend.

Sometimes re-saving the category in the admin resolved the issue, but most of the time it doesn't. Where does this error come from? How to fix it? Did anyone else encounter this error too?

Edit

It looks like the category page asks the URL rewrite table for the URL's. It generates this query:

SELECT `e`.*, 
   IF(at_is_active.value_id > 0, at_is_active.value, 
   at_is_active_default.value) AS 
   `is_active`, 
   `url_rewrite`.`request_path` 
FROM   `catalog_category_entity` AS `e` 
   INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` 
           ON ( `at_is_active_default`.`entity_id` = `e`.`entity_id`) 
              AND ( `at_is_active_default`.`attribute_id` = '46' ) 
              AND `at_is_active_default`.`store_id` = 0 
   LEFT JOIN `catalog_category_entity_int` AS `at_is_active` 
          ON ( `at_is_active`.`entity_id` = `e`.`entity_id` ) 
             AND ( `at_is_active`.`attribute_id` = '46' ) 
             AND ( `at_is_active`.`store_id` = 1 ) 
   LEFT JOIN `url_rewrite` 
          ON ( url_rewrite.entity_id = e.entity_id ) 
             AND ( url_rewrite.is_autogenerated = 1 
                   AND url_rewrite.store_id = 1 
                   AND url_rewrite.entity_type = 'category' ) 
WHERE  ( IF(at_is_active.value_id > 0, at_is_active.value,   
     at_is_active_default.value) 
            = 
            '1' ) 
   AND ( `e`.`entity_id` IN( '10', '170', '171', '172', 
                             '173', '175', '176', '177', 
                             '178', '179', '180', '189', '276' ) ) 
ORDER  BY `e`.`position` ASC 

But if your url_rewrite-table is corrupted (which is most likely in my case), it ends up with having duplicate ID's.

Answer 1

1. Delete records from 'url_rewrite' table where "entity_type" is "category".
2. run php bin/magento indexer:reindex

Answer 2

The following SQL query cleans up duplicate category rewrites. Do not use without a backup.

You can use n98-magerun2 db:console or any other mysql client:

 delete
  from url_rewrite
  where url_rewrite_id in (
    select url_rewrite_id
    FROM (select url_rewrite_id
      from url_rewrite
      where entity_type = 'category'
      group by target_path, store_id
      having count(*) > 1) t
  )

Answer 3

1. Go to Admin Panel -> Marketing -> URL Rewrites.
2. Filter by "category/{entity_id} (which causes the already exist error)" in the target_path. If you find the same target_path for the same store then delete the one.
3. Run php bin/magento indexer:reindex

Answer 4

For us just running this did the trick:

php bin/magento indexer:reindex

Our error (with Magento 2.2.2) was this when doing frontend search or entering category:

Exception #0 (Exception): Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "XXXX" already exists.

Answer 5

Run SQL

SELECT product_id ,COUNT(*) FROM cataloginventory_stock_status GROUP BY product_id, product_id HAVING COUNT(*) > 1

After run above sql you can get all duplicate ids then run below SQL query.

delete from cataloginventory_stock_status where  product_id = {AddDuplicateId} and website_id != 0 ;

update cataloginventory_stock_status set stock_status = 1 where  product_id = {AddDuplicateId} and qty >= 1 ;

Answer 6

You can follow the guide to fix it from stackexchange and create an extension to fix this here: https://github.com/magepow/magento-2-same-Id-already-exists