Announcement

Collapse
No announcement yet.

Strange intermittent SQL error

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

  • #16
    hmmm i'll direct a vB dev to this thread to see if he can help
    :: Always Back Up Forum Database + Attachments BEFORE upgrading !
    :: Nginx SPDY SSL - World Flags Demo [video results]
    :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

    Comment


    • #17
      This is happening again, right now, as we speak. My /tmp directory is rising and falling in extremely large increments. However, I don't see any files in there. Something besides files must be increasing the file system space.

      Comment


      • #18
        Can I get a me too up in here?

        I too am now getting this problem...

        Like 12 emails an hour.

        The only file it's complaining about is external.php

        Help?

        Code:
        Database error in vBulletin 3.0.7:
        
        Invalid SQL: 
        		SELECT thread.threadid, thread.title, thread.lastposter, thread.lastpost, thread.postusername, thread.dateline, forum.forumid, forum.title AS forumtitle, post.pagetext AS preview
        		FROM thread AS thread
        		INNER JOIN forum AS forum ON(forum.forumid = thread.forumid)
        		LEFT JOIN post AS post ON (post.postid = thread.firstpostid)
        		LEFT JOIN deletionlog AS deletionlog ON (deletionlog.primaryid = thread.threadid AND deletionlog.type = 'thread')
        		WHERE 1=1
        			AND thread.forumid IN(3,4,5,6,7,8,9,11,10,16,12,13,17)
        			AND thread.visible = 1
        			AND open <> 10
        			AND deletionlog.primaryid IS NULL
        		ORDER BY thread.dateline DESC
        		LIMIT 15
        	
        mysql error: Got error 28 from table handler
        
        mysql error number: 1030
        
        Date: Monday 05th of September 2005 06:51:58 AM
        Script: http://www.nullwhore.com/forums/external.php
        Referer: 
        IP Address: 65.93.54.17

        Comment


        • #19
          This is still biting me once every 7 to 10 days. I'll get a flood of errors, then everything goes back to normal.

          It appears that something is creating a massive file or files in /tmp. So big, that when it deletes the file, it takes a long for the file system to reclaim the space for re-use. During that period is when the errors show up.

          What is creating this huge /tmp file(s)??

          Comment


          • #20
            Just a guess out of the blue here but since its dealing with the external.php - could it be some site with an unoptimzed rss reader requesting the rss feeds for each user at thier site?
            Plan, Do, Check, Act!

            Comment


            • #21
              From the access_logs, that doesn't appear to be happening. I'm now thinking the fact that the errors are associated with the external.js script is a secondary artifact of the problem - not the cause.

              Comment


              • #22
                Freddie I believe is working on the issue he said:

                I can't explain the details of what is happening to his filesystem but the cause is the query is throwing a "Using Filesort" which is generating the extra data to be written to the filesystem. We either have to get rid of the filesort, which we need to strive to do everywhere or cache external.php

                The best solution would be to achieve both results.
                I've asked Freddie if maybe Gary you'd like to be a test case for his work around/fixes ???
                :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                :: Nginx SPDY SSL - World Flags Demo [video results]
                :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                Comment


                • #23
                  Its possible. Have him contact me via PM.

                  Comment


                  • #24
                    You are still having this problem with RC3?

                    Comment


                    • #25
                      I'm running 3.0.9. I don't put beta software on my production servers.

                      Here's some interesting info from the last incident (about an hour ago):
                      Code:
                      [email protected]: du -sk /tmp
                      20      /tmp
                      [email protected]: df /tmp
                      Filesystem           1K-blocks      Used Available Use% Mounted on
                      /dev/sda2              2585332    864480   1589524  36% /tmp
                      [email protected]: uptime
                       12:42:44  up 122 days,  7:04,  1 user,  load average: 18.76, 18.82, 10.01
                      [email protected]: ls -ltra /tmp
                      total 24
                      drwx------    2 root     root        16384 Apr 15 01:02 lost+found
                      drwxr-xr-x   24 root     root         4096 May 28 05:37 ..
                      drwxrwxrwx    3 root     root         4096 Sep 27 12:42 .
                      What's interesting about this is that you can see from the df that the /tmp file system is 36% full, but listing the file system shows nothing there. I suspect the inode of the temporary file was already removed, but the space wasn't yet freed up. Meanwhile, while this was taking place, the disk susbsystem was so busy, it caused high utilization of the entire platform, causing the app to be unavailable.

                      Comment


                      • #26
                        3.5.0 includes a potential solution for this problem. I say potential because I can't be certain that it will resolve your issue but I see no reason that it won't.

                        Comment


                        • #27
                          I guess I'll have to wait until I upgrade to 3.5. Meanwhile, can you explain what the cause is? In technical terms - not layman's terms.

                          Comment


                          • #28
                            You can add an index to thread.dateline to see if it helps your situation. You'll want to remove the index before your upgrade to 3.5 as you will end up with the field indexed twice if you don't.

                            To ADD: ALTER TABLE thread ADD INDEX (dateline)

                            To Delete Later: ALTER TABLE thread DROP INDEX dateline

                            Comment


                            • #29
                              I'll give it a shot. Thanks!

                              Comment


                              • #30
                                Didn't work. Just happened again.
                                Next?

                                Comment

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