URL rewrites not being generated - calls to doReplace() actually insert instead.

[simonworkhouse]

We have been experiencing an intermittent issue where entries into the url_rewrite table are not being generated automatically. I have noticed that this issue has been mentioned in the past but I believe that we have isolated the cause.

Magento\UrlRewrite\Model\Storage\DbStorage::doReplace(...) doesn't do a REPLACE but an INSERT, and errors for duplicate entries aren't throwing an AlreadyExistsException as expected.

A general gist of the code path is as follows:
Magento\CatalogUrlRewrite\Model\UrlRewriteBunchReplacer::doBunchReplace(...) ->
Magento\UrlRewrite\Model\Storage\DbStorage::doReplace(...) ->
Magento\Framework\DB\Adapter\Pdo\Mysql::insertMultiple(...) ->
Magento\Framework\DB\Adapter\Pdo\Mysql::insertArray(...)

When we updated the doReplace(...) and insertMultiple(...) functions to pass the replace strategy through to insertArray(...) everything worked as expected. Would I be correct in assuming that this was the intended strategy and that is was just missing from doReplace and insertMultiple? Why would it not be a replace?

Also, any calls to Magento\Framework\DB\Adapter\Pdo\Mysql::insertMultiple(...) will fail if there are duplicates in the data based on unique constraints. This function doesn't take this into account and doesn't have the ability to pass the strategy through to insertArray(...). There also doesn't appear to be an equivalent replaceMultiple(...).

I would guess that this might be causing some other bugs we have noticed, particularly with importing catalog data from a CSV.

Preconditions

1. Magento 2.2.5
2. PHP 7.1.20
3. MySQL 5.7.23
4. Multiple stores set up and enabled with the same products and root category

Steps to reproduce

Assuming that the category data will fail for a duplicate entry:

1. Update the URL key for a category

Actual result

Received the following MySQL error running the query that Magento genereted:
Error Code: 1062. Duplicate entry 'some-product-1' for key 'URL_REWRITE_REQUEST_PATH_STORE_ID'

An AlreadyExistsException didn't appear to have been thrown, or was silently handled.

Expected result

The correct entries in the url_rewrite table are created.

[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

@simonworkhouse, thank you for your report.
We were not able to reproduce this issue by following the steps you provided. Please provide more detailed steps to reproduce or try to reproduce this issue on a clean installation or latest release.

[simonworkhouse]

Has a human actually read the bug report? As I stated, this is an intermittent issue that happens when the catalog data is under certain conditions. Unfortunately I cannot simply provide an SQL dump for obvious reasons (privacy issues, etc...) and it could take quite some time for me to generate a CSV (or series of CSVs) that one could use to import into a clean installation that simulates these conditions.

This insistence on everything needing to be immediately reproducible from a clean installation is nonsense, particularly when the details of the issue can be clearly seen if one just reads the bug report and reviews the code.

Here are a number of bug reports related to this issue:
#6606
#8227
#12662
#5863

Clearly there's a serious issue with the way that the URL rewrites are generated and updating the Magento\UrlRewrite\Model\Storage\DbStorage::doReplace(...) function to actually do a REPLACE fixes the issue in our instance. Also, the issues with Magento\Framework\DB\Adapter\Pdo\Mysql::insertMultiple(...) need to be noted and someone needs to manually review this.

@simonworkhouse, we believe that this issue exists. But we cannot add issue to our work backlog and fix it until we have obvious and 100% clear way to reproduce it.

[orlangur]

@simonworkhouse,

Magento\UrlRewrite\Model\Storage\DbStorage::doReplace(...) doesn't do a REPLACE but an INSERT

There is nothing wrong with that, method names should not be mixed up with underlying SQL.

Please provide an isolated test case which reproduces a problem on a clean Magento installation.

[simonworkhouse]

@orlangur

There is nothing wrong with that, method names should not be mixed up with underlying SQL.

I do realise that, but the problem is due to the fact it is actually trying to perform the action of a replace with an insert. There doesn't appear to be any protection to prevent duplicates in the batch update either, so batches will fail if there are ever any duplicates. Additionally, the exception that is supposed to be thrown on duplicates doesn't ever appear to be thrown. MySQL logs indicate that the transaction was rolled back so could it be possible that the exception wasn't thrown due to this?

The query that Magento was trying to run generated the MySQL error "Error Code: 1062. Duplicate entry 'some-product-1' for key 'URL_REWRITE_REQUEST_PATH_STORE_ID'" and updating the code to perform a REPLACE fixed the issue for me. Whether or not that's a byproduct of another bug/issue I can't be sure without digging further, but from reviewing the code I would suggest that it was probably intended to perform a replace initially.

I will have to spend a bit of time setting up a test case for you, as I can't be certain if it has anything to do with a multi store setup, CSV imports, the category structure, something else or a combination of these. You may have to wait a little while for that as I don't have too much time available at the moment. However; I would have thought that reviewing the code would highlight the issue clearly enough. Is there any reason as to why it's not performing a REPLACE?

[orlangur]

@simonworkhouse because old URL rewrites are removed prior to inserting:

protected function doReplace(array $urls)
    {
        $this->deleteOldUrls($urls);

        $data = [];
        foreach ($urls as $url) {
            $data[] = $url->toArray();
        }
        try {
            $this->insertMultiple($data);

Feel free to reopen issue whenever you find out exact steps.

[simonworkhouse]

@orlangur

because old URL rewrites are removed prior to inserting:

Yes, I had noticed that. However; it's not necessarily the conflict with the existing URLs that is the issue, it appears that it might be the data that it's trying to insert that contains the duplicates.

Are we able to have this issue re-opened please?