update 5-1 to 5-2 gives user warning: Duplicate entry '611-thumbnail' for key 1 query in image_update_5200

On http://drupal.org/node/207198#comment-682539 drewish was supposing this error happens when using old version of MySQL like 4.0. Is 4.1.22 considered old too?

yngens, it sounds like there's some duplicate derivative images that's screwing up the INSERT. do you have a db backup that you can look at to see what's going on with node 611?

hi drewish,

i looked at and could not find anything wrong. just to go through i deleted node 611 and run the failed query once again. and once again it found another duplicate entry. so i just went ahead and completely emptied 'image' field and run INSERT INTO image query again. this time another error messages appeared in the logs:

Unknown table 'image' in field list query: SELECT DISTINCT(node.nid), image.nid AS image_nid FROM node node INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'content_access_rid') OR (na.gid = 1 AND na.realm = 'forum_access'))) AND ( (node.status = '1') AND (node.type IN ('image')) ) LIMIT 0, 3 in /home/mysite/public_html/includes/database.mysql.inc on line 155.

i'm afraid 2.x-dev is not ready for use. i will try to revert back to 1.x-dev now, and hopefully will get my pictures to appear again.

you dropped the image table? that would cause the problem you describe in #3... i think the correct thing to have done would have been to just "delete from {files} where nid=611" and "delete from {image} where nid=611".

no, i just deleted node/611. not in mysql, but within my drupal installation.

Very same problem here. I reverted to 1.6, but I'd like to know what to do to cleanup: just drop table {image} ?

Thanks,

Xav

I got error on upgrading 1.5 to 2.x (jan. 10th "fixed") on both mysql 4.1 and 5
Maybe because of empty {file_revisions} table.
Nothing droped. And i also got emty gallery bug.

Marking as duplicate of #208785: image_update_5200 fails on MySQL < 4.1

Sorry, but to me this is still an issue with MySQL 5.0.67, and I'm finding it impossible to upgrade from working 5.1.9 or 5.1.x-dev to 5.2.x dev without getting some variation of the following warning:

Duplicate entry '209-preview' for key 1 query: INSERT INTO image SELECT DISTINCT f.nid, f.fid, f.filename FROM files f INNER JOIN node n ON f.nid = n.nid WHERE n.type='image' AND f.filename IN ('_original', 'original', 'thumbnail', 'forhåndsvis', 'preview')

So the mysql version is not the problem, but the mess in the image and files table causes duplicate keys in the image table.

Says I'm supposed to upgrade to 5.2 version before upgrading to 6.x, but when this is impossible I'm kinda stuck...
Edited

If you think the assigned status is wrong, then you should explain why. Reverting status.

Table files have primary key at column fid. New table image have primary key nid & image_size. No fid ! Here is a problem. Where I change primary key to fid in table image, error in sql statement INSERT INTO image SELECT DISTINCT f.nid, f.fid, f.filename FROM files ..... is away. But all image from galleries is missing :-(.

I am attaching result set from query :

SELECT DISTINCT f.nid, f.fid, f.filename FROM files f INNER JOIN node n ON f.nid = n.nid WHERE n.type='image' AND f.filename IN ('_original', 'thumbnail', 'preview') order by nid

I am using modul img_assist too.

@rudolf119: Which MySQL version are you on?

MySQL is 5.0.51a (Mandriva 2008.1)

Subscribing

Um, I posted to the bug that this one is marked as a duplicate of, but then I realized I don't have the mySQL version specified.

On mySQL 5.0.51a, I get the following error upgrading to Image-5.x-2-alpha:

user warning: Duplicate entry '50104-thumbnail' for key 1 query: update_sql INSERT INTO def_image SELECT DISTINCT f.nid, f.fid, f.filename FROM def_files f INNER JOIN def_node n ON f.nid = n.nid WHERE n.type='image' AND f.filename IN ('_original', 'thumbnail', 'preview', 'artikel-bild') in /var/www/virtual/alfa.fria.nu/includes/database.mysql.inc on line 174.

The error is caused by duplicate entries, of both _original and derivatives. I have no idea what causes the dupes, but I do know a dirt simple fix. Amend the INSERT to:
INSERT INTO {image} SELECT DISTINCT f.nid, f.fid, f.filename FROM {files} f INNER JOIN {node} n ON f.nid = n.nid WHERE n.type='image' AND f.filename IN ('_original', 'thumbnail', 'preview', 'artikel-bild') GROUP BY nid,filename
This will make the index unique, and since that isn't guaranteed by the original table structure - this seems to be the more correct SQL statement.

@15
Nicely summed up Ibn al-Hazardous
The fix works for me.

I too am unsure where the dupes came from.

Regards
Alan

You can get rid of the dupes in your files table like this:

Update: Note the binary on the filepath - without this the check will be done (well, at least it was on my system) case insensitive and you may end up removing too many rows (as I first did!).

Sadly, although fixes the SQL issue I still can't upgrade from 1.9 without loosing my galleries. But that's another issue (to do with using image views - turn them off before looking at your galleries).

I subscribe to this. I have the same problem during suggested migration to 2.x branch. I tried to remove duplicates, as raintonr suggested, but still no luck.

My SQL database is 5.0.51a, on Ubuntu 8.04 server.

I also subscribe to this since I have the same problem.

Committing the attached patch based on the fix by Ibn al-Hazardous.

(And marked a heap of duplicates.)

Automatically closed -- issue fixed for 2 weeks with no activity.

Reopening.
Just had #623668: Image table needed rebuilding on upgrade filed as a duplicate, so it looks like the GROUP BY method isn't working in all cases.

The suggestion from that issue is to use INSERT ... ON DUPLICATE KEY UPDATE, like this:

Though we can take out the GROUP BY as it was another attempt at fixing this bug (see above).

However, I've just been for a wander through the MySQL docs, and maybe we can use REPLACE instead?

Any thoughts from MySQL experts on whether these will work, and which is best?
BTW, neither of these work on Postgres.

Also, does anyone have pre-5--2 files / node tables they could dump and upload that we could test on?

I have set up a dummy database with just files and image tables, using the files table from comment #11 above.
The node table is irrelevant -- it serves only to limit the query by node type, and we can remove the WHERE on filenames too.
So our basic query is:

INSERT INTO image SELECT f.nid, f.fid, f.filename FROM files f

Here are my results:

// 1. just DISTINCT -- fails, but we knew that anyway
INSERT INTO image SELECT DISTINCT f.nid, f.fid, f.filename FROM files f

so the distinct is probably pointless. Let's ditch it.

// 2. works for me if image table is empty first
INSERT INTO image SELECT f.nid, f.fid, f.filename FROM files f GROUP BY nid,filename

// 3. REPLACE
REPLACE INTO image SELECT f.nid, f.fid, f.filename FROM files f GROUP BY nid,filename

works no matter what is in the image table

// 4. on duplicate key
INSERT INTO image SELECT f.nid, f.fid, f.filename FROM files f GROUP BY nid,filename ON DUPLICATE KEY UPDATE nid = VALUES(nid)

for some reason it didn't like 'nid = f.nid', said unknown column. The above worked though.

So I'm not sure what caused the current code (method 2) to fail -- how did the GROUP BY still produce duplicates?