Migration tool causing url_rewrite duplicates in Magento 2 leading to missing categories in Navigation

Question

I am currently attempting to Migrate data from Magento 1 to Magento 2.

I ran the Ubertheme Magento "Data Migration Pro" tool several times in the course of trying to Migrate our information over from one site to another. (usually this is because my connection dropped and I ran the program again) but it seems to have created some duplicate URL_keys in the database.

This looked OK on the site but when we came to change the URL to make the site live we lost the categories in the Megamenu.

As we were using a custom theme (based on Infortis: Ultimo) we decided to try and switch to the basic Magento theme instead and it came up with an error.

After checking the Var reports we found that the error read as follows:

1 exception(s):
Exception #0 (Exception): Item (Magento\Catalog\Model\Category\Interceptor) with the same ID "384" already exists.

Exception #0 (Exception): Item (Magento\Catalog\Model\Category\Interceptor) with the same ID "384" already exists.
#0 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php(307): Magento\Framework\Data\Collection->addItem(Object(Magento\Catalog\Model\Category\Interceptor))
#1 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php(1123): Magento\Eav\Model\Entity\Collection\AbstractCollection->addItem(Object(Magento\Catalog\Model\Category\Interceptor))
#2 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php(913): Magento\Eav\Model\Entity\Collection\AbstractCollection->_loadEntities(false, false)
#3 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/module-catalog/Model/ResourceModel/Category/Collection.php(177): Magento\Eav\Model\Entity\Collection\AbstractCollection->load(false, false)
#4 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/Data/Collection.php(831): Magento\Catalog\Model\ResourceModel\Category\Collection->load()
#5 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/module-catalog/Plugin/Block/Topmenu.php(81): Magento\Framework\Data\Collection->getIterator()
#6 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/Interception/Interceptor.php(121): Magento\Catalog\Plugin\Block\Topmenu->beforeGetHtml(Object(Magento\Theme\Block\Html\Topmenu\Interceptor), 'level-top', 'submenu', 0)
#7 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Theme\Block\Html\Topmenu\Interceptor->Magento\Framework\Interception\{closure}('level-top', 'submenu', 0)
#8 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Theme/Block/Html/Topmenu/Interceptor.php(26): Magento\Theme\Block\Html\Topmenu\Interceptor->___callPlugins('getHtml', Array, Array)
#9 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/module-theme/view/frontend/templates/html/topmenu.phtml(18): Magento\Theme\Block\Html\Topmenu\Interceptor->getHtml('level-top', 'submenu', 0)
#10 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/TemplateEngine/Php.php(59): include('/var/www/vhosts...')
#11 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Element/Template.php(270): Magento\Framework\View\TemplateEngine\Php->render(Object(Magento\Theme\Block\Html\Topmenu\Interceptor), '/var/www/vhosts...', Array)
#12 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Theme/Block/Html/Topmenu/Interceptor.php(167): Magento\Framework\View\Element\Template->fetchView('/var/www/vhosts...')
#13 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Element/Template.php(300): Magento\Theme\Block\Html\Topmenu\Interceptor->fetchView('/var/www/vhosts...')
#14 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Element/AbstractBlock.php(667): Magento\Framework\View\Element\Template->_toHtml()
#15 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Theme/Block/Html/Topmenu/Interceptor.php(479): Magento\Framework\View\Element\AbstractBlock->toHtml()
#16 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(557): Magento\Theme\Block\Html\Topmenu\Interceptor->toHtml()
#17 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(533): Magento\Framework\View\Layout->_renderBlock('catalog.topnav')
#18 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('catalog.topnav')
#19 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('catalog.topnav')
#20 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('catalog.topnav', true)
#21 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Element/AbstractBlock.php(511): Magento\Framework\View\Layout\Interceptor->renderElement('catalog.topnav', true)
#22 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/module-theme/view/frontend/templates/html/container.phtml(10): Magento\Framework\View\Element\AbstractBlock->getChildHtml()
#23 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/TemplateEngine/Php.php(59): include('/var/www/vhosts...')
#24 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Element/Template.php(270): Magento\Framework\View\TemplateEngine\Php->render(Object(Magento\Framework\View\Element\Template), '/var/www/vhosts...', Array)
#25 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Element/Template.php(300): Magento\Framework\View\Element\Template->fetchView('/var/www/vhosts...')
#26 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Element/AbstractBlock.php(667): Magento\Framework\View\Element\Template->_toHtml()
#27 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(557): Magento\Framework\View\Element\AbstractBlock->toHtml()
#28 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(533): Magento\Framework\View\Layout->_renderBlock('store.menu')
#29 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('store.menu')
#30 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('store.menu')
#31 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('store.menu', true)
#32 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/module-theme/view/frontend/templates/html/sections.phtml(26): Magento\Framework\View\Layout\Interceptor->renderElement('store.menu')
#33 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/TemplateEngine/Php.php(59): include('/var/www/vhosts...')
#34 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Element/Template.php(270): Magento\Framework\View\TemplateEngine\Php->render(Object(Magento\Framework\View\Element\Template), '/var/www/vhosts...', Array)
#35 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Element/Template.php(300): Magento\Framework\View\Element\Template->fetchView('/var/www/vhosts...')
#36 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Element/AbstractBlock.php(667): Magento\Framework\View\Element\Template->_toHtml()
#37 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(557): Magento\Framework\View\Element\AbstractBlock->toHtml()
#38 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(533): Magento\Framework\View\Layout->_renderBlock('navigation.sect...')
#39 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('navigation.sect...')
#40 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('navigation.sect...')
#41 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('navigation.sect...', false)
#42 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(585): Magento\Framework\View\Layout\Interceptor->renderElement('navigation.sect...', false)
#43 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(535): Magento\Framework\View\Layout->_renderContainer('page.top', false)
#44 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('page.top')
#45 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('page.top')
#46 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('page.top', false)
#47 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(585): Magento\Framework\View\Layout\Interceptor->renderElement('page.top', false)
#48 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(535): Magento\Framework\View\Layout->_renderContainer('page.wrapper', false)
#49 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('page.wrapper')
#50 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('page.wrapper')
#51 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('page.wrapper', false)
#52 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(585): Magento\Framework\View\Layout\Interceptor->renderElement('page.wrapper', false)
#53 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(535): Magento\Framework\View\Layout->_renderContainer('root', false)
#54 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(206): Magento\Framework\View\Layout->renderNonCachedElement('root')
#55 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(488): Magento\Framework\View\Layout\Interceptor->renderNonCachedElement('root')
#56 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(193): Magento\Framework\View\Layout->renderElement('root', true)
#57 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Layout.php(954): Magento\Framework\View\Layout\Interceptor->renderElement('root')
#58 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Framework\View\Layout->getOutput()
#59 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\View\Layout\Interceptor->___callParent('getOutput', Array)
#60 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Framework\View\Layout\Interceptor->Magento\Framework\Interception\{closure}()
#61 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php(494): Magento\Framework\View\Layout\Interceptor->___callPlugins('getOutput', Array, Array)
#62 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Result/Page.php(257): Magento\Framework\View\Layout\Interceptor->getOutput()
#63 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/View/Result/Layout.php(170): Magento\Framework\View\Result\Page->render(Object(Magento\Framework\App\Response\Http\Interceptor))
#64 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/Interception/Interceptor.php(58): Magento\Framework\View\Result\Layout->renderResult(Object(Magento\Framework\App\Response\Http\Interceptor))
#65 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/Interception/Interceptor.php(138): Magento\Framework\View\Result\Page\Interceptor->___callParent('renderResult', Array)
#66 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/Interception/Interceptor.php(153): Magento\Framework\View\Result\Page\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Response\Http\Interceptor))
#67 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/View/Result/Page/Interceptor.php(130): Magento\Framework\View\Result\Page\Interceptor->___callPlugins('renderResult', Array, Array)
#68 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/App/Http.php(139): Magento\Framework\View\Result\Page\Interceptor->renderResult(Object(Magento\Framework\App\Response\Http\Interceptor))
#69 /var/www/vhosts/websitename.co.uk/httpdocs/generated/code/Magento/Framework/App/Http/Interceptor.php(24): Magento\Framework\App\Http->launch()
#70 /var/www/vhosts/websitename.co.uk/httpdocs/vendor/magento/framework/App/Bootstrap.php(257): Magento\Framework\App\Http\Interceptor->launch()
#71 /var/www/vhosts/websitename.co.uk/httpdocs/index.php(39): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http\Interceptor))
#72 {main} 

