Upgraded to Mysql 5 and am now getting errors

[cditty]

I just upgraded my server to mysql 5 and I am getting these errors every now and then. Any suggestions?

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 = 756
AND thread.visible = 1
AND lastpost > 1148125274
AND deletionlog.primaryid IS NULL

[Steve Machol]

Please post the complete error message.

[cditty]

Database error in vBulletin 3.0.8:


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 = 1221
AND thread.visible = 1
AND lastpost > 1148163935
AND deletionlog.primaryid IS NULL


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


mysql error number: 1054

[Steve Machol]

This means that either:

1. Your thread table is messed up.

2. You are using a table prefix and have not edited your config.php to use that prefix.

3. You have installed a hack that is causing this.

I would first check your database and see if you are using a prefix.

[Scott MacVicar]

Steve these are known issues with MySQL 5 that are fixed in 3.5.x.

3.0.x does not support MySQL 5.

[cditty]

Well crap. What a time for me to let me subscription run out. Thanks Scott.

[rcc]

Database error in vBulletin 3.0.8:


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 = 1221
AND thread.visible = 1
AND lastpost > 1148163935
AND deletionlog.primaryid IS NULL


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


mysql error number: 1054

I had the same error after upgrading to MySQL 5.0.19-standard

The error I was geting is:

Database error in vBulletin 3.0.3:

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 = 1083
AND thread.visible = 1
AND lastpost > 1147857568
AND deletionlog.primaryid IS NULL

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

mysql error number: 1054

Date: Wednesday 17th of May 2006 08:42:08 AM
Script: http://saudielection.com/ar/forum/usercp.php?
Referer: http://saudielection.com/ar/forum/login.php
Username: ………
IP Address: ………..


This error did not allow any one to go to there CP on the forum.

This is how I fixed it:

edit usercp.php

find:

FROM " . TABLE_PREFIX . "thread AS thread, " . TABLE_PREFIX . "subscribethread AS subscribethread
LEFT JOIN " . TABLE_PREFIX . "deletionlog AS deletionlog ON(deletionlog.primaryid = thread.threadid AND type = 'thread')
WHERE subscribethread.threadid = thread.threadid
AND subscribethread.userid = $bbuserinfo[userid] 

replace with

FROM " . TABLE_PREFIX . "subscribethread AS subscribethread
LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON(subscribethread.threadid = thread.threadid)
LEFT JOIN " . TABLE_PREFIX . "deletionlog AS deletionlog ON(deletionlog.primaryid = thread.threadid AND type = 'thread')
WHERE subscribethread.userid = $bbuserinfo[userid] 

PS:
I found this fix on a forum (not vbulletin) which deals with vb errors among other things.

good luck

[Floris]

Hey cool, nice of you to help fix this.

Note to people who read this though: Always make a backup of your database and files before making customizations like these; this way you can revert back to the original version and try again.

[rcc]

Hey cool, nice of you to help fix this.

Note to people who read this though: Always make a backup of your database and files before making customizations like these; this way you can revert back to the original version and try again.

You are absolutely right. ALLWAYS make backup of EVERYTHING before doing any changes to the code or the database.

I have screwed up many times , but I was allways able to go back to my original settings because of backup.

Any way I thought this might help some poor sole like me struglling to patch things up.