Duplicated & missing product rows in system product export

[wbyrnetx]

Steps to reproduce

1. Install Magento from 2.0.2 tag (the one we tested on, but likely affects all versions).
2. Create/import enough products so that when running an export, the main product query will have to use offsets to batch the results
3. Look at the CSV and you will find duplicated product entries and some products that are missing altogether. From my testing, the total count is always correct and matches a SELECT COUN(*) FROM catalog_product_entity.

I spent some time looking into what may be causing this, and I believe it has to do with the ORDER BY has_options clause in the MySQL query. What I'm not sure about, is why the Magento export orders by that field in the first place.

When combining that ORDER BY with an OFFSET, the MySQL result order is inconsistent.

This can be observed by running the following queries against the Magento2 sample database.

1. Query the first 10 products
   SELECT entity_id, sku FROM catalog_product_entity AS e ORDER BY e.has_options ASC LIMIT 10;

2. Query the first 20 products
   SELECT entity_id, sku FROM catalog_product_entity AS e ORDER BY e.has_options ASC LIMIT 20;

If you compare the result set... you'll see what I'm talking about. For me, the first row in query 1 has entity_id 9347. In query 2, the first row has entity_id 90. Once you introduce offsetting the results, you get the same problem.

My proposed solution would be to order by entity_id in the export query, as that will always produce consistent result ordering, even with limits and offsets. This would require a change in Magento\CatalogImportExport\Model\Export\Product:: export

However, without knowing why it's sorted by has_options in the first place, I can't be sure.

[BaDos]

Hi @wbyrnetx

Thank you for reporting.
We've created an internal ticket MAGETWO-58337 to resolve this issue.

[Ctucker9233]

I've just noticed this prolem in 2.1.1. Seems to happen to random products. Looking at the product entity table I can't see the duplications where they show in the exported .csv. @mmansoorebay does this commit fix this problem in 2.1.1 or is there a patch pending?

[wujashek]

+1 Looking forward for this issue to be resolved.

[harimayco]

+1 i'm also facing this issue in magento 2.1.3.

[jontay27]

+1, Will this be resolved soon?

[ScottBadger]

I'm seeing the same thing as harimayco, and when I remove the duplicate rows, make my edits and re-import, I get a "Number of columns does not correspond to the number of rows in the header" error. I also tried copying the non-duplicate rows into another csv, made the edits there and imported but got the same error. Any way to use the "good" rows from the export in an import? Also, I can export/import other product's variations without a problem,, and no real difference that I can see with those I can't..... Anybody know why some products work and others don't? Thanks, Scott

[ScottBadger]

So.... for me at least, it turned out to be an ampersand in the descriptions (see http://stackoverflow.com/questions/39906595/sign-in-magento-2-0-product-import-is-causing-errors ). Changed the "&" to "and" and voila!...no dupes in the export. Made my edits and imported without errors.. Scott

[jaredchu]

So "&" character cause this issue, has anyone solution for it?

[jaredchu]

we found that _ character in product image also cause this issue

[harimayco]

@jaredchu fix for duplicate product in export #7350

[veloraven]

This issue was fixed for develop branch.
Internal ticket for ver. 2.0 - MAGETWO-62439
Internal ticker for ver. 2.1 - MAGETWO-58548

[magento-engcom-team]

@wbyrnetx thank you for your report.
The fix for this issue is already fixed in 2.2.0 release

[simonmaass]

I am having the exact same problem as described above! I am using magento 2.2.5!

The only difference that i can see between the products that exportet fine and the ones that have dublicates is the field "custom_options" - it looks like prducts with custom options get wrong export with dublicate products