Url rewrite product category issue

[Luigi Pelosi]

Hi there,
I'm facing an issue with the product category url rewrites.
I have a Magento 2.1.6 installation which it seems that url rewrites don't work properly.
When I issue the php bin/magento indexer:reindex command all the indexes except catalog_product_category and catalogrule_product update correctly.
Taking a look to catalog_url_rewrite_product_category table I see that is empty although I have 2000 product and plus.

If I try to save a new product, that table starts to be populated starting from the latest product's id (see the screenshots).

So the question is: how can I rewrite correctly the others 2000 products' url?
I tried to do so selecting all products and re-adding them to main website through the attribute update section but no results.

I hope I was clear.

Take a look to the screenshots for a better understand.

[esaydam]

I investigate detailed whats going on here. Below is the history.

1) We decided to disable categorypath1/categorypath2/product-url format

2) But there was a bug magento configuration which has 'catalog/seo/product_use_categories' path.

3) It was creating urls altough we disabled this configuration

4) We developed a module to avoid that, caled Workaround_FixProductUrl

Another related story

• after we disabled this configuration, we redirected old product urls which contains category path.

Now, what was the root issue?

• When saving a category, magento tries to insert records (category_id,product_id,url_rewrite_id) to catalog_url_rewrite_product_category table.

• One of the process was selecting records from url_rewrite table and inserting this table. But native behaviour is selecting our custom redirects which hasnt assigned to category.
  • Information: There are different kind of redirect records. entity_type = custom are the records that we inserted manually.(or redirects from admin panel).
  • custom redirects are not assigned to any product or category directly, they are basic redirects.

• Native is selecting records which has custom type. This was the bug.

What we did?

we update meta_data field in url_rewrite table for old redirects.