Magento Enterprise URL re-writes from 1.12->1.13.1 are adding unnecessary characters to the end of the urls for products

Question

We are just in the process of upgrading Magento EE from 1.12 to 1.13.1 and we've hit a little snag on the url rewrites that are automatically generated from the migration script.

We run 3 different store views and most of the products are replicated through each, for example:

• www.storename.com/mens/product.html
• www.storename.com.au/mens/product.html
• www.storename.co.uk/mens/product.html

However after running the url migration script all these URLS have switched on the front-end and now display like:

• www.storename.com/mens/product-1.html
• www.storename.com.au/mens/product-2.html
• www.storename.co.uk/mens/product-3.html

I'm sure this has something to do with the system identifying these as duplicates and therefore adding -1,-2,-3 to each of the corresponding URLS.

What would be the best way to fix this? We cannot continue the upgrade because this will cause a SEO nightmare as all the URLS like /product.html will 301 to /product-1.html and so forth.

Answer 1

This usually happens when a URL key being imported already exists in the URL rewrites, resulting in incremental integers being added at the end. URL keys, which are indexed from the _url_key tables are constrained to be unique. Prior to EE 1.13, you would also sometimes get the entity IDs appended for some reason. I have not seen this particular behavior on EE 1.13 yet.

To resolve your problem, you basically need to reset your URL rewrites with the following series of queries.

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `core_url_rewrite`;
TRUNCATE TABLE `enterprise_catalog_category_rewrite`;
TRUNCATE TABLE `enterprise_catalog_product_rewrite`;
TRUNCATE TABLE `enterprise_url_rewrite`;
TRUNCATE TABLE `enterprise_url_rewrite_category_cl`;
TRUNCATE TABLE `enterprise_url_rewrite_product_cl`;
TRUNCATE TABLE `enterprise_url_rewrite_redirect_cl`;
TRUNCATE TABLE `enterprise_url_rewrite_redirect_rewrite`;
SET FOREIGN_KEY_CHECKS = 1;

I have done this previously, and it work without affecting anything else, as long as you don't have custom rewrites. Please note that this will remove all of the URL rewrites, including system ones (like ones your mentioned) and custom URLs, which have enterprise_url_rewrite.system = 0.

See this question as well for more information.

Answer 2

After some digging, this solved our issue:

DELETE url_table
FROM catalog_product_entity_url_key url_table
INNER JOIN catalog_product_entity_varchar old_url_table ON url_table.store_id = old_url_table.store_id
AND url_table.store_id <>0
AND old_url_table.store_id <>0
AND url_table.attribute_id = old_url_table.attribute_id
AND url_table.entity_id = old_url_table.entity_id;

Then re-running a reindex on the whole site, fixes the issue with -1's -2's etc... credit for this fix goes to this page: http://www.code4business.de/update-magento-enterprise-edition-1-13-0-2/#more-1144