Data-migration-tool: Integrity Check failed for Url Rewrites

[MagePsycho]

data-migration is failing at integrity check stage for url rewrite step with the following command:
php bin/magento migrate:data {/path/to/data-migration/config.xml}

[ERROR]: There are duplicates in URL rewrites:
Request path: towel-set.html Store ID: 2 Target path: catalog/product/view/id/10229
Request path: towel-set.html Store ID: 2 Target path: catalog/product/view/id/10224

I tried to check but there are no any duplicate entries:
select * from enterprise_url_rewrite where request_path like 'towel-set%' and store_id = 2

Gives only 1 result

select request_path, store_id, count(*) from enterprise_url_rewrite group by request_path, store_id having count(*) > 1

Gives empty result set.

Any idea what can be the issue?

[victor-v-rad]

Try auto_resolve_urlrewrite_duplicates = 1 in your config.xml

[MagePsycho]

I got the same duplicate error but this time as [INFO] level instead of [ERROR]

After further investigation, it seems that I have same request path for different target_path, as in above example.

[victor-v-rad]

But in this case it resolves your duplicates

[MagePsycho]

@victor-v-rad You mean if we fix other integrity check issues and when data migration starts, it should fix this Url rewrites issues?

[MagePsycho]

Further investigating,
Most of the duplicate rewrite has additional /category/XXX in the path, for example:

Request path: electronics/accessories/iphone-charger.html Store ID: 2 Target path: catalog/product/view/id/7940
Request path: electronics/accessories/iphone-charger.html Store ID: 2 Target path: catalog/product/view/id/7940/category/708

Is it safe to delete either of one (if yes which one?) in M1's rewrites table?

[victor-v-rad]

You do not have to delete records from your Magento 1 DB. After you set auto_resolve_urlrewrite_duplicates = 1 the tool shows you [INFO] massage instead of [ERROR], resolve the duplicates and write not conflicting records into your Magento 2 url_rewrite table. Does it do as I described? Which one of the two was added to your Magento 2 url_rewrite table?

[MagePsycho]

Does it do as I described? Which one of the two was added to your Magento 2 url_rewrite table?

It's failing at integrity check level.

[victor-v-rad]

Hi @MagePsycho
You could share your DB dump of the next Magento 1 tables:
enterprise_url_rewrite
catalog_category_entity_url_key
catalog_product_entity_url_key
enterprise_url_rewrite_redirect
catalog_category_product
catalog_product_website
core_store
cms_page
cms_page_store
You can send it through the email (find it in my profile https://github.com/victor-v-rad)
We will try to migrate it.

[digitalredefned]

@victor-v-rad - I'm running into the same issue - only 1 instance of value causing the issue(verified with query) and have tried the auto_resolve_urlrewrite_duplicates setting change as well. What's interesting is that the same number of records are in url_rewrite in the M2 db as in core_url_rewrite of the M1 DB when it fails so it looks like everything is being imported before the duplicate entry error is thrown.

[digitalredefned]

So Victors last post here got me thinking and started looking at the other tables. I noticed that the cms_page table had an entry with an identifier that was the same as the category that was throwing the error. Just as a test, I changed the identifier value and the migration was able to complete. Is this a bug or something I just need to account for somehow?

[victor-v-rad]

Is this a bug or something I just need to account for somehow?

It is a bug in your case and fix for it will be available with 2.1.1 release of the tool.

[digitalredefned]

Thanks!

[victor-v-rad]

It's failing at integrity check level.

Internal issue MAGETWO-57435

[victor-v-rad]

Hi @digitalredefned
Could you send us the tables I mentioned earlier. We have not managed to reproduce the issue yet and it would give us a clue to root cause.

[VignaPranav]

Hi @victor-v-rad ,
I am facing the same url rewrite issue. Could you suggest me the solution to resolve it?

[not-art]

If anyone else has issues with consistent integrity check failures for duplicate index keys, despite being sure your M1 instance has no duplicate keys in enterprise_url_rewrite, you may have the issue we were having:

Our URL re-write slugs for products in m1 (catalog_product_entity_url_key table) had a lot of catalog_product_entity_url_key.value's at 255 or around it in character length. 255 is the max length for the url slug.

Now, when m2 is migrating the url rewrites, it seems to first pull through the enterprise_url_rewrite table (which holds your auto-generated url re-writes, and manually added ones from the admin site), and then processes the individual entity tables (catalog_product_entity_url_key, catalog_category_entity_url_key, cms_page,etc...).

SO! With url rewrite slugs near the varchar(255) max, and given the fact you need url rewrites for products in categories, the data migration tool was not catching the duplicates for the pre-generated urls from enterprise_url_rewrite & the freshly created ones from the processing of catalog_product_entity_url_key. It'd start to write a record using the category first, then take as much of the slug value as it could, so (and for the-below example, imagine we already ensured no store_id duplicate issues; for us, we have 1 store, so the two-column index isn't being broken by the store id):

record in enterprise_url_rewrite.request_path:
electronics/video-games/imagine_this_is_255_characte

original slug in catalog_product_entity_url_key.value:
imagine_this_is_255_characters_long

• data migration tool migrates "electronics/video-games/imagine_this_is_255_characte" without issue
• data migration tool generates re-writes for "imagine_this_is_255_characters_long", by appending the category to the request_path, and takes as much of the slug value (up-to 255), making "electronics/video-games/imagine_this_is_255_characte"
• the insert fails for the duplicate request_path record causing a dupe-index key failure for the truncated url slugs value.

We fixed this by doing a pre-migration data update, appending a "m-" to all entries in "catalog_product_entity_url_key.value", EG:

UPDATE catalog_product_entity_url_key SET value = CASE WHEN LENGTH(catalog_product_entity_url_key.value > 253) THEN LEFT(CONCAT('m-', catalog_product_entity_url_key.value), 255) ELSE CONCAT('m-', catalog_product_entity_url_key.value) END

This allowed our url rewrites to complete without duplicate key error.

Given that url key slugs, when combined with the category slug, can sometimes exceed 255 and cause truncation issues, and given that index keys in MySQL have a limit on max-length for the key that can exceed slug lengths, we also recommend adding a generated varchar(64) column to url_rewrite in m2, which has a computed SHA2 hash of the request_path & store_id, dropping the original two-column index across request_path and store_id, and making a new single index on the new hash. As this is an auto-generated column, it will re-gen on inserts and updates, thus keeping the hash up to date with changes to the request_path, EG:

Create hashed column for url_rewrite uniqueness

ALTER TABLE url_rewrite ADD COLUMN request_path_hash varchar(64) GENERATED ALWAYS AS (sha2(concat(request_path, store_id), 256)) STORED after metadata;

DROP ORIGINAL MULTI-COLUMN INDEX

DROP INDEX URL_REWRITE_REQUEST_PATH_STORE_ID ON url_rewrite;

RECREATE INDEX ON A SINGLE COLUMN, WHICH IS A SHA2(CONTACT(), 256) of the original 2 columns (request_path and store_id)

CREATE UNIQUE INDEX URL_REWRITE_REQUEST_PATH_STORE_ID ON url_rewrite(request_path_hash) USING BTREE;

NOTE: This is a core change to base magento structure / m2 OOB db schema; use at your own risk ONLY IF your company is generating url slugs that approach the 255 character limit. If you're SEO-friendly and have <100 to 120 char length slugs, this is likely overkill. FWIW / HTH other m2 migration devs out there... :)