From what I can tell this error seems to be cause by having duplicate url_rewrite paths in the database.

Looking in the url_rewrite database table there seems to be a number of values which have a duplicate target path:

I rolled back the site to a previous backup (before we did the migration) and it would seem that the site can function on the blank theme there.

As the error references 'categories' and the 'catalog.topnav' I think that they might be relates

I'm not sure how to resolve this and would appreciate any input with regards to this issue.

Answer 1

OK according to the Migration extension support team Magento 2 re-generates URLs rewrite for categories and products so the fix is:

1. Back-up your current M2 database.

2. Run the following SQL statement in your Magento 2 database:

DELETE FROM url_rewrite WHERE entity_type = 'category' OR entity_type = 'product'; 
DELETE FROM catalog_url_rewrite_product_category;

3. Go to back-end of your site, do steps below for M2 regenerates URLs rewrite for categories and associated products:

4. In categories management, create a new root category named ‘Temp Root Category’ Open Stores management, edit the default Store of the default Website and set the ‘Root Category’ form field to the newly added root category ‘Temp Root Category’ and click Save button. Once done, open Stores management once again, edit the default Store of the default Website and set the Root Category form field to the main root category which you want to publish in the storefront again and click Save button. Magento will regenerates categories and associate products URLs rewrite in this step.

It might take sometime to complete this task (It depends on the volume of categories and associated products) And then, re-index the data and clean the Magento cache.