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

0 Members and 1 Guest are viewing this topic.

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Administrator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: query problems with MySQL 5.0.x
« 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:
Code: [Select]
FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." cReplace with:
Code: [Select]
FROM (".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c)

In member.phpadmin/comments.php find:
Code: [Select]
FROM ".COMMENTS_TABLE." c, ".IMAGES_TABLE." iReplace with:
Code: [Select]
FROM (".COMMENTS_TABLE." c, ".IMAGES_TABLE." i)

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

In postcards.php find:
Code: [Select]
FROM ".POSTCARDS_TABLE." p, ".IMAGES_TABLE." iReplace with:
Code: [Select]
FROM (".POSTCARDS_TABLE." p, ".IMAGES_TABLE." i)

In search.php find:
Code: [Select]
FROM ".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." mReplace with:
Code: [Select]
FROM (".WORDLIST_TABLE." w, ".WORDMATCH_TABLE." m)

In includes/auth.php find:
Code: [Select]
FROM ".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." mReplace with:
Code: [Select]
FROM (".GROUP_ACCESS_TABLE." a, ".GROUP_MATCH_TABLE." m)

In admin/home.php find:
Code: [Select]
FROM ".USERS_TABLE." u, ".SESSIONS_TABLE." sReplace with:
Code: [Select]
FROM (".USERS_TABLE." u, ".SESSIONS_TABLE." s)

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

  • Pre-Newbie
  • Posts: 5
    • View Profile
Re: query problems with MySQL 5.0.x
« 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)

Code: [Select]
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

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Administrator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: query problems with MySQL 5.0.x
« 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.
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 #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 :-) ). 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

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Administrator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: query problems with MySQL 5.0.x
« 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.
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 #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

Offline Murphy

  • Pre-Newbie
  • Posts: 5
    • View Profile
Re: query problems with MySQL 5.0.x
« 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:

Offline V@no

  • If you don't tell me what to do, I won't tell you where you should go :)
  • Administrator
  • 4images Guru
  • *****
  • Posts: 17.849
  • mmm PHP...
    • View Profile
    • 4images MODs Demo
Re: query problems with MySQL 5.0.x
« 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?
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 #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 :-)

@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

Offline Murphy

  • Pre-Newbie
  • Posts: 5
    • View Profile
Re: query problems with MySQL 5.0.x
« 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!



Offline punzeroni

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

Offline Murphy

  • Pre-Newbie
  • Posts: 5
    • View Profile
Re: query problems with MySQL 5.0.x
« 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

Offline www.operacija.com

  • Pre-Newbie
  • Posts: 3
    • View Profile
Re: query problems with MySQL 5.0.x
« 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
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.

Offline TheSteffen

  • Pre-Newbie
  • Posts: 3
    • View Profile
Re: query problems with MySQL 5.0.x
« Reply #28 on: January 15, 2006, 03:14:26 PM »
Me too  :(

Please help

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.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'

Offline TheSteffen

  • Pre-Newbie
  • Posts: 3
    • View Profile
Re: query problems with MySQL 5.0.x
« 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 :roll:
Quote
FROM ".IMAGES_TABLE." i,  ".CATEGORIES_TABLE." c

ersetzten wie oben beschrieben.

THX