6.x upgrade error: Unknown column 'f.nid' in 'field list' query

I had the same problem. I upgraded from drupal 5.x to drupal 6.x and it removes the f.nid column from the files table before the image module update query runs. I had to restore my old version of the files table to file_old, and modify the insert query above to

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

A little bumpy, but it worked.

Confirming this in alpha2.

I experienced this issue while upgrading from Drupal 5.4 to Drupal 6.3 and jvandervort's workaround worked.

In case somebody needs, my steps to follow it were:
1) launch in command prompt: mysqldump -u USERNAME -pPASSWORD OLD_DATABASE files > files.sql
2) in file files.sql replace in some editor string `files` with string `files_old` (with quotes)
3) launch in command prompt: mysql -u USERNAME -pPASSWORD NEW_DATABASE < files.sql

The table file_revisions seems to have been deleted before image module update, so no workarounds are needed.

I *love* you two, you just saved my wedding website! ;) Also note that after doing artem_sokolov's step (#2) and then jvandervort's step (#1), you will want to re-run the update.php script. My thumbnails did not come back after running solely #2 and #1, but after I ran update.php, the world was good. :)

This happened after upgrading Drupal 5.9 with image-5.x-1.9 to Drupal 6.3 with image-6.x-1.0-alpha2.

I was unfortunate enough to have no backup available, but I solved my misery this way:
INSERT INTO image (nid, fid, image_size) SELECT u.nid, f.fid, f.filename FROM files f LEFT JOIN upload u ON f.fid=u.fid
If you have trouble with the primary key, just temporarily remove it. Note that you'd need to refine this query if you've also uploaded files and not just images, because this query takes its information from the upload table.

A full corrected statement with drupal table name placholders taking care of the filenames and image node type too is:

insert into {image} SELECT distinct u.nid, u.fid, f.filename from {files} f, {upload} u, {node} n where f.fid=u.fid and u.nid=n.nid and n.type='image' and f.filename in ('_original', 'thumbnail', 'preview')

I just had a look in CVS head's image.install. The update containing the problematic SQL code is contained in function image_update_5200(). This is a function that would work in drupal 5 and has been introduced for the not yet finalized 5.x-2.x release. Updating from 5.x-2.x to 6.x-1.x should work. Updating from 5.x-1.x to 6.x-1.x does not work. I don't know if it is possible to get both update paths working.

I created the attached patch to fix this issue. I upgraded successfully from a drupal 5.10 installation using image 5.x-1.9 to drupal 6.4 using image from CVS head. The patch applies cleanly against image.install in CVS.

I'm not sure about the whole VERSION constant. Where does that come from?

The VERSION constant is defined in modules/system/system.module see http://api.drupal.org/api/constant/VERSION/6 and http://api.drupal.org/api/constant/VERSION/5. The constant is available in D4.7 till D7.0-dev and seems a reliable indicator for the running Drupal version.

Please remove the conditional VERSION code. Updates in a 6.x module are for 6.x, and updates in a 5.x module are for 5.x.

I'm a bit unsure about the WHERE EXISTS statement... is that known to work in all and each and every edge-case?

The patch tries to address the case that users of the 5.x-1.x series face. The current 6.x-1.x version expects that 5.x-2.x is installed and has the mentioned problems when updating from 5.x-1.x to 6.x-1.x. The VERSION condition applies if a user updates from 5.x-1.x. If he updates from 5.x-1.x to 5.x-2.x the first (original) part is executed. If he updates from 5.x-1.x to 6.x-1.x the alternative part matching Drupal 6's core database schema is used. If there is no conditional code, all 5.x-1.x users have to update to 5.x-2.x first before switching to Drupal 6 and image 6.x-1.x. Many users will still have installed 5.x-1.x, because there is no final image 5.x-2.x yet.

If you have any idea how to cleanly solve this issue without the VERSION condition I'd be happy to update the patch.

The WHERE EXISTS clause is standard SQL and works with MySQL 5.0 and PostgreSQL 8.1 on my test system.

great i just wasn't sure where that was defined. i'm super short on time but if i can get a few people to test this i'd love to get it committed. in the mean time i've updated the 6.x release notes to tell the users to upgrade to 5.x.-2.x before going to 6.x.

great
The patch is working perfectly.

Thanks guys for the wonderful job.

You should run the update.php file to complete the process. In my scenario I have to run the update.php file.

My patch works fine for users upgrading from Image 5.x-1.x to 6.x-1.x but as I understand it will not get blessed by the module's maintainer because of the distinction between an upgrade from 5.x-1.x to 6.x-1.x and 5.x-2.x to 6.x-1.x (or an older 6.x-1.x to a newer 6.x-1.x). I don't know what to do in this case. Is there any way to tell users: If you want to upgrade from 5.x-1.x to 6.x-1.x (without upgrading to 5.x-2.x-dev first), you need this patch but it will not be included in the normal 6.x code because of ...? I think that many users will upgrade from the stable (read 5.x-1.x) series of the Image module to the 6.x-1.x version when they upgrade their Drupal site to Drupal 6 and I'm wondering how we could properly guide these users.

Any thoughts from Image module's maintainer(s)?

Trying to get this back into my tracker.

Please test attached patch.

Committed.

Just found this...
Is this issue the reason for the upgrade warning on the project page?
If so, now it's been committed, can we remove that warning, or change it?

"Hopefully, yes." ;) We would need to test the upgrade path from D5 with 5.x-1.x to 6.x-1.x to be sure.

