4images Forum & Community
Welcome, Guest. Please login or register.
Did you miss your activation email?
May 24, 2018, 09:34:14 PM

Login with username, password and session length
Search:     Advanced search
Follow 4images on twitter: Click here to follow!
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 6 ... 8 » »» Print
Author Topic: query problems with MySQL 5.0.x  (Read 183260 times)
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: 565

mmm PHP...


View Profile WWW
« Reply #15 on: December 04, 2005, 08:26:19 PM »

Ok, please test this (if it all works, I'll post it as a compability fix):

Please note, in each file is possible more then one instance of searched line, that means all of the found lines must be changed as instructed!

In index.phpcategories.phpdetails.phppostcards.phpsearch.phptop.phpincludes/functions.php (two times) find:
1
FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c
Replace with:
1
FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)


In member.phpadmin/comments.php find:
1
FROM ".COMMENTS_TABLE." c, ".IMAGES_TABLE." i
Replace with:
1
FROM (".COMMENTS_TABLE." c, ".IMAGES_TABLE." i)


Stop here. Test every page and only if you have problem continue next:

In postcards.php find:
1
FROM ".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i
Replace with:
1
FROM (".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i)


In search.php find:
1
FROM ".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." m
Replace with:
1
FROM (".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." m)


In includes/auth.php find:
1
FROM ".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." m
Replace with:
1
FROM (".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." m)


In admin/home.php find:
1
FROM ".USERS_TABLE." u, ".SESSIONS_TABLE." s
Replace with:
1
FROM (".USERS_TABLE." u, ".SESSIONS_TABLE." s)


In admin/usergroups.php find:
1
FROM ".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gm
Replace with:
1
FROM (".GROUPS_TABLE." g, ".GROUP_MATCH_TABLE." gm)
« Last Edit: January 15, 2006, 09:25:45 PM by Acidgod » 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)
Murphy
Pre-Newbie

Offline Offline

Posts: 5

Thank You
-Given: 0
-Receive: 0


View Profile
« Reply #16 on: December 04, 2005, 11:41:57 PM »

I'm sorry, but i get always this
(tried 2 times with a new installation and an existing album)

1
2
3
4
5
6
7
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) AND c.cat_id = i.cat_id LIMIT 0, 1
Unknown column 'i.user_id' in 'on clause'

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 = 1 AND c.cat_id = i.cat_id ORDER BY image_name ASC LIMIT 0, 9
Unknown column 'i.user_id' in 'on clause'

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in x\4images\includes\db_mysql.php on line 116
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: 565

mmm PHP...


View Profile WWW
« Reply #17 on: December 05, 2005, 01:01:03 AM »

then you missed a line in the a file. cause in the error its clear that you didnt do the changes on that particular query that produces the error.
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 #18 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 Smile ). 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
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: 565

mmm PHP...


View Profile WWW
« Reply #19 on: December 05, 2005, 02:47:48 PM »

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'll try ask some experts on this metter.


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.
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 #20 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
Logged
Murphy
Pre-Newbie

Offline Offline

Posts: 5

Thank You
-Given: 0
-Receive: 0


View Profile
« Reply #21 on: December 07, 2005, 12:43:54 PM »

then you missed a line in the a file. cause in the error its clear that you didnt do the changes on that particular query that produces the error.
i changed the files with the help of "punzeroni"
now i get the error only on the starting site.
the other one are o.k.

there is no "new picture" showing on the same site too
could it be the same error?
howcan i fix it?

many thanks or helping me  Wink
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: 565

mmm PHP...


View Profile WWW
« Reply #22 on: December 10, 2005, 07:36:34 AM »

I just upgraded mine to v5.0.16 (lattest) and see no errors...did they added support for these type of queries?

Anyone can confirm/deny that v5.0.16 has no problems?
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 #23 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 Smile

@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
Logged
Murphy
Pre-Newbie

Offline Offline

Posts: 5

Thank You
-Given: 0
-Receive: 0


View Profile
« Reply #24 on: December 10, 2005, 04:18:23 PM »

after installing the server completely new, i don't get any error.
i think, there was something in the cache ?
any idea's why?

now it works perfect  Wink

@punzeroni
error: look at reply 16!


Logged
punzeroni
Pre-Newbie

Offline Offline

Posts: 9

Thank You
-Given: 0
-Receive: 0


View Profile WWW
« Reply #25 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
Logged
Murphy
Pre-Newbie

Offline Offline

Posts: 5

Thank You
-Given: 0
-Receive: 0


View Profile
« Reply #26 on: December 10, 2005, 05:07:25 PM »

i reinstalled xampp
http://www.apachefriends.org/en/xampp.html

for me the best, because completely configuered
Logged
www.operacija.com
Pre-Newbie

Offline Offline

Posts: 3

Thank You
-Given: 0
-Receive: 0


View Profile
« Reply #27 on: January 09, 2006, 06:19:15 PM »

hi,

I install fresh copy off 4images
then I use http://www.milchsemmel.de/~punzeroni/4images-SQL2003fix.tar.bz2 patch, but I've got error:

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) AND c.cat_id = i.cat_id LIMIT 0, 1
Unknown column 'i.user_id' in 'on clause'

I don't know what to do now... :/

MySQL version: 5.0.18

help me, please. thnx.
Logged
TheSteffen
Pre-Newbie

Offline Offline

Posts: 3

Thank You
-Given: 0
-Receive: 0


View Profile
« Reply #28 on: January 15, 2006, 03:14:26 PM »

Me too  Sad

Please help

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.username FROM 4images_images i, 4images_categories c LEFT JOIN phpbb_users u ON (u.user_id = i.user_id) WHERE i.image_active = 1 AND i.cat_id NOT IN (0) AND c.cat_id = i.cat_id LIMIT 2580, 1
Unknown column 'i.user_id' in 'on clause'
Logged
TheSteffen
Pre-Newbie

Offline Offline

Posts: 3

Thank You
-Given: 0
-Receive: 0


View Profile
« Reply #29 on: January 15, 2006, 07:11:53 PM »

das hier ist in der functions.php 2x vorhanden und muß auch 2x ersetzt werden Rolling Eyes
Quote  [Expand]
FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c

ersetzten wie oben beschrieben.

THX
Logged
Pages: «« « 1 [2] 3 4 5 6 ... 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.193 seconds with 20 queries.
Post your comments here