Upgraded to Mysql 5 and am now getting errors

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • cditty
    Senior Member
    • Jan 2001
    • 151

    Upgraded to Mysql 5 and am now getting errors

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

    Code:
    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
    Former Customer Support Manager
    • Jul 2000
    • 154488

    #2
    Please post the complete error message.
    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
    Change CKEditor Colors to Match Style (for 4.1.4 and above)

    Steve Machol Photography


    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


    Comment

    • cditty
      Senior Member
      • Jan 2001
      • 151

      #3
      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

      Comment

      • Steve Machol
        Former Customer Support Manager
        • Jul 2000
        • 154488

        #4
        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.
        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
        Change CKEditor Colors to Match Style (for 4.1.4 and above)

        Steve Machol Photography


        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


        Comment

        • Scott MacVicar
          Former vBulletin Developer
          • Dec 2000
          • 13286

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

          3.0.x does not support MySQL 5.
          Scott MacVicar

          My Blog | Twitter

          Comment

          • cditty
            Senior Member
            • Jan 2001
            • 151

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

            Comment

            • rcc
              Member
              • Dec 2004
              • 77
              • 3.0.3

              #7
              Originally posted by 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
              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:

              PHP Code:
              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

              PHP Code:
              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
              Last edited by rcc; Thu 25 May '06, 9:30am.

              Comment

              • Floris
                Senior Member
                • Dec 2001
                • 37767

                #8
                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.

                Comment

                • rcc
                  Member
                  • Dec 2004
                  • 77
                  • 3.0.3

                  #9
                  Originally posted by 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.
                  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.

                  Comment

                  widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                  Working...