Hi,

I made the upgrade from 5.x-1.x to 6.x-1.x yesterday and encountered this very problem. My database backup did not seem to want to work for me, so I tried stdbrouw's fix (post #4 above) to get my database back:

INSERT INTO image (nid, fid, image_size) SELECT u.nid, f.fid, f.filename FROM files f LEFT JOIN upload u ON f.fid=u.fid

That worked except for the last five image uploads. I can see the associated files in the images folder with my ftp client, but they do not show on the screen in Drupal. Attempts to rebuild derivatives are unsuccessful, asking for an image to upload.

Also I cannot upload any new images -- the box for the thumbnail is drawn (with no image) and then quickly disappears. The alt-text for the thumbnail is displayed and clickable. There is no large image displayed. I have triple-checked the database file_directory_path and image_default_path and verified their settings (Administer>File System>File System Path and Images>Image File Settings>Default Image Path) as well as clearing the cache. No Luck.

I have a feeling that my database is still not right. The 'image' structure looks like this:

Field Type Null Default
nid int(10) No 0
fid int(10) No 0
image_size varchar(32) No

Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 607 nid
image_size
fid INDEX 607 fid

As you can probably tell, my mySQL & programming knowledge is limited. Any ideas are appreciated.

Nick

Hi,

I'm having a similar issue. However, the SQL statement from #5 gives me an error:

mysql> insert into {image} SELECT distinct u.nid, u.fid, f.filename from {files} f, {upload} u, {node} n where f.fid=u.fid and u.nid=n.nid and n.type='image' and f.filename in ('_original', 'thumbnail', 'preview');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{image} SELECT distinct u.nid, u.fid, f.filename from {files} f, {upload} u, {no' at line 1

The S&R operation mentioned in #2 can be made easily on the shell:

$ sed 's/`files`/`files_old`/g' files.sql > files_fixed.sql

However, I'm still having problems to display any 'image.module' images after and D5->D6 upgrade from 'image.module' 5.x-2.0-alpha4 to 6.x-1.0-beta4; how is the status of this issue, should it be fixed in recent releases of the 'image.module'?

Greetings, -asb

Don't use the {} in SQL you feed directly to the server; the {} are for Drupal's DB API only.

@asb: is your problem the one from #207557: update 5-1 to 5-2 gives user warning: Duplicate entry '611-thumbnail' for key 1 query in image_update_5200 or #583076: Error on Database Updates for 6100 when upgrading to Beta3: 'duplicate entry' for primary key?

@joachim:

> is your problem the one from #207557 [...] or #583076 [...]

No, I don't think so. Since I got trouble with an upgrade that was troubleless (at least regarding image.module) before, I posted a detailed description at #672638: D5->D6 upgrade: file paths not updated. I believe to have solved this issue, but I'm not sure if I followed the best path.

Greetings, -asb

For anyone else with this problem, after a particularly messy upgrade I was able to salvage my image table using a modified version of the query from comment #1 above:

INSERT IGNORE INTO image SELECT DISTINCT f.nid, f.fid, f.filename FROM files_old f INNER JOIN node n ON f.nid = n.nid WHERE n.type='image' AND f.filename IN ('_original', 'thumbnail', 'preview') AND f.fid NOT IN (SELECT fid FROM image ) ;