Author Topic: query problems with MySQL 5.0.x  (Read 237402 times)

0 Members and 1 Guest are viewing this topic.

Offline punzeroni

  • Pre-Newbie
  • Posts: 9
    • View Profile
    • milchsemmel.de
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

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Global Moderator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: query problems with MySQL 5.0.x
« Reply #1 on: October 25, 2005, 02:49:32 PM »
* V@no didn't know v5.x was already released!

Since you are so up-to-date, what about the lattest version 5.0.15?  can you see if it works there?
Your first three "must do" before you ask a question:
Please do not PM me asking for help unless you've been specifically asked to do so. Such PMs will be deleted without answer. (forum rule #6)
Extension for Firefox/Thunderbird: Master Password+    Back/Forward History Tweaks (restartless)    Cookies Manager+    Fit Images (restartless for Thunderbird)

Offline punzeroni

  • Pre-Newbie
  • Posts: 9
    • View Profile
    • milchsemmel.de
Re: query problems with MySQL 5.0.x
« Reply #2 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

Offline darkman83

  • Pre-Newbie
  • Posts: 5
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #3 on: October 28, 2005, 01:02:21 AM »
okay guys...before i use this fiy i get 2 of this errors...and after i use this i get only one, i think the only thing wich need to changed is the random image displaying...mhm...i dunno what to change, but i read the topic..my i be noobish? have also tried to disable the random image displying...but i dunno how lol
I use MySQL 5.0.15 xD

So any fixes there?

Offline punzeroni

  • Pre-Newbie
  • Posts: 9
    • View Profile
    • milchsemmel.de
Re: query problems with MySQL 5.0.x
« Reply #4 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

Offline darkman83

  • Pre-Newbie
  • Posts: 5
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #5 on: October 28, 2005, 03:28:20 PM »
yes, i've overwritten all files...also functions.php...i think you have forgotten to fix this ^^

Offline punzeroni

  • Pre-Newbie
  • Posts: 9
    • View Profile
    • milchsemmel.de
Re: query problems with MySQL 5.0.x
« Reply #6 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.

Offline darkman83

  • Pre-Newbie
  • Posts: 5
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #7 on: October 28, 2005, 03:48:03 PM »
http://dm.hopto.org/4images/

as i said...before i get 2 of those lines...and after replacing the files only 1!!!

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Global Moderator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: query problems with MySQL 5.0.x
« Reply #8 on: October 28, 2005, 11:43:18 PM »
Do you think its a bug in mysql itself? well, I do, because for me it doesnt make sence why would it metter the sequence of the tables in the query...

[EDIT]
I digged out something, its NOT a bug:
http://bugs.mysql.com/bug.php?id=13551

Quote from: Sergei Golubchi
This is a change that was made in 5.0.15 to make MySQL more compliant with the
standard.
According to the SQL:2003

<from clause> ::= FROM <table reference list>
<table reference list> ::=
    <table reference> [ { <comma> <table reference> }... ]
<table reference> ::=
    <table factor>
  | <joined table>
<joined table> ::=
    <cross join>
  | <qualified join>
  | <natural join>
...

Thus when you write

  ... FROM t1 , t2 LEFT JOIN t3 ON (expr)

it is parsed as

(1)    ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))

and not as

(2)    ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)

so, from expr you can only refer to columns of t2 and t3 - operands of the
join.
Workaround - to put parentheses explicitly as in (2). Then you can refer to t1
columns from expr.

Unfortunately, this change is not properly documented in the manual, it will be
fixed.

So, try instead of switching the tables around replace
Code: [Select]
FROM 4images_images i, 4images_categories c with:
Code: [Select]
FROM (4images_images i, 4images_categories c) see if it helps
Your first three "must do" before you ask a question:
Please do not PM me asking for help unless you've been specifically asked to do so. Such PMs will be deleted without answer. (forum rule #6)
Extension for Firefox/Thunderbird: Master Password+    Back/Forward History Tweaks (restartless)    Cookies Manager+    Fit Images (restartless for Thunderbird)

Offline darkman83

  • Pre-Newbie
  • Posts: 5
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #9 on: October 29, 2005, 12:34:47 PM »
okay i've tried....if i do so it shows me no more images up and i get those two error lines back as before xD

Offline punzeroni

  • Pre-Newbie
  • Posts: 9
    • View Profile
    • milchsemmel.de
Re: query problems with MySQL 5.0.x
« Reply #10 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

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Global Moderator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: query problems with MySQL 5.0.x
« Reply #11 on: October 29, 2005, 06:16:51 PM »
@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.
I'd say the brackets version ;)
But, I'm still waiting for Jan's responce about "would it be faster to left join the second table instead of joining them as it is now":
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_images i
LEFT JOIN 4images_categories c ON (c.cat_id = i.cat_id)

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) LIMIT 290, 1
In fact since the only value is being read from 4images_categories table is "cat_name", and that value is avalabe by default in $cat_cache array, I'd say 4images_categories table should not be included in the query at all and the final queries with work around would be:
Code: [Select]
    $sql = "SELECT DISTINCT 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".get_user_table_field(", u.", "user_name")."
            FROM ".IMAGES_TABLE." i
            LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
            WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql)
            ORDER BY RAND()";
    $result = $site_db->query($sql);
    while ($row = $site_db->fetch_array($result)) {
      $random_image_cache[$row['cat_id']] = $row;
      $random_image_cache[$row['cat_id']]['cat_name'] = $cat_cache[$row['cat_id']]['cat_name'];

And

Code: [Select]
    $sql = "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".get_user_table_field(", u.", "user_name")."
            FROM ".IMAGES_TABLE." i
            LEFT JOIN ".USERS_TABLE." u ON (".get_user_table_field("u.", "user_id")." = i.user_id)
            WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql)
            LIMIT $number, 1";
    $random_image_cache[0] = $site_db->query_firstrow($sql);
    $random_image_cache[0]['cat_name'] = $cat_cache[$random_image_cache[0]['cat_id']]['cat_name'];
Your first three "must do" before you ask a question:
Please do not PM me asking for help unless you've been specifically asked to do so. Such PMs will be deleted without answer. (forum rule #6)
Extension for Firefox/Thunderbird: Master Password+    Back/Forward History Tweaks (restartless)    Cookies Manager+    Fit Images (restartless for Thunderbird)

Offline darkman83

  • Pre-Newbie
  • Posts: 5
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #12 on: October 29, 2005, 11:24:30 PM »
Ah okay...thanks...that worked...no more error is showing up ^^
YEAHH BIG THANKS!!!!  :lol: :mrgreen: :D :)

Offline baconzoo

  • Jr. Member
  • **
  • Posts: 57
    • View Profile
    • http://www.baconzoo.com/library
Re: query problems with MySQL 5.0.x
« Reply #13 on: November 16, 2005, 01:51:48 PM »
Can you spell out the process of fixing this?  For the lame?

Offline Murphy

  • Pre-Newbie
  • Posts: 5
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #14 on: December 04, 2005, 07:58:54 PM »
please give us a chance to fix it  :roll: