image_update_5200 fails on MySQL < 4.1

delete FROM file_revisions WHERE EXISTS (SELECT * FROM image WHERE image.fid = file_revisions.fid)

this query fails on my setup with message:

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 'EXISTS (SELECT * FROM drupal_image WHERE drupal_image.fid = dru query: DELETE FROM drupal_file_revisions WHERE EXISTS (SELECT * FROM drupal_image WHERE drupal_image.fid = drupal_file_revisions.fid) in ....

MySQL database 4.0.20
PHP 4.3.8

it's only mixed up in the message, which does some trimming and escaping I guess.

The original code is in image.install:

 // Remove old file_revision records.
  $ret[] = update_sql("DELETE FROM {file_revisions} WHERE EXISTS (SELECT * FROM {image} WHERE {image}.fid = {file_revisions}.fid)");

The problem in #1 happened during update.php.

But if I execute the query manually in phpmyadmin (with the proper table prefix for my setup):

DELETE FROM drupal_file_revisions WHERE EXISTS (SELECT * FROM drupal_image WHERE drupal_image.fid = drupal_file_revisions.fid)

I get error

MySQL said:
#1064 - 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 'EXISTS (SELECT * FROM drupal_image WHERE drupal_image.fid = dru

(with MySQL 4.0.20, phpMyAdmin 2.6.0)

I've got a similar issue. When I upgrade image module to the last version, I got this error:

The following queries were executed
image module
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 */;
    * 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)

Update #5201

    * No queries

user warning: 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 'EXISTS (SELECT * FROM image WHERE image.fid = file_revisions.fi query: DELETE FROM file_revisions WHERE EXISTS (SELECT * FROM image WHERE image.fid = file_revisions.fid) in /web/htdocs/www.example.com/home/includes/database.mysql.inc on line 172.

I use Mysql 4.0.27. Any help?

Thanks, Aress

mysql> DESCRIBE drupal_image;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| nid        | int(10) unsigned |      | PRI | 0       |       |
| fid        | int(10) unsigned |      | MUL | 0       |       |
| image_size | varchar(32)      |      | PRI |         |       |
+------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> DESCRIBE drupal_file_revisions;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| fid         | int(10) unsigned    |      | PRI | 0       |       |
| vid         | int(10) unsigned    |      | PRI | 0       |       |
| description | varchar(255)        |      |     |         |       |
| list        | tinyint(1) unsigned |      |     | 0       |       |
+-------------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

SELECT * FROM drupal_image;
works
SELECT fid FROM drupal_file_revisions;
works

mysql> SELECT fid FROM drupal_file_revisions WHERE EXISTS (SELECT * FROM drupal_image);
ERROR 1064: 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 'EXISTS (SELECT * FROM drupal_image)' at line 1
mysql> SELECT fid FROM drupal_file_revisions WHERE EXISTS (SELECT * FROM drupal_image WHERE drupal_image.fid = drupal_file_revisions.fid);
ERROR 1064: 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 'EXISTS (SELECT * FROM drupal_image WHERE drupal_image.fid = dru
mysql> SELECT * FROM drupal_file_revisions WHERE EXISTS (SELECT 1);
ERROR 1064: 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 'EXISTS (SELECT 1)' at line 1

But trying seems futile since I found the following comment on http://dev.mysql.com/doc/refman/4.1/en/exists-and-not-exists-subqueries.... :

EXISTS is supported from 4.1 on. Earlier versions of MySQL can try rewriting the query using a LEFT JOIN.

As mentioned earlier, I'm on MySQL 4.0.20 :-(

Since Drupal 5.x only requires MySQL 3.23.17 or higher (http://drupal.org/requirements), there needs to be something in the image.module documentation that it requires MySQL 4.1 or higher.

I've just been bitten by the same problem as soxofaan when upgrading from Drupal 4.7.x to Drupal 5.x.

A quick look through the code shows that the query on line 114 of image.install (5.x-1.6 release) is the only one that uses subqueries. Perhaps that query can just be rewritten?

> A closer look shows that there is no column "filename" in table image, but columns filesize is unassigned.

'filename' is correct; it is the derivative image size. Note that the 'image_size' field is also a string, and is more descriptive. It is only 32 chars in the image table, though; which filename is 255 in the files table, which could possibly result in some conflicts.

I've just encountered this issue (mysql 4.0.27). I managed (I think!) to get around it by using the following query:

delete from file_revisions using file_revisions, image where image.fid = file_revisions.fid

Unfortunately the syntax is specific to mysql so it probably won't work on other databases. :(

Why is the image module ham-handedly thrashing the file_revisions table like this, anyway? Other file modules may have put entries there that this SQL statement will delete.

Chris Johnson, it's purpose was to clean up some of the mess made in the files table made by earlier versions of the image module. it makes a reasonable effort to avoid other module's files.

Here's a few fixes for those who wish to run update on image, but use MySQL < 4.1:

MySQL version < 4.1:

If you run a version earlier than 4.1, the SQL command used in image's
update #4 and #5200 ("EXISTS") won't work, and you'll get an SQL error
when you run update.php.

I) image update #4 throws an SQL error.

