4images Forum & Community
Welcome, Guest. Please login or register.
Did you miss your activation email?
August 18, 2018, 07:43:52 PM

Login with username, password and session length
Search:     Advanced search
Togle to toolbar
Translate this page with =>
Translate this page >
* Home Help Search Login Register
 
+  4images Forum & Community
|-+  4images Issues / Ausgaben
| |-+  Discussion & Troubleshooting (Moderators: Acidgod, ivan, Rembrandt)
| | |-+  query problems with MySQL 5.0.x
0 Members and 1 Guest are viewing this topic. « previous next »
Pages: [1] 2 3 4 5 ... 8 » »» Print
Author Topic: query problems with MySQL 5.0.x  (Read 184889 times)
punzeroni
Pre-Newbie

Offline Offline

Posts: 9

Thank You
-Given: 0
-Receive: 0


View Profile WWW
« 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  [Expand]
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  Shocked

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  [Expand]
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
Logged
V@no
If you don't tell me what to do, I won't tell you where you should go :)
Administrator
4images Guru
*****
Offline Offline

Posts: 17849

Thank You
-Given: 47
-Receive: 577

mmm PHP...


View Profile WWW
« 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?
Logged

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)
punzeroni
Pre-Newbie

Offline Offline

Posts: 9

Thank You
-Given: 0
-Receive: 0


View Profile WWW
« Reply #2 on: October 25, 2005, 03:47:47 PM »

Quote  [Expand]
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
Logged
darkman83
Pre-Newbie

Offline Offline

Posts: 5

Thank You
-Given: 0
-Receive: 0


View Profile
« 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?
Logged
punzeroni
Pre-Newbie

Offline Offline

Posts: 9

Thank You
-Given: 0
-Receive: 0


View Profile WWW
« 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
Logged
darkman83
Pre-Newbie

Offline Offline

Posts: 5

Thank You
-Given: 0
-Receive: 0


View Profile
« 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 ^^
Logged
punzeroni
Pre-Newbie

Offline Offline

Posts: 9

Thank You
-Given: 0
-Receive: 0


View Profile WWW
« 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.
Logged
darkman83
Pre-Newbie

Offline Offline

Posts: 5

Thank You
-Given: 0
-Receive: 0


View Profile
« 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!!!
Logged
V@no
If you don't tell me what to do, I won't tell you where you should go :)
Administrator
4images Guru
*****
Offline Offline

Posts: 17849

Thank You
-Given: 47
-Receive: 577

mmm PHP...


View Profile WWW
« 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   [Expand]
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
1
FROM 4images_images i, 4images_categories c
with:
1
FROM (4images_images i, 4images_categories c)
see if it helps
Logged

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)
darkman83
Pre-Newbie

Offline Offline

Posts: 5

Thank You
-Given: 0
-Receive: 0


View Profile
« 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
Logged
punzeroni
Pre-Newbie

Offline Offline

Posts: 9

Thank You
-Given: 0
-Receive: 0


View Profile WWW
« 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 Smile

@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 Smile )

@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
Logged
V@no
If you don't tell me what to do, I won't tell you where you should go :)
Administrator
4images Guru
*****
Offline Offline

Posts: 17849

Thank You
-Given: 47
-Receive: 577

mmm PHP...


View Profile WWW
« 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 Wink
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  [Expand]
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:
1
2
3
4
5
6
7
8
9
    $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

1
2
3
4
5
6
7
    $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'];
Logged

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)
darkman83
Pre-Newbie

Offline Offline

Posts: 5

Thank You
-Given: 0
-Receive: 0


View Profile
« Reply #12 on: October 29, 2005, 11:24:30 PM »

Ah okay...thanks...that worked...no more error is showing up ^^
YEAHH BIG THANKS!!!!  Laughing Mr. Green Very Happy Smile
Logged
baconzoo
Jr. Member
**
Offline Offline

Posts: 57

Thank You
-Given: 0
-Receive: 0


View Profile WWW
« Reply #13 on: November 16, 2005, 01:51:48 PM »

Can you spell out the process of fixing this?  For the lame?
Logged
Murphy
Pre-Newbie

Offline Offline

Posts: 5

Thank You
-Given: 0
-Receive: 0


View Profile
« Reply #14 on: December 04, 2005, 07:58:54 PM »

please give us a chance to fix it  Rolling Eyes
Logged
Pages: [1] 2 3 4 5 ... 8 » »» Print 
« previous next »
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF | SMF © 2015, Simple Machines Valid XHTML 1.0! Valid CSS!
Page created in 0.061 seconds with 19 queries.
Post your comments here