Topics

1

Discussion & Troubleshooting / query problems with MySQL 5.0.x

« on: October 25, 2005, 01:50:56 PM »

Hi there,

I'm using 4images in version 1.7.1.

I recently updated my mysql server to version 5 (5.0.13-rc). After that I received heaps of SQL-Errors like this:

DB Error: Bad SQL Query:
SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name, u.user_name
FROM 4images_images i, 4images_categories c
LEFT JOIN 4images_users u ON (u.user_id = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN (0, -1) AND c.cat_id = i.cat_id LIMIT 290, 1
Unknown column 'i.user_id' in 'on clause'

The error messages are all looking the same: "Unknown column 'XY' in 'on clause'"
As a result 4images doesn't show any images 

So i had a search through the mysql bug database and discovered the following:
http://bugs.mysql.com/bug.php?id=12943

To summarize it, among other things they say that the join-SQL syntax used by 4images is not SQL:2003 compliant. Since version 5 mysql claims to be fully SQL:2003 compliant and thus complains about those queries. As it seems the mysql guys are working on some fixes to make mysql 5 work with non SQL:2003 compliant queries.

If you take a look at the example quoted above you can see the problem: the 2 tables (u and i) mentioned in the ON statement of JOIN need to be operands of the JOIN statement. But currently the operands are c and u. 

the correct syntax needs to be something like this:

SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_description, i.image_keywords, i.image_date, i.image_active, i.image_media_file, i.image_thumb_file, i.image_download_url, i.image_allow_comments, i.image_comments, i.image_downloads, i.image_votes, i.image_rating, i.image_hits, c.cat_name, u.user_name
FROM 4images_categories c, 4images_images i
LEFT JOIN 4images_users u ON (u.user_id = i.user_id)
WHERE i.image_active = 1 AND i.cat_id NOT IN (0, -1) AND c.cat_id = i.cat_id LIMIT 290, 1

this problem exists in a lot of 4images files

I took the liberty to rewrite some queries in order to make them SQL:2003 compliant.
I put my altered files here:
http://www.milchsemmel.de/~punzeroni/4images-SQL2003fix.tar.bz2

Cheers
punzeroni