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

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?

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".

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:

Update #5200
    * CREATE TABLE {image} ( `nid` INTEGER UNSIGNED NOT NULL, `fid` INTEGER UNSIGNED NOT NULL, `image_size` VARCHAR(32) NOT NULL, PRIMARY KEY (`nid`, `image_size`), INDEX image_fid(`fid`) ) /*!40100 DEFAULT CHARACTER SET utf8 */;
    * Failed: 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')
    * DELETE FROM {file_revisions} WHERE EXISTS (SELECT * FROM {image} WHERE {image}.fid = {file_revisions}.fid)

The error message said:

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!).

create table dupe_files as (select min(f.fid) from files f group by binary(f.filepath) having count(*) > 1);

delete from files where fid in (select * from dupe_files);

drop table dupe_files;

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.)

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:

$ret[] = update_sql("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" . $cond . " GROUP BY nid,filename on duplicate key update nid=files.nid");

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?

$ret[] = update_sql("REPLACE 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" . $cond . ");

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?

try this ! :

http://drupal.org/node/418220#comment-2849102

entry #20

We have to actually fix this in code in order to release a 1.0 version of the module -- we can't expect users to have to go delve into their database to upgrade.

Any ideas on which of the approaches in comment #23 to try would be much appreciated.

Note to self: try the patch at #583076: Error on Database Updates for 6100 when upgrading to Beta3: 'duplicate entry' for primary key with my test DB, assuming I still have it.

Committed this patch.

I've seen with similar problems on other project that the best way is with INSERT IGNORE, so rolling back the GROUP BY the earlier patch put in and changing to that.

I was discussing at the CPH code sprint on Sunday that so few people need to upgrade from 5 to 6 now that we really shouldn't let this hold us back. So this is going in and then it's time for a 1.0 release!

#207557 by joachim: (Revisited) Fixed update errors due to duplicate data in Drupal 5 files table.

I have the same problem. I tried to upgrade from version 5-1.9 to 5-2.0-alpha4 and -dev version too, and I always get the warning, and the Failed message. Its blocking me in a full 5 -> 6 upgrade. Any patch, or manual update docs would be appreciated.

Oh, I probably need to apply the patch to the 5 branch so the 5200 update function gets the fix too...

Urgh... seems the 5--2 branch is behind by several commits that affected update 5200.

@Scolo -- can you apply this patch and try the update again please?

Thanks for the patch, it solves the problem for me.

Brilliant!

Thank you so much for testing this Scolo! :D

I'll make a new alpha release on the 5--2 branch and hopefully we can finally put this issue to bed!!!

#207557 by joachim: (Revisited) Fixed out of sync image_update_5200() with commits from DRUPAL-6--1 branch; fixed update errors due to duplicate data in Drupal 5 files table.