I tried a test upgrade on a staging site for an old project...

I had Drupal 5.ancient and image 5-1.

I got this warning when upgrading:

user warning: Duplicate entry '31-thumbnail' for key 1 query: INSERT INTO image SELECT DISTINCT u.nid, f.fid, f.filename FROM upload u INNER JOIN files f ON u.fid = f.fid INNER JOIN node n ON u.nid = n.nid WHERE n.type = 'image' AND f.filename IN ('_original', 'thumbnail', 'preview', 'feature') in D:\xampp\htdocs\drupal-work-newport-upgrade\sites\all\modules\image\image.install on line 271.

My upgraded site seems fine, but some nodes have no images -- node 31 and nodes with higher nids than that.
So basically the SQL error caused the transfer of data from {files} to {images} in image_update_5200 to stop.

Here's the relevant bit of my OLD file table from the 5-1 database:

60  31   _original  files/images/Playground.jpg   image/jpeg    133339
61  31  thumbnail   files/images/Playground.thumbnail.jpg   image/jpeg  3632
62  31  feature     files/images/Playground.feature.jpg   image/jpeg  18162
63  31  thumbnail   files/images/Playground.thumbnail.jpg   image/jpeg  3632
64  31  feature     files/images/Playground.feature.jpg   image/jpeg  18162

Trying to run just that one query on this in phpmyadmin produces the same error, because there are 2 entries with nid 31 and 'thumbnail'. Obviously this is bad data, and I have no idea how it came about.

But have we had other reports of missing images after upgrade? This might be one of the causes.

On the plus side, upgrade from 5-1 to 6-dev worked perfectly apart from this. I have images, I can make new images, I have attached images and I have galleries!
The galleries taxo lost images though -- have we not committed that fix yet?

Comments

sun’s picture

sun
Primary language German
Location Karlsruhe
commented

IRC?

joachim’s picture

joachim commented

Hmm okay I see a few duplicates of this... oops :D

Looking at another old site, I get the same error for 100-thumbnail, but my data seems ok:

  	Edit  Delete   	100  306  	preview
	Edit 	Delete 	100 	305 	thumbnail
	Edit 	Delete 	100 	307 	feature
	Edit 	Delete 	100 	304 	_original

Baffled....

sun’s picture

sun
Primary language German
Location Karlsruhe
commented

No IRC available?

So the issue seems to be that SELECT DISTINCT u.nid, f.fid, f.filename does not seem to be taken into account.

Potentially, because we need u.nid + f.filename to be unique. And f.fid is in between.

61  31  thumbnail   files/images/Playground.thumbnail.jpg   image/jpeg  3632
63  31  thumbnail   files/images/Playground.thumbnail.jpg   image/jpeg  3632
62  31  feature     files/images/Playground.feature.jpg   image/jpeg  18162
64  31  feature     files/images/Playground.feature.jpg   image/jpeg  18162

...is queried as:

31  61  thumbnail
31  63  thumbnail
31  62  feature
31  64  feature

But we want:

31  thumbnail  61
31  feature    62

Two options:

a) Remove {img_assist}.image_size from primary key.

b) Refactor that database update to fetch all data first, make it unique, and only afterwards insert into {img_assist}.

joachim’s picture

joachim commented

Sorry, didn't see your comment last night and thought I was only replying to myself...

I don't know enough about primary keys and what we need them for, so I'm not sure what's best.

raintonr’s picture

raintonr commented

How is this related to #207557: update 5-1 to 5-2 gives user warning: Duplicate entry '611-thumbnail' for key 1 query in image_update_5200?

joachim’s picture

joachim commented

Looks like the same error message. We should consolidate issues.

Leeteq’s picture

Leeteq commented

Subscribing.

joachim’s picture

joachim commented

Related to #424386: images saved to files table with bad data in filename column?
In haste, only have internets in passing. If someone could do the rounds of the possibly related issues and mark as duplicates if they are, that would be great.

joachim’s picture

joachim commented

It's been suggest to me that we use

REPLACE INTO
rather than
INSERT INTO

which guarantees that if a row already exists it's replaced instead.
Would that fix it?

http://dev.mysql.com/doc/refman/5.0/en/replace.html

joachim’s picture

joachim commented
Status: Active » Closed (duplicate)

Marking as duplicate of #207557: update 5-1 to 5-2 gives user warning: Duplicate entry '611-thumbnail' for key 1 query in image_update_5200, which has a fix.