Removing {files} with duplicate filenames:

Instead of this:

SELECT f1.fid, f1.nid, f1.filepath
FROM files f1
INNER JOIN node n
ON f1.nid = n.nid
WHERE n.type = 'image'
AND EXISTS ( SELECT *
FROM files f2
WHERE f2.filepath = f1.filepath
AND f1.fid <> f2.fid
AND f1.fid < f2.fid )

(and DELETE FROM files WHERE fid = ...)

Run this: (UNTESTED!)

DELETE
FROM `files` f1
LEFT JOIN `files` f2
ON f1.fid = f2.fid
INNER JOIN `node`
ON f1.nid = node.nid
WHERE f2.filepath = f1.filepath
AND f1.fid <> f2.fid
AND f1.fid <> f2.fid

Cleaning up the file_revisions table isn't critical, but if you wish to
do it by hand you can do so.

Instead of this:

DELETE FROM file_revisions WHERE NOT EXISTS (SELECT * FROM files WHERE files.fid = file_revisions.fid)

You can run this: (UNTESTED!)

DELETE
FROM `file_revisions`
LEFT JOIN `files`
ON file_revisions.fid = files.fid
WHERE files.fid IS NULL;

II) image update #5200 throws an SQL error.

Cleaning up the file_revisions table isn't critical, but if you wish to
do it by hand you can do so.

Instead of this:

DELETE FROM file_revisions
WHERE EXISTS (SELECT *
FROM image
WHERE image.fid = file_revisions.fid)

Run this (on MySQL): (UNTESTED!)

DELETE FROM file_revisions
USING file_revisions, image
WHERE image.fid = file_revisions.fid;

If somebody on mysql < 4.1 would give these a shot, and confirm they work, I'll create a proper patch for image.install.

(the title field didn't take "<")

Marking as duplicate of #207557: update 5-1 to 5-2 gives user warning: Duplicate entry '611-thumbnail' for key 1 query in image_update_5200

Ooops. One too much. ;) Reverting status.

Can someone please test/confirm that attached patch works on MySQL 4.0?

I had this issue too, and in my case it is absolutely certain that duplicates are the problem. I was able to solve the problem by executing the manual query:
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') GROUP BY nid,filename

Since I use "GROUP BY" I get no duplicated index. I then ran the "DELETE FROM {file_revisions}..." manually too, and it removed as many lines as had been added to {image} by the previous query (ca 15.000 - I'm working for a newspaper). What's left is a measly 70 entries.

Edit: Oh, and this is using MySQL 5.0.51a (does this mean I'm posting to the wrong bug?)

When trying to update to 5.x-2.0-alpha3, MySQL version 5, get this error (below) and all my images disappear from the site.

user warning: Duplicate entry '181-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', 'thumbnail', 'preview') in /home/content/c/h/a/charitableu/html/includes/database.mysql.inc on line 174.

Subscribing. I had the same problem, mentioned also here: http://drupal.org/node/207557

After upgrading from 5.x-1.9 to 5.x-2.0-alpha3, I got this error message:

user warning: You have an error in your SQL syntax near 'EXISTS (SELECT * FROM image WHERE image.fid = file_revisions.fid)' at line 1 query: DELETE FROM file_revisions WHERE EXISTS (SELECT * FROM image WHERE image.fid = file_revisions.fid) in F:\Program Files\Apache2\htdocs\includes\database.mysql.inc on line 174.

This was output as the report for the update:

The following queries were executed
image module
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 */;
* 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)

Marking as duplicate of #207557: update 5-1 to 5-2 gives user warning: Duplicate entry '611-thumbnail' for key 1 query in image_update_5200, which has a fix.