Duplicate Attribute Pictures After the nopCommerce 5.0 Upgrade: Cause and Fix

This is a data problem in the nopCommerce 5.00 upgrade that we found while reading the upgrade code for our StorePulse plugin. It only affects some older stores, it’s easy to prevent, and it’s tedious to clean up afterwards. This page explains the cause, how to check your store, and the fix before and after upgrading.

What you’d see after upgrading

• On a product page, choosing a colour or size shows the same picture twice in the gallery.
• In the admin area, an attribute value or combination lists the same picture twice.
• A picture you removed from a variant on 4.70–4.90 is back.

Product pictures themselves aren’t affected, only the pictures linked to attribute values (for example, a colour swatch’s picture) and to attribute combinations.

Why it happens

Two upgrades, four versions apart, handle the same data:

In the 4.70 upgrade code, the step that clears the old column is started but never waited for, so it can stop before it finishes. Where it stopped, the old links stay. The 5.00 upgrade then treats them as new and copies them a second time. Neither picture table prevents the same picture being linked twice.

The same leftover links explain the removed pictures coming back. Removing a picture on 4.90 deletes it from the new table, but the old column still pointed to it, so 5.00 adds it again.

Is your store affected?

• First installed on 4.70 or later: not affected. The old column was never filled.
• Upgraded through 4.70 from 4.60 or earlier: possibly. It depends on whether the 4.70 clean-up finished.
• No attribute pictures: not affected.

Check before upgrading

The free StorePulse plugin checks this as part of its 5.00 upgrade report: it counts the old links and how many of them would become duplicates. Or run this query on the 4.90 database:

-- SQL Server, on the 4.90 database. Read only.
SELECT
  (SELECT COUNT(*) FROM ProductAttributeValue v
     JOIN Picture p ON p.Id = v.PictureId) AS ValueLinks,
  (SELECT COUNT(*) FROM ProductAttributeValue v
     JOIN Picture p ON p.Id = v.PictureId
     WHERE EXISTS (SELECT 1 FROM ProductAttributeValuePicture x
       WHERE x.ProductAttributeValueId = v.Id AND x.PictureId = v.PictureId)) AS ValueDuplicates,
  (SELECT COUNT(*) FROM ProductAttributeCombination c
     JOIN Picture p ON p.Id = c.PictureId) AS CombinationLinks,
  (SELECT COUNT(*) FROM ProductAttributeCombination c
     JOIN Picture p ON p.Id = c.PictureId
     WHERE EXISTS (SELECT 1 FROM ProductAttributeCombinationPicture x
       WHERE x.ProductAttributeCombinationId = c.Id AND x.PictureId = c.PictureId)) AS CombinationDuplicates;

Links is how many rows the 5.00 upgrade would copy. Duplicates is how many of those are already in the new table and would show twice. Links that aren’t duplicates are usually pictures that were removed after 4.70 and would come back.

Fix before upgrading: clear the old links

Since 4.70, nopCommerce doesn’t read the old column at all. What you see in the 4.90 admin area is what’s in the new tables. Clearing the old column changes nothing on 4.90, and leaves the 5.00 upgrade nothing to copy, so products look exactly the same after the upgrade.

-- SQL Server, on the 4.90 database. Back up first.
-- 4.90 never reads these columns, so the storefront doesn't change.
UPDATE ProductAttributeValue SET PictureId = NULL WHERE PictureId IS NOT NULL;
UPDATE ProductAttributeCombination SET PictureId = NULL WHERE PictureId IS NOT NULL;

Run the check query again afterwards: both link counts should be 0. On MySQL or PostgreSQL the same statements work as written. Do this on the copy you test the upgrade with too.

Already upgraded? Remove the duplicates

Once you’re on 5.00 the old column is gone, so the duplicates have to be removed from the new tables. This keeps one row for each picture on each value or combination:

-- SQL Server, on the 5.00 database. Back up first.
-- Keeps the oldest row of each repeated picture and deletes the copies.
WITH d AS (
  SELECT ROW_NUMBER() OVER (PARTITION BY ProductAttributeValueId, PictureId ORDER BY Id) AS n
  FROM ProductAttributeValuePicture)
DELETE FROM d WHERE n > 1;

WITH d AS (
  SELECT ROW_NUMBER() OVER (PARTITION BY ProductAttributeCombinationId, PictureId ORDER BY Id) AS n
  FROM ProductAttributeCombinationPicture)
DELETE FROM d WHERE n > 1;

Then click Clear cache in the admin header. Pictures that came back after being removed aren’t duplicates, so this doesn’t touch them. Compare the affected products with a 4.90 backup and remove those pictures in the admin area.

This is one of several things to check before a 5.00 upgrade. Our upgrade guide covers the process, and the nopCommerce 5.0 overview lists the other changes.

Frequently asked questions

Next steps

Run StorePulse or the check query on your 4.90 store. If the counts are above zero, clear the old links before you upgrade. If you’ve already upgraded and the catalogue is large, we can clean it up for you, or check for it as part of our nopCommerce 5.0 upgrade service.