Where is URL Key information stored in database?

Question

I'm using a mass import extension to import product URL Keys for a Magento 2.3.0 site which recently went live. The URL key information in the backend product detail is correctly updated but Magento continues to use a different URL key (this may have been the original key when the product was created or it may be magento default - I'm not certain). This is causing indexing problems, our generated sitemap shows the correct URL, i.e. it uses the backend URL key, but that URL is ignored by Magento, is unavailable and hence we're getting soft 404 errors from search engines.

I can force the URL key to correctly update by adding the product to our 'main website' again in the backend either from product detail or by using a mass action. This isn't a practical solution when we have over 50K products in our catalogue. It takes about 4 minutes to update 200 products this way and there are often errors (URL key already exists).

My question is where in the DB is the URL key actually stored? Is it possible to force the database to 'see' the updated URL key using SQL?

TIA

Answer 1

If I had to guess, I'd say you're updating the URL key at the global level but you have an overriding value at either the store view or the website level. Use the scope selector and double check. That's biggest culprit most of the time.

If you want to see all of your URL keys use this SQL query. Replace the 2746 with whatever the entity_id of your product is.

select a.*
from `catalog_product_entity_varchar` a
join `eav_attribute` b
    on b.attribute_id = a.attribute_id
where b.attribute_code = 'url_key'
and a.entity_id = 2746

Answer 2

@Shawn Abrahamson, thanks for taking the time to answer. It appears that the issue was a large amount of URL rewrites which were negating my URL key changes in the backend admin. The redirect type was set to 'No' - which I foolishly assumed meant they were inactive, I've since been advised that 'No' means an Apache rewrite is still used, there is simply no redirect.

Where these rewrites came from is still unknown, we did a lot of product maintenance and adjustment (50K products) in the year or so before the site went live, this may have left some legacy rewrites that we didn't need or want. The answer was to regenerate all current rewrites and import 301 redirects via script for the old URL keys that were 'lost' during migration to M2.

Answer 3

You can find the URL key information in the url_rewrite table.

Hope that helps!

Answer 4

Final urls stored in url_rewrites table by using url_key and url_path field of entity.

If you are using an importer it's good idea using a url rewrite generator after import finished Something like that https://github.com/olegkoval/magento2-regenerate_url_rewrites .

It fixes all missing url rewrites and support multi store etc.