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]

Originally Posted by Rickie3

I contacted my host but they say its a vbulletin problem

Well 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]

Originally Posted by Rickie3

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

Probably, 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.

PHP Code:

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.