Upgrade from 5.x-1.9 to 6.x-1.0 broken

You've an old image table lying around, from 4.x. Rename that and rerun that query.

@com2: Can you please elaborate in detail about your Image module version history? Which version did you install first? - do you remember single versions you updated to? - which version was installed last? (if there were development snapshots involved, we'd additionally need their dates)

Could you please try whether the upgrade works with the latest development snapshot. I recently committed some fixes for the upgrade path.

Same problem here. Wanted to upgrade from D5 to D6. All worked fine except image.

This is what i've been going through the last couple of days.

Versions :
drupal 5.22
image 1.9

files an images directory :
www.mysite.com/files/images

Simple plan : upgrade to D6.16

Had a backup ? yes(thank god) -> tip: "backup and migrate" module, put site offline, manual backup of database via "backup and migrate" Then try to upgrade.
Know something about drupal : yes

Result : 3 days without more than 3 hours sleep

What went right : 99,9 % -> drupal core and + 35 modules
What went wrong : 0,01% -> image module : all (sometimes some) image paths lost, or corrupt

seven D5 restores later, here's the verdict:

***
D5 -> D6 : image 1.9 -> image 6.x-1.x-dev directly (didn't know any better):

problem first discovered, SQL error on update (Failed) , 80% of pictures gone ( only paths )
Result : restore to D5

***
attempt2 : D5 -> D6 : image 1.9 -> image 6.x-1.0-beta5 directly, with first only core and image module ( just a test ) , no images
Result : restore to D5

***
Read everything there is to read about upgrading image and upgrading drupal ( again, and then some ) .

Most likely culprits :
SQL update on install module
files table, images table

Best help I found on the subject : upgrade image to latest image version in 5 first (clear cache etc. )

***
So I did :

D5 : image 1.9 -> 5.x-2.0-alpha4 only (!) : sql error, no images in D5, (restore )

***

D5 : image 1.9 -> 5.x-2.x-dev only (!) : sql error, no images in D5, sigh (restore )

Changing this to critical.
Probably a duplicate, but at least this is a clear description of the problem ( i hope )

Anyone ? This is getting very annoying ...

Thanks for any help,
Davy

Can you give examples of what happens to your paths?

Well, that's kind of complicated, but I'll give it a try :

**********************
Drupal 5 and image 1.9 :

files structure +example :

fid nid filename filepath filemime filesize

476 86 _original files/images/60jaar_1.jpg image/jpeg 46852
477 86 thumbnail files/images/60jaar_1.thumbnail.jpg image/jpeg 3083
478 86 preview files/images/60jaar_1.preview.jpg image/jpeg 27750

# records : 5,168

image structure + example :

nid fid image_size

86 476 _original
86 477 thumbnail
86 478 preview

# records : 1,473

nicely working

*************************

Drupal 5 image upgraded (from 1.9 to 2.x ) before (!) update.php :

( looking for the same image file )

files structure + example :

fid nid filename filepath filemime filesize

476 86 _original files/images/60jaar_1.jpg image/jpeg 46852
477 86 thumbnail files/images/60jaar_1.thumbnail.jpg image/jpeg 3083
478 86 preview files/images/60jaar_1.preview.jpg image/jpeg 27750

image struct + ex :

nid fid image_size

86 476 _original
86 477 thumbnail
86 478 preview

# records : identical

above already not showing any images !

*****************

After previous step, update.php is run. 2 SQL errors ( don't remember which )

files

(nid is gone, uid is added ):

fid uid filename filepath filemime filesize status timestamp

476 1 _original files/images/60jaar_1.jpg image/jpeg 46852 1 0
477 1 thumbnail files/images/60jaar_1.thumbnail.jpg image/jpeg 3083 1 0
478 1 preview files/images/60jaar_1.preview.jpg image/jpeg 27750 1 0

images

nid fid image_size

86 476 _original
86 477 thumbnail
86 478 preview

# records : identical

again not showing images. ( There are 0 images in this gallery, if I go to my image nodes via content list filter on image, they are all there, but the path to the image file is gone ) Why ? I don't know

**********

after previous step I upgrade to D6.16 and image 6.x - 1.0 - beta5 ( and run update.php )

files

fid uid filename filepath filemime filesize status timestamp

476 1 _original files/images/60jaar_1.jpg image/jpeg 46852 1 0
477 1 thumbnail files/images/60jaar_1.thumbnail.jpg image/jpeg 3083 1 0
478 1 preview files/images/60jaar_1.preview.jpg image/jpeg 27750 1 0

# records : 2,757 !

nid fid image_size

734 2045 original

nid 86 is gone (!) , now starting from nid : 734 and fid : 2044

# records : 1,161 !

and now it's a complete mess. Some images showing, but wrong ones and most not.
Paths incorrect or missing in image node etc etc
everything else seems to be working ( but I can't be sure of course )

This is the best I can do.

Was your SQL error anything like "Duplicate entry '611-thumbnail' for key 1 query in image_update_5200"?

If so, this is #207557: update 5-1 to 5-2 gives user warning: Duplicate entry '611-thumbnail' for key 1 query in image_update_5200.

No, I think it were these ( not entirely sure )

from : http://drupal.org/node/357793 , because i didn't save them

* Failed: 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')
* Failed: DELETE FROM {file_revisions} WHERE EXISTS (SELECT * FROM {image} WHERE {image}.fid = {file_revisions}.fid)

( the last one I think passed )

Gah! Failed create table? That's really rather bad! EVERYTHING after that is going to fail.

How old is this site -- what was its original version of Drupal and Image?

good question ;-)

I think it's 3 (4?) years old now. I don't remember if i started with D4 or D5 unfortunately. Could be 4 but I don't think so. Is there a way to see with which version I started ?
How old is D5 anyway lol

aha :

user 1 member for : 2 years 41 weeks

Knowing me I started with D5 if it was available, but still not sure.
But that shouldn't matter, should it ? I mean, everything upgraded just fine for years now.

If you started with d4, you WILL have an image table. Which means that another table named "image" cannot be created, which will result in your mess.

Start with deleting that image table (from your early d5 install, before image requires image table) (or rename it to image_bak or something). Then upgrade ...

if that's not it then sorry, I have no idea.

I was trying that now on a test site ( same DB, but on a test domain )

so far :

it's a upgraded 6 install

bad images filepaths

deleted images table

ran update.php

empty images table

now running following sql on that :

INSERT INTO image
SELECT DISTINCT u.nid, f.fid, f.filename
FROM files f
INNER JOIN upload u ON f.fid = u.fid
WHERE f.filename
IN (
'_original', 'thumbnail', 'preview'
)

MySQL rerror :

#1062 - Duplicate entry '1614-thumbnail' for key 'PRIMARY'

stopped at 700 or so records
have the same problem with the images ( some are good, some are gone )

aha !

now I am looking for that duplicate entry.

will keep you posted

seems that I also have 'original' types ( without the _ ) added that to the query

update1 :

ran this ( looking for 1614)

SELECT DISTINCT u.nid, f.fid, f.filename
FROM files f
INNER JOIN upload u ON f.fid = u.fid
WHERE f.filename
IN (
'_original', 'thumbnail', 'preview'
)

excerpt ( nid asc) :
nid fid filename

1550 3331 _original
1551 3335 _original
1552 3339 _original
1553 3343 _original
1554 3347 _original
1572 3359 _original
1573 3363 _original
1576 3367 _original
1577 3371 _original
1578 3375 _original
1581 3379 _original
1587 3383 _original
1588 3387 _original
1589 3391 _original
1590 3395 _original
1613 3399 original
1613 3400 thumbnail
1613 3401 preview
1613 3402 original
1614 3405 original
1614 3406 thumbnail
1614 3403 thumbnail
1614 3404 preview
1615 3407 preview
1615 3408 original
1615 3409 thumbnail
1615 3410 preview
1616 3411 original
1616 3412 thumbnail
1616 3413 preview
1616 3414 original
1617 3415 thumbnail
1617 3416 preview
1617 3417 original
1617 3418 thumbnail
1618 3431 preview
1618 3432 original
1618 3433 thumbnail
1618 3434 preview
1619 3426 original
*********************************************************
update :

I don't think I can restore it in this state, without copying of tables from a backup.

example :

running this :

SELECT DISTINCT u.nid, f.fid, f.filename
FROM files f
INNER JOIN upload u ON f.fid = u.fid
WHERE f.filename
IN (
'_original', 'original'
)

returns duplicates with the same node ID and filename that are pointing to different fid. I looked up the fid's and they are indeed pointing to different files(paths). The upload table is corrupt now.
So no hope for easy restoring from this state (D5 to D6 update gone wrong).
If you have no backup, there is a major problem when having this duplicates problem.

from the uploads table :

fid vid description list nid weight

3771 1883 0 1883 0
3772 1883 0 1883 0
3773 1883 0 1883 0
3774 1883 0 1883 0

3771 and 3774 are different image files

so no help there

**************************************
next update :

feeling like sherlock now ;-)

I was looking for a difference ( any difference ) and found one in the files table. Behold the timestamp ! Maybe we can work from there..

fid uid filename filepath filemime filesize status timestamp

3774 3 original files/images/100_7981-1_0.original.JPG image/jpeg 223691 1 1271174796
3773 3 preview files/images/100_8049-1.preview.JPG image/jpeg 211607 1 1271174794
3772 3 thumbnail files/images/100_8049-1.thumbnail.JPG image/jpeg 15969 1 1271174794
3771 3 original files/images/100_8049-1.original.JPG image/jpeg 334665 1 1271174794

and strangely enough,a lot of entries have timestamp 0, but only where there are no duplicates. If it is a duplicate it has a different timestamp.

****************************************

> If you started with d4, you WILL have an image table. Which means that another table named "image" cannot be created, which will result in your mess.

Indeed. Except that we fixed the update code to take that into account :/
@DavyD: can you check that image_update_5200() has a " db_rename_table($ret, 'image', 'image_old'); " line in it?

> #1062 - Duplicate entry '1614-thumbnail' for key 'PRIMARY'

You're now on the problem for that other issue I linked to.

> can you check that image_update_5200() has a " db_rename_table($ret, 'image', 'image_old'); " line in it?

I think so, because I got the image_old table :-)

> You're now on the problem for that other issue I linked to.

Yep, but I haven't solve it yet ;-)
Getting there I think. If I have solved it, I'll test it a couple of times and I'll post a working solution

Have to make dinner for the kids now so i'll leave it be for an hour haha

thanks for the help!

> I think so, because I got the image_old table :-)

