Announcement

Collapse
No announcement yet.

Strange intermittent SQL error

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

  • Strange intermittent SQL error

    This has happened twice since I've moved to vB:
    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(5,51,52,20,16,2,14,15,46,1,10,55,56,38,57,11,12,23,3,48,17,18,25,26,30,58,22,47,4,13,31,19,9,27,49,21,40,41,42,43,44,45,8,35,36,34,32,33,29,37,50)
    			AND thread.visible = 1
    			AND open <> 10
    			AND deletionlog.primaryid IS NULL
    		ORDER BY thread.dateline DESC
    		LIMIT 15
    	
    mysql error: Error writing file '/tmp/MYygAXHp' (Errcode: 28)
    
    mysql error number: 3
    
    Date: Monday 06th of June 2005 10:40:25 AM
    Script: http://forum.miata.net/vb/external.php?type=js
    Now before anyone jumps in and tells me that I'm out of file space, or that my permissions are incorrect:
    Code:
    Filesystem           1K-blocks      Used Available Use% Mounted on
    /dev/hda8              1004024    327976    625044  35% /tmp
    and
    Code:
    drwxrwxrwt    4 root     root         4096 Jun 29 07:22 /tmp
    My question is, what is being written to /tmp? I've never seen any temporary files in there. When does it happen? It seems to be coming from the Javascript syndication call. Could this really be a locking problem of some sort that merely *appears* like a file system problem?

    Note: Both times this happened, it occurred in flood. I.e. - I received about 15 or 20 email notices of the error in rapid succession, then everything was fine and it didn't happen again until 3 weeks later. Then same thing - about 10 or 15 notices, then everything was fine again.

  • #2
    Unfortunately this is definitely an out-of-space error. It's the only thing an error 28 can mean:

    Error code 28: No space left on device

    My guess is that your /tmp partition was temporarily full when this error occurred.
    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
      Originally posted by Steve Machol
      Unfortunately this is definitely an out-of-space error. It's the only thing an error 28 can mean:

      Error code 28: No space left on device

      My guess is that your /tmp partition was temporarily full when this error occurred.
      You know, I'm a pretty smart guy, and I knew that would be your answer. That's why I specifically asked "what is being written to /tmp?"

      You see, I have more than half a Gig in my /tmp partition, and nothing on my server writes to it except vB/MySql. And since I've never seen any temporary files there, clearly they're being written and deleted very quickly. So when does vB/MySql write to /tmp? How much? Are you saying that half a gig of temp space isn't sufficient for vB?

      Lets not just chalk this up to "you /tmp partition was temporarily full". Lets try to figure out the cause so it doesn't continue to happen.

      Comment


      • #4
        I really don't know what is being written to the /tmp directory. I do know what this error means, and I'm sorry if you disagree. If you can find an alternate explanation for error 28, then please let me know. So far I have only found the one explantion.

        And your entire server uses the /tmp directory, not just vB.
        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


        • #5
          Here's a explanation of this error from the MySQL site:

          http://www.mysql.com/news-and-events...000000249.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


          • #6
            It is a temporary out of space error caused by temporary files being created and deleted in the normal course of work. The reason you wouldn't see a file would be because it wasn't able to be created in its entirety and deleted by MySQL.

            I would suggest increasing the size of your temp partition. You should have at least twice your memory allocated for temporary files when running database applications.
            Translations provided by Google.

            Wayne Luke
            The Rabid Badger - a vBulletin Cloud demonstration site.
            vBulletin 5 API

            Comment


            • #7
              Depending on the size of the database that temp table could be really quite big, also there may be more than one at once.
              I wrote ImpEx.

              Blog | Me

              Comment


              • #8
                Originally posted by Gary Fischman
                You know, I'm a pretty smart guy, and I knew that would be your answer. That's why I specifically asked "what is being written to /tmp?"

                You see, I have more than half a Gig in my /tmp partition, and nothing on my server writes to it except vB/MySql. And since I've never seen any temporary files there, clearly they're being written and deleted very quickly. So when does vB/MySql write to /tmp? How much? Are you saying that half a gig of temp space isn't sufficient for vB?

                Lets not just chalk this up to "you /tmp partition was temporarily full". Lets try to figure out the cause so it doesn't continue to happen.
                here's more info on mysql tmpdir as well http://dev.mysql.com/doc/mysql/en/temporary-files.html

                MySQL uses the value of the TMPDIR environment variable as the pathname of the directory in which to store temporary files. If you don't have TMPDIR set, MySQL uses the system default, which is normally /tmp, /var/tmp, or /usr/tmp. If the filesystem containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a filesystem where you have enough space.

                MySQL creates all temporary files as hidden files. This ensures that the temporary files are removed if mysqld is terminated. The disadvantage of using hidden files is that you do not see a big temporary file that fills up the filesystem in which the temporary file directory is located.

                For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form SQL_*.

                ALTER TABLE creates a temporary table in the same directory as the original table.
                :: 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


                • #9
                  Originally posted by Steve Machol
                  I really don't know what is being written to the /tmp directory. I do know what this error means, and I'm sorry if you disagree. If you can find an alternate explanation for error 28, then please let me know. So far I have only found the one explantion.
                  Steve - I never disagreed. No need to get defensive. I'm trying to figure out what part of vB uses /tmp.
                  And your entire server uses the /tmp directory, not just vB.
                  Yes, I understand how *nix works. I also know that nothing I currently have running has much use for /tmp.
                  Originally posted by Steve Machol
                  Here's a explanation of this error from the MySQL site:

                  http://www.mysql.com/news-and-events...000000249.html
                  They mention a "tmpdir" variable, however I don't have one. Presumably it defaults to /tmp?
                  Originally posted by Wayne Luke
                  I would suggest increasing the size of your temp partition. You should have at least twice your memory allocated for temporary files when running database applications.
                  Does this apply to the MySQL client box or the server box? In fact, is the error being generated on the MySQL client or the server? It appears to be coming from the client machine, which has 1G of memory. So should /tmp on that box have 2G? The server box has 4G of memory.

                  Note: Based on the message in the error, it strongly suggests that this is being caused by the Javascript syndication bits. Can someone perhaps look at that code and maybe offer a suggestion as to what would eat half a gig of disk space?

                  Incidentally, the filename given in the error message is not a *nix hidden file. So it makes me question whether that's a MySQL generated error.
                  Last edited by Gary Fischman; Thu 30 Jun '05, 3:12am.

                  Comment


                  • #10
                    Hi Gary does seem the error is from your mysql server, and if you read my post and quoted info in post #8 http://www.vbulletin.com/forum/showp...93&postcount=8

                    when tmpdir variable in mysql isn't set it defaults to using server's /tmp directory

                    When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files. The maximum disk space required is determined by the following expression:

                    (length of what is sorted + sizeof(row pointer))
                    * number of matched rows
                    * 2

                    The row pointer size is usually four bytes, but may grow in the future for really big tables.
                    if you check the error message you posted above it's an ORDER BY query that it choked on so pretty sure it's mysql related tmp message.

                    May i ask how large your thread, post and forum tables are ?

                    As to size of /tmp it should be 2 to 3x times the size of the largest vB or mysql database table you have, so if thread table happen to be 1GB in size, you'd want 2-3GB free space on /tmp if post table is largest and say 2GB in size you'd want 4-6GB of free space

                    Or you can set a tmpdir setting in my.cnf and point it to else where and tell mysql to use that directory for tmp files i.e. tmpdir = /home/mysqltmp
                    Last edited by George L; Thu 30 Jun '05, 4:45am.
                    :: 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


                    • #11
                      Originally posted by eva2000
                      when tmpdir variable in mysql isn't set it defaults to using server's /tmp directory
                      Yep - caught that just after I finished my post.
                      May i ask how large your thread, post and forum tables are ?
                      Thread: 140k rows, 25 Meg
                      Post: 1.33 million rows, 685 Megs
                      Forum: 54 rows, 104k
                      As to size of /tmp it should be 2 to 3x times the size of the largest vB or mysql database table you have, so if thread table happen to be 1GB in size, you'd want 2-3GB free space on /tmp if post table is largest and say 2GB in size you'd want 4-6GB of free space

                      Or you can set a tmpdir setting in my.cnf and point it to else where and tell mysql to use that directory for tmp files i.e. tmpdir = /home/mysqltmp
                      So my largest table is actually the postindex table and its about 750M. So I should have around 2.5 - 3GB?

                      And that's on the Database server box, right? Not the client machine?

                      Comment


                      • #12
                        yup 3-4GB would be minimum i'd add the sized up for post, thread, postindex, and forum tables and times that by 3x

                        in /etc/my.cnf on mysql server you can add under [mysqld] group the line

                        tmpdir = /home/tmp

                        or somewhere where you have free disk space in abundance

                        note, you can also get error 28 low free disk space when a file reaches max file size limit of your OS i.e. 2GB or 4GB file size limits if you have such a limit

                        you can check by typing

                        create file sized 2100MB or 4200MB to test file system limit

                        dd if=/dev/zero of=bigfile bs=2100 count=1048576

                        or

                        dd if=/dev/zero of=bigfile bs=4200 count=1048576
                        :: 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


                        • #13
                          Thanks - I just bumped my /tmp space up on the server. That should do the trick.

                          Comment


                          • #14
                            Ok let us know how it goes
                            :: 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


                            • #15
                              I'm at 2.5 Gig of /tmp space, and its still happening on occasion. If I look at /tmp immediately after getting notified of the error, the volume usage is back down to 2%, and there's no evidence that the problem ever occurred. Recovery is usually within 60 seconds. But during that 60 seconds, there may be over 100 of the SQL errors generated. (And presumably, every user who got the error probably timed out.)

                              The thing that's bothering me is that the error message always contains a reference to the syndication script. (http://forum.miata.net/vb/external.php?type=js)

                              I still suspect that there's something wrong in the syndication script. What is that script doing? No one has answered that in this thread.

                              Comment

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