Updating the values of "catalog/seo/category_url_suffix", "catalog/seo/product_url_suffix" and "catalog/seo/product_use_categories" can cause Magento to report on URL rewrite conflicts that don't exist in the url_rewrite table

[simonworkhouse]

Updating the values of "catalog/seo/product_url_suffix", "catalog/seo/category_url_suffix" and "catalog/seo/product_use_categories" can cause Magento to fail to update the entries in the url_rewrite table while showing an error with a link to a non-existent rewrite.

Preconditions

1. Magento 2.2.5
2. PHP 7.1.20
3. MySQL 5.7.23

Steps to reproduce

1. composer create-project --repository-url=https://repo.magento.com/ magento/project-community-edition {install dir}
2. cd {install dir}
3. ./bin/magento setup:install --backend-frontname=? --db-host=? --db-name=? --db-user=? --db-password=? --base-url=? --admin-user=? --admin-password=? --admin-email=? --admin-firstname=? --admin-lastname=?
4. ./bin/magento deploy:mode:set developer
5. ./bin/magento app:config:dump
6. ./bin/magento cache:disable
7. Confirm that the values for "catalog/seo/product_url_suffix" and "catalog/seo/category_url_suffix" are ".html" and the value for "catalog/seo/product_use_categories" is "0" (the default values)
8. Proceed to the admin panel and create a sub-category called "Sale" with the URL key of "sale"
9. Create a sub-category under "Sale" called "Sandwiches" with the URL key of "sandwiches"
10. Update the value of "catalog/seo/product_url_suffix" and "catalog/seo/category_url_suffix" to ".php" and run "./bin/magento app:config:import"
11. Create a product called "Sandwiches" with the URL key of "sandwiches" and add it to the "Sale" category
12. Update the values for "catalog/seo/product_url_suffix" and "catalog/seo/category_url_suffix" to ".test", the value for "catalog/seo/product_use_categories" to "1" and run "./bin/magento app:config:import"
13. Update the URL key for the "Sale" category from "sale" to "sale-test"

Expected result

The URL keys for categories should never have been allowed to conflict like this and any warnings linking to URL rewrites should always link to existing entries.

Actual result

An error stating the following is displayed:

The value specified in the URL Key field would generate a URL that already exists.
To resolve this conflict, you can either change the value of the URL Key field (located in the Search Engine Optimization section) to a unique value, or change the Request Path fields in all locations listed below:
   
- sale-test/sandwiches.test

The link "sale-test/sandwiches.test" points to a record in the url_rewrite table that doesn't exist as the transaction that contained the insert for this entry was rolled back.

[magento-engcom-team]

Hi @simonworkhouse. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento-engcom-team give me {$VERSION} instance

where {$VERSION} is version tags (starting from 2.2.0+) or develop branches (2.2-develop +).
For more details, please, review the Magento Contributor Assistant documentation.

@simonworkhouse do you confirm that you was able to reproduce the issue on vanilla Magento instance following steps to reproduce?

• yes
• no

[engcom-backlog-nickolas]

Hello @simonworkhouse, thank you for your report. I'm not able to reproduce this issue. On step 11:

Create a product called "Sandwiches" with the URL key of "sandwiches" and add it to the "Sale" category

I've got an error:

[simonworkhouse]

@engcom-backlog-nickolas Try again. Be careful to follow the instructions in the exact order that they have been provided and monitor the changes to the url_rewrite table as you step through the process. I have just run through the process again myself.

After step 9 you should only have similar entries to the following in the url_rewrite table:

• '5', 'category', '3', 'sale.html', 'catalog/category/view/id/3', '0', '1', NULL, '1', NULL
• '6', 'category', '4', 'sale/sandwiches.html', 'catalog/category/view/id/4', '0', '1', NULL, '1', NULL

And after step 11 you should have entries similar to:

• '5', 'category', '3', 'sale.html', 'catalog/category/view/id/3', '0', '1', NULL, '1', NULL
• '6', 'category', '4', 'sale/sandwiches.html', 'catalog/category/view/id/4', '0', '1', NULL, '1', NULL
• '8', 'product', '1', 'sandwiches.php', 'catalog/product/view/id/1', '0', '1', NULL, '1', NULL
• '9', 'product', '1', 'sale/sandwiches.php', 'catalog/product/view/id/1/category/3', '0', '1', NULL, '1', '{"category_id":"3"}'