how to rebuilding URL rewrite?

[shuaiZend]

Steps to reproduce

1. login phpmyadmin
2. TRUNCATE tables catalog_url_rewrite_product_category and URL_rewrite
3. run cli bin/magento index:reindex

Expected result

1. Rebuilded all page URL rewrite

Actual result

1. 404

[daim2k5]

I tried to "truncate" the table and got a

Cannot truncate a table referenced in a foreign key constraint (magento2.catalog_url_rewrite_product_category, CONSTRAINT FK_BB79E64705D7F17FE181F23144528FC8 FOREIGN KEY (url_rewrite_id) REFERENCES magento2.url_rewrite (url_rewrite_id))

What was wrong with the URL's in the database?

[shuaiZend]

I use phpmyadmin truncate tables.
can truncate.

[maksek]

What is purpose of truncating tables? It is not expected to do, and do not expect to support such actions.

[dexterityzx]

I truncated the url_rewrite table because I met the error - 'URL key for specified store already exists.'

I followed the suggestions in the discussion list and it did work.

However, the rewritten url never came back even I manually input the url and saved the categories again.

[daim2k5]

@dexteritycc yes but truncating is not the solution. I also think that your FOREIGN KEY are not ON?

[dexterityzx]

@daim2k5 Do you mean the FOREIGN KEY constraints?

[daim2k5]

@dexteritycc yes

[piotrekkaminski]

You should not modify the database. I will close this one and discussion can continue in #1471

[jordanbrauer]

@daim2k5 I have found a solution that sort of works if you have not dealt with this yet (year late, but idk /shrug). If so, this is here for anyone else coming across this issue as well.

Steps for Solution

1. Run the following SQL queries from the command line or phpMyAdmin,

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE TABLE url_rewrite; 
SET FOREIGN_KEY_CHECKS = 1;

2. Within your backend, navigate to Products > Catalog.
3. Clear any and all filters, select all products (drop down menu option Select All).
4. Select the Update Attributes action from the Actions dropdown menu.
5. Under Products Information, click the Websites tab.
6. Under the Add Product to Websites section, check the box for Main Website (and any other sites you may have)
7. Click Save.
8. Navigate to Products > Categores.
9. For each category you want to have a new URL key written, select it, update the URL key, ensure that the Create Permanent Redirect for Old URL checkbox is checked.
10. Click Save.

Now your URL rewrite table will be filled with only your used rewrites. I don't recommend doing this if your store has been live for quite some time (people may have bookmarks, and your site may be indexed by search engines already - potentially harming your SEO rankings).

---

@piotrkwiecinski That's not a very helpful answer and issue #1417 is not the same as what is happening here.

What we are looking to have done is reindex all URL rewrites (cms, product, and category) to remove unused/old records. It's an inefficient aspect of Magento 2 to allow the url_rewrite table get so large (one user in another issue has reported 800mb !!!?) for no real reason that I can think of. If it is something to do with SEO - don't assume this for us users.

What you have linked to, is users having issues with importing via .csv files. While I understand that they are receiving a similar error that could be very well caused by the same module (category/product url rewrite modules), that's no reason to close the issue and link an unrelated cause to the a similar error.

[lathadev]

@jordanbrauer

We need to truncate both the tables url_rewrite and catalog_url_rewrite_product_category table

after we need to follow the steps otherwise we can reindex using the following commands

sudo php bin/magento indexer:reindex
sudo php bin/magento indexer:reindex catalog_product_attribute