Problem after upgrading mysql to 5.0.15

[sickboy]

When going to the user control panel:

Database error in vBulletin 3.5.0:
Invalid SQL:
SELECT thread.threadid, thread.forumid,
IF(threadread.readtime IS NULL, 1125072776, IF(threadread.readtime < 1125072776, 1125072776, threadread.readtime)) AS threadread,
IF(forumread.readtime IS NULL, 1125072776, IF(forumread.readtime < 1125072776, 1125072776, forumread.readtime)) AS forumread,
thread.lastpost, subscribethread.subscribethreadid
FROM thread AS thread,
subscribethread AS subscribethread
LEFT JOIN threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = 1)
LEFT JOIN 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 : Tuesday, October 25th 2005 @ 06:12:56 PM
Script : http://www.xxxxxxxx.xx/usercp.php
Referrer : http://www.xxxxxxxx.xx/
IP Address : xx.xxx.xxx.xx
Username : Sickboy
Classname : vB_Database_MySQLi

[Boxy]

This has been fixed in the next version to support MySQL 5.0.x but until then you will need to update the query manually in the usercp.php file (line 283)

from:

SELECT thread.threadid, thread.forumid,
   IF(threadread.readtime IS NULL, $readtimeout, IF(threadread.readtime < $readtimeout, $readtimeout, threadread.readtime)) AS threadread,
   IF(forumread.readtime IS NULL, $readtimeout, IF(forumread.readtime < $readtimeout, $readtimeout, forumread.readtime)) AS forumread,
   thread.lastpost, subscribethread.subscribethreadid
  FROM " . TABLE_PREFIX . "thread AS thread,
   " . TABLE_PREFIX . "subscribethread AS subscribethread
  LEFT JOIN " . TABLE_PREFIX . "threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = " . $vbulletin->userinfo['userid'] . ")
  LEFT JOIN " . TABLE_PREFIX . "forumread AS forumread ON (forumread.forumid = thread.forumid AND forumread.userid = " . $vbulletin->userinfo['userid'] . ")
  WHERE subscribethread.threadid = thread.threadid
   AND subscribethread.userid = " . $vbulletin->userinfo['userid'] . "
   AND thread.visible = 1
  HAVING thread.lastpost > IF(threadread > forumread, threadread, forumread)

to:

SELECT thread.threadid, thread.forumid,
   IF(threadread.readtime IS NULL, $readtimeout, IF(threadread.readtime < $readtimeout, $readtimeout, threadread.readtime)) AS threadread,
   IF(forumread.readtime IS NULL, $readtimeout, IF(forumread.readtime < $readtimeout, $readtimeout, forumread.readtime)) AS forumread,
   thread.lastpost, subscribethread.subscribethreadid
  FROM " . TABLE_PREFIX . "subscribethread AS subscribethread
  LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (subscribethread.threadid = thread.threadid)
  LEFT JOIN " . TABLE_PREFIX . "threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = " . $vbulletin->userinfo['userid'] . ")
  LEFT JOIN " . TABLE_PREFIX . "forumread AS forumread ON (forumread.forumid = thread.forumid AND forumread.userid = " . $vbulletin->userinfo['userid'] . ")
  WHERE subscribethread.userid = " . $vbulletin->userinfo['userid'] . "
   AND thread.visible = 1
  HAVING thread.lastpost > IF(threadread > forumread, threadread, forumread)

[sickboy]

Major thanks, fixed my problem Thanks again!

[JulianD]

I'm using 5.0.13-rc but I can't reproduce that problem on a clean installation... Do I need to have subscribed threads or something? or it is only affecting 5.0.15?

Thanks for the fix though!!