Announcement

Collapse
No announcement yet.

vB crashes MySQL on one really, really long query.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • vB crashes MySQL on one really, really long query.

    Hi all. I've seen this a few times. I get a MySQL error on the following query:


    Code:
    Invalid SQL:
    
                                REPLACE INTO search
                                    (userid, titleonly, ipaddress, personal, query, searchuser, forumchoice, prefixchoice, sortby, sortorder, searchtime, showposts, orderedids, dateline, searchterms, displayterms, searchhash, completed)
                                VALUES
                                    (0,
                                    0,
                                    '75.36.203.166',
                                    0,
                                    'oil cooler 911',
                                    '',
                                    '257,8,223',
                                    '',
                                    'rank',
                                    'DESC',
                                    49.94111, 1,
                                    '2597545,2597470,2597565,2597589,2597601,2597592,2597465,2597442,2597307,2597303,2597322,2597329,2597429,2597607,2597631,2597840,2597814,3671638,2597861,2597881,2597806,2597768,2597720,2597677,2597751,2597764,2597766,2597273,2597272,2597068,2597045,2597069,2597086,2597095,2597032,2597030,2597001,2596988,2597024,2597025,2597034,2597104,2597137,2597245,2597240,2597254,2597260,2597262,2597233,2597172,2597141,2597138,2597150,2597159,2597163,2597904,2597910,2598599,2598585,2688679,2954387,2598697,2598545,2598493,2598411,2598369,2598420,2598423,2598462,2598723,2598773,2598947,2598943,2598967,2598972,2598993,2598913,2598907,2598810,2598805,2598843,2598889,2598898,2598349,2598337,2598091,
    
    [this section is about 15,000 lines long]
    
                                    1254023413,
                                    'a:25:{s:5:\"query\";s:14:\"oil cooler 911\";s:10:\"searchuser\";s:0:\"\";s:9:\"exactname\";i:1;s:11:\"starteronly\";i:0;s:3:\"tag\";s:0:\"\";s:11:\"forumchoice\";a:2:{i:0;s:3:\"257\";i:1;s:1:\"8\";}s:12:\"prefixchoice\";a:0:{}s:11:\"childforums\";i:1;s:9:\"titleonly\";i:0;s:9:\"showposts\";i:1;s:10:\"searchdate\";s:1:\"0\";s:11:\"beforeafter\";s:5:\"after\";s:6:\"sortby\";s:4:\"rank\";s:9:\"sortorder\";s:10:\"descending\";s:9:\"replyless\";i:0;s:10:\"replylimit\";i:0;s:14:\"searchthreadid\";i:0;s:9:\"saveprefs\";i:0;s:11:\"quicksearch\";i:0;s:10:\"searchtype\";i:1;s:7:\"exclude\";s:0:\"\";s:7:\"nocache\";i:0;s:4:\"ajax\";i:0;s:11:\"humanverify\";a:2:{s:5:\"input\";s:6:\"hG2E9p\";s:4:\"hash\";s:32:\"b269e7908dbe1b84b57eb614ae635a57\";}s:6:\"userid\";i:0;}',
                                    'a:8:{s:5:\"words\";a:1:{i:0;s:34:\"oil</u> OR <u>cooler</u> OR <u>911\";}s:9:\"highlight\";a:3:{i:0;s:3:\"oil\";i:1;s:6:\"cooler\";i:2;s:3:\"911\";}s:6:\"common\";a:0:{}s:5:\"users\";a:0:{}s:6:\"forums\";a:2:{i:257;i:257;i:8;i:8;}s:8:\"prefixes\";a:0:{}s:3:\"tag\";s:0:\"\";s:7:\"options\";a:3:{s:11:\"starteronly\";i:0;s:11:\"childforums\";i:1;s:6:\"action\";s:7:\"process\";}}',
                                    'e2de7f5182ceb313b62a13dfdd036879',
                                    1);
    
    MySQL Error   : MySQL server has gone away
    Error Number  : 2006
    Request Date  : Saturday, September 26th 2009 @ 08:49:23 PM
    Error Date    : Saturday, September 26th 2009 @ 08:50:13 PM
    Script        : http://forums.pelicanparts.com/search.php?do=process
    Referrer      : http://forums.pelicanparts.com/search.php?do=process
    IP Address    : 75.36.203.166
    Username      : Unregistered
    Classname     : vB_Database
    MySQL Version :
    I realize that I have a pretty big forum, and this is probably too big of a query to run on the server. So, what can be done to minimize / prevent this error? I got three of them today.

    Thanks in advance,

    Wayne

  • #2
    You can try reducing this number to see if it helps with that:

    Admin CP -> vBulletin Options -> Message Searching Options -> Maximum Search Results to Return

    However this is a server issue. You'll need to contact your host about this. Here are the reasons for the MySQL server going away:

    http://www.vbulletin.com/forum/showt...265#post310265

    Also from the MySQL site:

    http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
    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


    • #3
      Hi Steve. Thanks for your reply. I do indeed have it set to return 500 matches in the vB control panel. I am running my own server (two servers, one running IIS, and the other is a dedicated MySQL machine). I have gotten this error from time to time over the past several years, but I usually just ignored it. Tonight I got it three times.

      Perhaps I wasn't being clear in my original post - I apologize. The query that vB is trying to send to MySQL is INCREDIBLY HUGE (1700 kb). I'm a database / Perl programmer and I believe that this is a bug in vBulletin that probably only shows up on very large forums. MySQL shouldn't be given a query that is several megabytes in size - it should be split up into smaller chunks.

      Here's a link to the actual query, it's almost 2 Megs in size: http://www.pelicanparts.com/temp/MySQL.txt

      I believe the problem is caused by too many items in the array:
      intval($vbulletin->GPC['showposts'])
      in the file search.php.

      Can you please have someone look into it?

      Thanks,

      Wayne

      Comment


      • #4
        Have you tried to set the return to something like 25 and see if the query is smaller in size then?

        Returning 500 is useless imo

        Just a thought.
        www.yamaha-forum.net | www.exactservers.com | www.ducati-superbikes.com | www.suzuki-forums.net | www.diavel-forum.com

        Comment


        • #5
          I haven't tried that. 500 is a lot, but sometimes useful, as my users will page through results to find what they are looking for. 25 would be too few results returned. I think this is a function of the total number of posts that would match this search - the size of the query contains far more than 500 results. My guess is that the 500 limit is being imposed on the results returned to the user, but not in the data / posts indexed in the query. The solution to this problem would probably be restricting the number of posts in this particular query to match the number of search returns in the control panel.

          I'm sure that this is just an oversight (bug?) in vB that only happens on large forums, and only with certain queries.

          -Wayne

          Comment


          • #6
            This is not a bug. Have you tried my original suggestion which was to reduce that number?
            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


            • #7
              I have no means to replicate the problem, as I am not aware of the original circumstances (i.e. actions by the users that caused this to happen). At the very least, if it's not a "bug", then it's very poor programming. MySQL shouldn't be given a 1700K query under any circumstances - there should be a mechanism within the code to limit within reason, the size of the query.

              If I limit the number of returns, then the ability of people to search through the archives is severely restricted. Since results are returned in date order, anything beyond the 500 searches is lost forever.

              -Wayne

              Comment


              • #8
                You are free to report this in the bug tracker if you want. However this really is a server limitation and not a vB coding issue.
                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


                • #9
                  Is there a way to figure out what search query triggered the above statement, so that I may adjust the results returned value and retest?

                  -Wayne

                  Comment


                  • #10
                    It is from someone doing a search on your forums.
                    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


                    • #11
                      Originally posted by pelicanparts View Post
                      Is there a way to figure out what search query triggered the above statement, so that I may adjust the results returned value and retest?

                      -Wayne
                      Please re-read my question above and let me know if you have an answer.

                      To make it even *more* clear, I do not know *exactly* what search query triggered this response.

                      -Wayne

                      Comment


                      • #12
                        Someone was searching your forums for this: oil cooler 911

                        I hope that is exact enough.
                        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


                        • #13
                          Thanks, that's all I was looking for.

                          -Wayne

                          Comment


                          • #14
                            More specifically, they were requesting all posts as opposed to just a thread list.
                            Baby, I was born this way

                            Comment


                            • #15
                              This has been in VB for years and rears its ugly head on my forum. Case in point.

                              Search for 'car not starting very well' in a quick search and it results in vB passing a 16MB SQL statement to mySQL, which quite rightly tells it where to go.

                              How this is not considered a bug is beyond me.
                              Regards

                              Steve.
                              www.mg-rover.org - Forum for MG and Rover owners everywhere!
                              85k users and 4million posts & growing.

                              Comment

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