Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - punzeroni

Pages: [1]
1
Discussion & Troubleshooting / Re: query problems with MySQL 5.0.x
« on: December 10, 2005, 04:23:38 PM »
Hi Murphy,

great that it finally works for you.
I had a look at your reply #16 but I was pretty sure that those errors should be fixed in my version. So... it must have been some caching.
Just for my interest: What server did you reinstall?

Cheers
punzeroni

2
Discussion & Troubleshooting / Re: query problems with MySQL 5.0.x
« on: December 10, 2005, 03:55:57 PM »
G'Day!

@Vano: I can definetly deny that 5.0.16 doesn't have those probs. I am using 5.0.16 at the moment and I can reproduce the errors with JOINS. Sorry mate :-)

@Murphy: could you please post the error message. I cannot find any other JOIN problem within my code so probably it is something else. Thx

See you later

3
Discussion & Troubleshooting / Re: query problems with MySQL 5.0.x
« on: December 05, 2005, 04:04:46 PM »

I am not sure that the corrections you suggested in the second part of your posting will help since they are not about any JOINs.
that's what I was not sure and couldnt check myself. So, if you can confirm that it only problem when JOIN tables used, then I can remove it from my post.

Ok, I had a quick look in the SQL2003 syntax definitions and in the mysql 5 manual and I am pretty sure the brackets aren't necessary in FROM parts of a query where you don't have a JOIN. It wouldn't make sence anyway.

As a reference see: http://dev.mysql.com/doc/refman/5.0/en/select.html

Cheers punzeroni

4
Discussion & Troubleshooting / Re: query problems with MySQL 5.0.x
« on: December 05, 2005, 11:01:08 AM »
Hi guys!

After some weeks i rejoin this discussion...

first of all something general about this problem:
@vano: I still think that the problems only exist on JOIN statements where the left operand of the statement isn't the table you use in the join statement. (that's what i explained in my very first posting) .
There are at least two ways of solving this: either you put the statements in the correct order or you use brackets as you suggested. I would recommend using the correct order rather than using brackets since the second table isn't used and so it is more optimized.
I am not sure that the corrections you suggested in the second part of your posting will help since they are not about any JOINs.

@Murphy and the rest: I put all the fixes that are necessary from my point of view in an archive. Just download it, unpack it and overwrite your original 4 image files with it (or use a merge program :-) ). You can download the file here: http://www.milchsemmel.de/~punzeroni/4images-SQL2003fix.tar.bz2
Please tell me if it works or if you remain having problems.

Cheers
punzeroni

5
Discussion & Troubleshooting / Re: query problems with MySQL 5.0.x
« on: October 29, 2005, 04:23:30 PM »
Hi guys,

sorry I was gone for a while... Did a kernel update which didn't work as it was supposed to :-)

@darkman: I can't find any additional problems in my files. So maybe send me your whole 4images distribution and I'll have a look at it (ok... you might leave the passwords out :-) )

@V@no: I gave Sergei's brackets a try for some examples and it works for me. So which version you finally choose seems to be an aestetical question.

Ciao
punzeroni

6
Discussion & Troubleshooting / Re: query problems with MySQL 5.0.x
« on: October 28, 2005, 03:35:23 PM »
@darkman

I'm not sure what your problem is. I think I fixed all the files I could find. Random pictures work for me. Could you please post your error message and - if possible - all of the 4images dist files. Then I'll have a look.

7
Discussion & Troubleshooting / Re: query problems with MySQL 5.0.x
« on: October 28, 2005, 01:13:37 PM »
Hi darkman,

did you also overwrite the file functions.php in the include subdir? I reckon the random-function is in there.

Cheers
punzeroni

8
Discussion & Troubleshooting / Re: query problems with MySQL 5.0.x
« on: October 25, 2005, 03:47:47 PM »
Quote
what about the lattest version 5.0.15?  can you see if it works there?

5.0.15 came into gentoo portage this night and it just finished compiling 10 minutes ago but the problems remain. if you turn around the JOIN operands - as explained in my example - it works.

See you
punzeroni

9
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:
Quote
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  8O

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:
Quote
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

Pages: [1]