added in a slave db server, getting a mysql error

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • JonnyQuest
    Senior Member
    • Apr 2000
    • 252

    added in a slave db server, getting a mysql error

    Hi,

    We just added in a new db server to our setup (now two servers: master + slave). We are getting some sporadic mysql errors:

    Invalid SQL:

    SELECT MAX(dateline) AS dateline
    FROM post AS post
    WHERE threadid = 1874
    AND dateline <
    AND visible = 1;

    MySQL Error : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND visible = 1' at line 5
    Error Number : 1064
    Date : Friday, January 20th 2006 @ 03:15:09 PM
    Script : http://www.bigfootball.com/forum/newreply.php
    Referrer : http://www.bigfootball.com/forum/new...wreply&p=30445
    Classname : vb_database
  • Steve Machol
    Former Customer Support Manager
    • Jul 2000
    • 154488

    #2
    SQL Syntax errors are almost always related to modified code. To troubleshoot this, the first thing you need to do is remove any hacks or plugins, then see if you still have this problem.

    If so, reupload all the original vB non-image files (except install.php). Make sure you upload these in ASCII format and overwrite the ones on the server.

    Next, create a new style and choose no parent style. This will force it to use the default templates. Finally empty your browser cache, close all browser windows then try again. Make sure you change to the new style and view your forums with it. Do you have the same problem?
    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

    • JonnyQuest
      Senior Member
      • Apr 2000
      • 252

      #3
      I'll work on making those changes.

      What is interesting is that I had this occur on two completely separate vb's when I switched to the new db server setup and not before I switched. To this extent, I feel pretty confident that it is a result of moving to the db master/server setup. I was hoping you could provide a bit of insite as to why we would get mysql errors from moving to a dual db server setup and if this mysql error meant anything to you that might be easily tracked down.

      Comment

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

        #4
        As I said SQL Syntax errors are generally code related. It's possible a file was not upploaded correctly. I'd start with the newreply.php file.
        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

        • JonnyQuest
          Senior Member
          • Apr 2000
          • 252

          #5
          I guess I'm still a big confused. The exact same files. Pointed at a single database server work perfectly. When pointed at a 2 server master/slave database setup, generate the above error on occasion.

          Just to confirm, in this case I should upload the files again?

          Comment

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

            #6
            It wouldn't hurt to upload the files again which is why I suggested this. I'm not aware of any db or server issues that would cause an SQL Syntax error, but I guess anything is possible.
            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

            • Lierduh
              Member
              • Jan 2003
              • 34

              #7
              Same problem here using the 3.5.4 code.

              The problem seems to be within the 'exec_send_notification' function.

              Code:
                      if ($postid)
                      {
                              $dateline = $vbulletin->db->query_first("
                                      SELECT dateline, pagetext
                                      FROM " . TABLE_PREFIX . "post
                                      WHERE postid = $postid
                              ");
              
                              $pagetext = $dateline['pagetext'];
              
                              $lastposttime = $vbulletin->db->query_first("
                                      SELECT MAX(dateline) AS dateline
                                      FROM " . TABLE_PREFIX . "post AS post
                                      WHERE threadid = $threadid
                                      AND dateline < $dateline[dateline]
                                      AND visible = 1
                              ");
              What would happen is the postid is fetched from the master server.
              When retrieving pagetext and dateline from the post table in the slave
              server, because the slave server may not necessarily been updated by
              the time the scripts hits it. I think either 'sleep' the script for a
              couple of seconds or better using a loop to make sure $dateline query
              result is not empty before carrying on.

              This thread has the same problem:

              Comment

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

                #8
                Feel free to report this in the 3.5 Bug Tracker here:
                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

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