Aha! But then why did creating the new image table fail?

> Yep, but I haven't solve it yet ;-)
Getting there I think. If I have solved it, I'll test it a couple of times and I'll post a working solution

What we figured out on the other issue, briefly summarized: some wacky race condition somewhere creates the duplicates. we're probably never going to figure out what it is. We just need a reliable way of either: a) doing whatever changes we need to do to that table without causing that error, or b) safely erasing the duplicates before any such operation. You'll see from that issue we tried all sorts of MySQL trickery, most of which work on test data but somehow fail in the real world :/

success !

My approach in #15 led to nowhere. It seems impossible to restore it from D5->D6 upgrade gone bad.
So back to the drawing board and here are the results :

I did have the duplicates problem (original and _original). And an old images table

FOR D5.22 + image 1.9 to D6.16 + image 6.x 1.0 beta5 ( and ubercart with imagefield update for free )

Now, solution ( images work and ubercart/imagefield images work)

did this on my testsite ( a full copy of my livesite ) :

step 1 : D5 image 1.9 -> D5 image 2.x
-----------------------------------------

*put site offline
*default theme
*run backup with backup and migrate module always (!) ( I suggest both on server and a file download to a local PC folder ) and check the sql file ! Import it first to a backup database on your server and check it ( I have about 5 DB copies of my D5 site for testing )
http://drupal.org/project/backup_migrate

