Image_attach.install v 1.1.4.4 2007/03/28 22:30:53 contains in update 20 an EXISTS clause which fails in MySQL 4.0:

DELETE FROM {image_attach} WHERE NOT EXISTS (SELECT * FROM {node} n WHERE n.nid = {image_attach}.iid AND n.type = 'image')

CommentFileSizeAuthor
#10 image_attach.install_132879_0.patch885 bytesdrewish
#3 image_attach.install_132879.patch847 bytesdrewish

Comments

cog.rusty’s picture

cog.rusty commented
Title: Image_attach update 20 fails in MySQL 4.0 » Image_attach update 2 fails in MySQL 4.0

I meant update 2 of course.

drewish’s picture

drewish commented
Assigned: Unassigned » drewish
Priority: Critical » Normal

it's really not critical because all the query was designed to do was remove old unreferenced records. i'll dig up the mysql 4 docs and try to figure out an equivalent query that is compatible.

drewish’s picture

drewish commented
Status: Active » Needs work
StatusFileSize
new image_attach.install_132879.patch847 bytes

Okay so the following should do it:

DELETE FROM ia USING {image_attach} ia LEFT JOIN {node} n ON ia.iid = n.nid WHERE n.nid IS NULL OR n.type != 'image'

Please try running that or by applying the attached patch and manually selecting image attach's update #2.

cog.rusty’s picture

cog.rusty commented

This time I got a user warning: Not unique table/alias: 'ia' query: DELETE FROM ia USING image_attach ia LEFT JOIN node n ON ia.iid = n.nid WHERE n.nid IS NULL OR n.type != 'image'

After trying a couple of things, what worked was

DELETE FROM {image_attach} USING {image_attach} ia LEFT JOIN {node} n ON ia.iid = n.nid WHERE n.nid IS NULL OR n.type != 'image'

drewish’s picture

drewish commented
Status: Needs work » Fixed

cool, i've committed that. thanks for your help.

mathieu’s picture

mathieu commented
Status: Fixed » Active

I just tried it and I get the following error : "#1109 - Unknown table 'image_attach' in MULTI DELETE". That's using mysql 4.1.

cog.rusty’s picture

cog.rusty commented

Crap... look at this Note:

http://dev.mysql.com/doc/refman/4.1/en/delete.html

Note: The syntax for multiple-table DELETE statements that use table aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the true table name to refer to any table from which rows should be deleted:

DELETE test FROM test AS t1, test2 WHERE ...

In MySQL 4.1, you must use the alias:

DELETE t1 FROM test AS t1, test2 WHERE ...

We did not make this change in 4.0 to avoid breaking any old 4.0 applications that were using the old syntax. However, if you use such DELETE statements and are using replication, the change in syntax means that a 4.0 master cannot replicate to 4.1 (or higher) slaves.

Cross-database deletes are supported for multiple-table deletes, but in this case, you must refer to the tables without using aliases. For example:

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...

Currently, you cannot delete from a table and select from the same table in a subquery.

This is getting ugly...

cog.rusty’s picture

cog.rusty commented

I tried completely removing the ia alias on MySQL 4.0 and forced update 2 and it worked ok.

DELETE FROM {image_attach} USING {image_attach} LEFT JOIN {node} n ON {image_attach}.iid = n.nid WHERE n.nid IS NULL OR n.type != 'image'

Can you try it on MySQL 4.1?

cog.rusty’s picture

cog.rusty commented

I also tested on a local MySQL 5. It seems to work.

drewish’s picture

drewish commented
Status: Active » Needs review
StatusFileSize
new image_attach.install_132879_0.patch885 bytes

can anyone else test out this patch?

drewish’s picture

drewish commented
Status: Needs review » Fixed

committed to HEAD and 5

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)