database error mysql?

[cmb7684]

My server was recently upgraded from omnicron to adama ..im not sure if this is what caused this but since then I have come across this problem when hitting the user cp button in either the vbindex page or forums page in vbulletin. All other buttons work ok. Does anyone have any idea what is wrong and how I can fix it?




Database error in vBulletin 3.0.6:

Invalid SQL:
SELECT thread.threadid, thread.forumid
FROM thread AS thread, subscribethread AS subscribethread
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = thread.threadid AND type = 'thread')
WHERE subscribethread.threadid = thread.threadid
AND subscribethread.userid = 1
AND thread.visible = 1
AND lastpost > 1189059102
AND deletionlog.primaryid IS NULL

mysql error: Unknown column 'thread.threadid' in 'on clause'

mysql error number: 1054

Date: Thursday 06th of September 2007 02:36:10 AM
Script: http://www.mysite.org/forums/usercp.php?
Referer: http://www.mysite.org/forums/vbindex.php
Username: XXXXXXX
IP Address: XXXXXXX

[Eikinskjaldi]

Are you able to access mysql tables via phpmyadmin or a command line? This looks like your threadid index column has been deleted, but that can't be right if viewing threads works fine.

I can't recall 3.0.6 particularly clearly, but do you have any mods that would be affecting latest or subscribed threads drawn up in the usercp and the vbindex? That query seems wrong to me, like it should be:

SELECT thread.threadid, thread.forumid
FROM thread AS thread, subscribethread AS subscribethread
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = thread.threadid)
WHERE subscribethread.threadid = thread.threadid
AND type = 'thread'
AND subscribethread.userid = 1
AND thread.visible = 1
AND lastpost > 1189059102
AND deletionlog.primaryid IS NULL

[Marco van Herwaarden]

Long time ago i have seen this same error. If i remember correctly it has to do with your MySQL version (you probably upgraded from 3.x to 4.x or 5.x). The error is probably caused by a modification.

[cmb7684]

A modification to what? Is this something in vbulletin I need to fix or is it something on the host or server's side?

[Lynne]

Try this (note parenthesis in FROM statement):

SELECT thread.threadid, thread.forumid
FROM (thread AS thread, subscribethread AS subscribethread)
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = thread.threadid AND type = 'thread')
WHERE subscribethread.threadid = thread.threadid
AND subscribethread.userid = 1
AND thread.visible = 1
AND lastpost > 1189059102
AND deletionlog.primaryid IS NULL

[Paul M]

I think it's this bug ;

http://www.vbulletin.com/forum/project.php?issueid=6207

[cmb7684]

Thanks guys ......I tried the link posted by Paul M and it worked like a champ. Thanks a million