(didn't disable image module)
*remove all modules in (keep a copy somewhere) sites/all/modules ( not necessary anymore )

*run these sql steps in phpMyAdmin :

/* backup image and files table - this step is not necessary anymore*/

RENAME TABLE image TO image_backup_d
CREATE TABLE files_backup_d SELECT * FROM files

/* delete records for duplicate image problem -> that was it !!! */

DELETE FROM files WHERE files.filename = 'original'

*install only image module 2.x dev in sites/all/modules ( or just delete 1.9 and replace with 2.x dev )
*update image module 1.9 to 2.x dev ( update.php )
(no sql errors ! jay)
*goto admin->settings-> image and "reset to defaults " ( should probably done that before, but I did it in this step of the process )
( you can rebuild your own types and sizes later )

*now go look for images : images are displayed ( bit slow at first, because it is rebuilding thumbnails and previews because my scale sizes were different )

step 2 D5->D6
---------------

update core D6 as you normally would
install image 6.x (latest)
update.php
enable image module ( got disabled on the upgrade )
jaaayyyy !
Now you can set your own derivatives and sizes back in image settings

I think it is necessary to install and update imagefield and filefield now ( needed for ubercart ) , if you had them before

proceed to upload other or all other modules

I don't know if all these steps are needed, but this worked for me and I didn't want to test all ways to do it
SQL's may need altering for your site/DB

Glad to hear you've got your site updated.

But I'm rather concerned that the update functions aren't doing the job!

It looks like you had #357793: Upgrading from 5.x-1.x to 6.x-1.x fails due to pre-D5 image table followed by #207557: update 5-1 to 5-2 gives user warning: Duplicate entry '611-thumbnail' for key 1 query in image_update_5200. Could you take a look at those -- I'm particularly curious why the {image} table checking code didn't work for you.

Can you tell me what the sequence is of calls to functions in a module install file ? Is it just executed like the order in the install file ?
Then I can look into it.
Sorry, not gotten around to module design yet.

AFAIK they are executed in numerical order. They are all implementations of this hook: http://api.drupal.org/api/function/hook_update_N

Hmm...

# mymodule_update_6200()

* This is the first update to get the database ready to run mymodule 6.x-2.*. Users can directly update from 5.x-2.* to 6.x-2.* and they get all 60XX and 62XX updates, but not 61XX updates, because those reside in the 6.x-1.x branch only.

This might be it. If you were on 5--1, and go to 6--1, then the update system may think that you are never on 5--2 and hence 5200 is never run. Can you debug to confirm?

It may be we have to add a check in the 6000 update for the old image table. What is its format? How can we test that an {image} table is the old 4.x one and not the 5--2 one?

I'm will dig into it, after my ubercart/imagefield problem is solved :-). Keep you posted.
I will update #20 as I find usefull info

problem solved
Now going to update my live site and then look into the install/upgrade file for image module

I ran into this issue - the image table would be created but would not be populated. It looks like image_update_5200() is the problem because it is not present in 5.x-1.9. image_update_5200() has SQL expecting the nid column to be in {files} and that is no longer the case.

The attached patch worked for me (not extensively tested); it obtains the nid by joining via the {file_revisions} table.

-    $ret[] = update_sql("INSERT IGNORE 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);
+    $ret[] = update_sql("INSERT IGNORE INTO {image} SELECT DISTINCT n.nid, f.fid, f.filename FROM {files} f LEFT JOIN file_revisions r ON f.fid=r.fid LEFT JOIN {node} n ON r.vid = n.vid WHERE n.type = 'image' AND f.filename" . $cond);

You have to upgrade via 5--2.

Yes, you need to upgrade from 5.x-1.x to 5.x-2.x, in order to upgrade to Drupal 6.