Magento url key has strange numbers added by the system Magento 1.7.0.2

Question

I've been pulling my hairs to figure out what's going on with my Magento store (1.7.0.2 version). 80% url-keys are appended with strange numbers in two different forms. Some are like this http://www.example.com/product-name-2886-351.html, random numbers assigned after my dress style numbers, but these randomly-assigned numbers are not changing after added by the system.

Another version URL with strange numbers are like this: http://www.example.com/product-name-2926.html. Notice the style number "2926" is assigned by the system. "2886" was originally input on the URL key section when we added this product.

Some SEO people told me it's because we uploaded the CSV again and again without truncating the url_rewrite table, so it created the duplicate version of products. That's why Magento does this to differentiate products. We uploaded CSV many times at the initial stage but there are some products are not affected by this. I'm not technical at all. I'm very confused by all this, but I don't quite believe this is the reason happening here.

After reading some threads, I know there are some real experts out there. Could you please help? If we upgrade to Magento 1.8, would this problem go away?

Answer 1

Typically this is caused because you have categories or products with duplicate urls. i.e a category with www.example.com/hats and a product with same url. Every time you save the product you will incrementally increase the core rewrite. The number you see at the end of your URL. The other main culprit is configurable products.

Step 1

Make sure the URL you’re trying to achieve is in fact unique.

There is a reason Magento appends these numbers to the end of your URLs, because it actually already exists. It’s like creating two categories with the same name in WordPress, the second category will have the URL “/category-2/”.

Step 2

Do a database backup

Open up PHPMyAdmin or something similar.

Find the core_url_rewrite table.

Search for your url in the request path. In my example I typed: "hats%"
this will find anything beginning with hats in the url.

Step 3

Just remember if you hard linked this page it will now 404. So if there is a risk of this, make sure you create a new redirect after this for that page.

Delete all rows from the search results.

Step 4

Reindex your Catalog URL rewrites.

Step 5

Go back to the category or product and click save.

Your urls should now be as desired!

The upgrade will help with the underlying cause of this, but will not correct it. You will still need to run through this process regardless.

Answer 2

To remedy this issue once and for all, use this extension. It fixes a underlying Magento bug that keeps changing the ending digits of duplicate product URLs after URL rewriting re-indexes or after you change product categories.

Install the extension and make sure you check the "Unique URL Key Verification" so whenever you create or edit a product with a potentially duplicate URL key, Magento prompts you to use a different one so you don't end up with duplicate product URLs in the first place, thus no strange numbers appended at the end.