database error help needed please!!

[Rickie3]

Hi my host has just upgraded to Mysql5.0.21-standard and PHP5.1.4

and now i'm getting a database error, clicking the userCP in the navbar
the error returned is as follows

Database error in vBulletin 3.5.0:

Invalid SQL:

SELECT thread.threadid, thread.forumid,
IF(threadread.readtime IS NULL, 1147835566, IF(threadread.readtime < 1147835566, 1147835566, threadread.readtime)) AS threadread,
IF(forumread.readtime IS NULL, 1147835566, IF(forumread.readtime < 1147835566, 1147835566, forumread.readtime)) AS forumread,
thread.lastpost, subscribethread.subscribethreadid
FROM sg_thread AS thread,
sg_subscribethread AS subscribethread
LEFT JOIN sg_threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = 1)
LEFT JOIN sg_forumread AS forumread ON (forumread.forumid = thread.forumid AND forumread.userid = 1)
WHERE subscribethread.threadid = thread.threadid
AND subscribethread.userid = 1
AND thread.visible = 1
HAVING thread.lastpost > IF(threadread > forumread, threadread, forumread);

MySQL Error : Unknown column 'thread.threadid' in 'on clause'
Error Number : 1054
Date : Saturday, July 15th 2006 @ 11:12:46 PM
Script : http://www.sats-general.com/forum/usercp.php
Referrer : http://www.sats-general.com/forum/index.php?
IP Address : xxxxxxxxxxxxxxx
Username : Rickie3
Classname : vB_Database

I contacted my host but they say its a vbulletin problem,but i didnt have issuues until the upgraded.Any help appreciated

[Paul M]

I contacted my host but they say its a vbulletin problemWell they could argue that - it's a change in mysql that causes that query (which used to work fine) to now fail - you need to upgrade to vb 3.5.4.

[Rickie3]

is there anyway to fix the problem without having to upgrade please

i did have other errors but i found a fix for them here http://www.vbulletin.com/forum/bugs35.php?bugid=2449

its just the userCP that is causing me grief now everything else is functioning perfectly on my board

[Paul M]

is there anyway to fix the problem without having to upgrade pleaseProbably, but there is no excuse not to upgrade, 3.5.0 has security bugs as well.

[Rickie3]

thanx for all your help Paul M,I took your advise and upgraded to 3.5.4, all problems now solved,and no errors so far,cheers

[Code Monkey]

For future reference, you needed to put the multiple FROM tables between brackets () when you have JOINS.


SELECT thread.threadid, thread.forumid,
IF(threadread.readtime IS NULL, 1147835566, IF(threadread.readtime < 1147835566, 1147835566, threadread.readtime)) AS threadread,
IF(forumread.readtime IS NULL, 1147835566, IF(forumread.readtime < 1147835566, 1147835566, forumread.readtime)) AS forumread,
thread.lastpost, subscribethread.subscribethreadid

FROM (sg_thread AS thread,
sg_subscribethread AS subscribethread)

LEFT JOIN sg_threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = 1)
LEFT JOIN sg_forumread AS forumread ON (forumread.forumid = thread.forumid AND forumread.userid = 1)
WHERE subscribethread.threadid = thread.threadid
AND subscribethread.userid = 1
AND thread.visible = 1
HAVING thread.lastpost > IF(threadread > forumread, threadread, forumread);


I had to figure this out on many of my non vb apps I have written when I upgraded. What a joy that was.