Update from 1.6 to 2.x-dev deletes images or error messages
| Project: | Image |
| Version: | 5.x-2.x-dev |
| Component: | image.module |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | duplicate |
Jump to:
I tried to update my image module as per the status update report from 1.6 to 2.x-dev as was recommended. I received an error message about a duplicate entry and deleted the 2.x module and went back to 1.6. All of the images were restored. I then attempted to upgrade some other modules, this time on my test site, however, when running update.php, this time I received a message that many of my images were deleted and none appeared to be rebuilt. None of the images showed up.
This was despite using the 1.6 image module.
At this point, I can not run update.php without my image module being affected, too, even though I do not want to upgrade it. I can not update my drupal 5.5 upto 5.6, I cannot update any of my other modules without losing about 50 images in the process.
I do have older backups of my files folder. I also have older backups of my database. I would like to go to a state prior to my aborted attempt of updating 1.6 to 2.0.
I have several questions, which I hope someone here will address.
1. Which tables in the database are impacted by the upgrade. I can delete those tables and revert to an older backed up table if that is the best option
2. I can restore the files folder as well (at least most of them)
3. Is there any other choice that is better?
by the way, this is the error message i am getting when I let the update go through to 2.0 dev
Unknown column 'image.nid' in 'field list' query: SELECT node.nid, image.nid AS image_nid FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_hierarchy term_hierarchy ON term_node.tid = term_hierarchy.tid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_hierarchy term_hierarchy2 ON term_node2.tid = term_hierarchy2.tid WHERE (term_node.tid = '9') AND (node.status = '1') AND (term_node2.tid = '9') LIMIT 0, 50 in /home/xxxxx/public_html/xxxxxx/drupal/includes/database.mysql.inc on line 172.
I even deleted node 39 without fixing the problem. Node 2 doesn't have any images
My server has php 5.2.5
#1
I also have experience the same problem. I attempted to revert back to 1.6 and that failed once update.php was ran. I've attempted to upgrade again to the dev version and this is what I got on when running update.php
# user warning: Table 'image' already exists query: 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 */; in /web/www/includes/database.mysqli.inc on line 151.
# user warning: Duplicate entry '1-_original' 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', 'preview', 'small', 'medium', 'large') in /web/www/includes/database.mysqli.inc on line 151.
AND:
The following queries were executed
image module
Update #5200
* 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', 'original', 'thumbnail', 'preview', 'small', 'medium', 'large')
* DELETE FROM {file_revisions} WHERE EXISTS (SELECT * FROM {image} WHERE {image}.fid = {file_revisions}.fid)
Update #5201
* No queries
Does anyone have any suggestions for fixing this problem?
Thanks in advance,
Ken Colwell
#2
I was able to get image galleries working again by reverting to 1.6 and using a backup of my files table.
Good luck if you don't have a database backup.
Ken Colwell
#3
you need to delete the {image} table. it looks like there was an old, pre-existing one.
#4
Drewish wrote: "you need to delete the {image} table. it looks like there was an old, pre-existing one."
I think that is indeed the problem, but that is a bug in the module that needs to be fixed. It's clear from reading the code in image.install that some Drupal sites have old {image} tables that need to be deleted, so the image_update_5200 function in image.install needs to take that into account and fix it.
The fact that an {image} table may exist in older Drupal sites is evident from reading the function image_update_1, which is supposed to "Update 4.5 to 4.6 or later." That function begins with the following statement:
if (db_table_exists('image')) {This seems to clearly indicate that that a table named {image} may already exist. However, there is no code, either in image_update_1 or in later update functions, to drop that table.
I don't understand the function of the old {image} table. If it is a table that became obsolete with the transition from Drupal 4.5 to later versions, I assume it would be okay to simply drop it. However, the maintainers of this module should know whether this is the case better than I do.
In any case, there is an obvious problem with the image_update_5200 function in image.install. It contains a SQL command to create table {image}. Obviously that SQL command will fail if a table by that name already exists. Moreover, since the new image table created by image_update_5200 has different fields than the old image table, the failure to create a new table will cause sites with the old image table to kick out error messages if they upgrade to the current version of the module.
Fixing this may be as simple as adding the following code at the top of the image_update_5200 function:
if (db_table_exists('image')) {db_query('DROP TABLE {image}');
}
However, since I'm not 100% sure that it's safe to drop the old image table, I hope the maintainers of this module will make a careful assessment before adding this fix.
#5
For me, a similar issue.
Upgrading to 2.x-dev - and now IMG tags displayed.
Went back to 1.6 - now IMG tags are outputted perfectly.
Subscribing.
#6
Same problem here.
Error for me came on second statement (INSERT INTO):
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 */;
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')
DELETE FROM {file_revisions} WHERE EXISTS (SELECT * FROM {image} WHERE {image}.fid = {file_revisions}.fid)
#7
Carlos: you don't say what your error was.
#8
Same "Update from 1.6 to 2.x-dev deletes images or error messages" problem.
Fixed "by reverting to 1.6 and using a backup of my files table", cf. comment #2.
Yes, ggod luck. The image.module is such an amazing maze sometimes...
#9
I can confirm it, update deleted all images in my files/images folder - about thousands images... terrible!
It write error message aout that image magick can resize some image.
Igorik
http://www.somvprahe.sk
#10
Sorry, my comment was for update from 1.6 to 1.8, not for 2.x
Igorik
#11
Two problems:
1) image table already exists. Rename that table to something else (in case there's something important in there) and rerun update 5200.
2) a change in file paths. This fix might help:
http://drupal.org/node/184489
#12
This fixed it for me:
insert into image select distinct n.nid,u.fid,f.filename from node n, upload u, files f WHERE n.type='image' and n.nid=u.nid and f.fid=u.fid AND f.filename IN ('_original', 'thumbnail', 'preview', 'small');
The filenames would be different depending on the configured Image sizes available.
#13
Hmmm. When I ran that I got a mysql error:
mysql> insert into image select distinct n.nid,u.fid,f.filename from node n, upload u, files f WHERE n.type='image' and n.nid=u.nid and f.fid=u.fid AND f.filename IN ('_original', 'thumbnail', 'preview', 'small');ERROR 1062 (23000): Duplicate entry '24-_original' for key 1
I think I remember seeing that when I ran the upgrade from D5.7 to D6.2 too. Anyway, it appears to be trying to create a primary key using the nid and filename of a translated node (node 24).
mysql> select distinct n.nid,u.fid,f.filename from node n, upload u, files f WHERE n.type='image' and n.nid=u.nid and f.fid=u.fid AND f.filename IN ('_original', 'thumbnail', 'preview', 'small') AND n.nid = '24' order by u.fid;
+-----+-----+-----------+
| nid | fid | filename |
+-----+-----+-----------+
| 24 | 105 | _original |
| 24 | 527 | thumbnail |
| 24 | 528 | preview |
| 24 | 529 | small |
| 24 | 704 | _original |
| 24 | 705 | thumbnail |
| 24 | 706 | preview |
| 24 | 707 | small |
+-----+-----+-----------+
8 rows in set (0.00 sec)
mysql> desc image;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| nid | int(10) unsigned | NO | PRI | | |
| fid | int(10) unsigned | NO | MUL | | |
| image_size | varchar(32) | NO | PRI | | |
+------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Digging a little deeper, node 24 is a multi-lingual content node with a translation at node 87.
mysql> select distinct n.nid,u.fid,f.filename from node n, upload u, files f WHERE n.type='image' and n.nid=u.nid and f.fid=u.fid AND f.filename IN ('_original', 'thumbnail', 'preview', 'small') AND n.nid = '87' order by u.fid;+-----+-----+-----------+
| nid | fid | filename |
+-----+-----+-----------+
| 87 | 105 | _original |
| 87 | 527 | thumbnail |
| 87 | 528 | preview |
| 87 | 529 | small |
| 87 | 704 | _original |
| 87 | 705 | thumbnail |
| 87 | 706 | preview |
| 87 | 707 | small |
+-----+-----+-----------+
8 rows in set (0.00 sec)
So, nodes 24 and 87 are 'Image' nodes which relate to the same files. When I go into
/en/admin/content/types/imageunder Workflow -> Multilingual options -> Synchronize translations, we have 'File attachments' checked.GOTCHA!
I suspect that may have come from uploading the same image twice on different nodes then relating them through the i18n module.
#14
Just confirming that after deleting the duplicate rows from the {upload} table and re-inserting the keys into the {image} table, all images are working as normal again.
#15
Using phpmyadmin, I renamed the 'image' table to 'image_old' then reran update 5200 and all my images showed up again.
#16
marked http://drupal.org/node/304722 as duplicate
#17
#12 seemed to fix along with clearing the cache
Drupal 5.12 to 6.6 upgrade with Image 6.x-1.0-alpha3
#18
#12 seemed to fix it for me also - at least at first look things seem ok
Drupal 5.10 to 6.6 upgrade with Image 6.x-1.0-alpha3
#19
I also upgraded my 5.11 to 6.6 with Image 6.x-1.0-alpha3.
Now images show up in nodes (image, image attachment, image galery)
I had to recreate image table manually because patch failed.
I had to restore term_node table from backup to make show images in image_galery. (term_node table was empty after upgrade)
Finally I could upgrade to drupal 6. Thank you guys for your work.
#20
Marking as duplicate of #357793: Upgrading from 5.x-1.x to 6.x-1.x fails due to pre-D5 image table