MYSQL Error: Disk is full writing '/tmp/STmGlP0P'

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Swakoo
    Member
    • Jan 2006
    • 33

    MYSQL Error: Disk is full writing '/tmp/STmGlP0P'

    hi guys,

    my database server for vbb recently crashed and i got the following problems... from the mysql log


    060212 5:25:20 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STmGlP0P' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs



    060212 15:04:06 [Note] /usr/sbin/mysqld: Normal shutdown

    060212 15:04:07 [ERROR] /usr/sbin/mysqld: Forcing close of thread 20469029 user: 'root'

    060212 15:04:22 [Warning] Warning: Optimize table got errno 28, retrying
    060212 15:04:24 [Note] /usr/sbin/mysqld: Shutdown complete

    060212 15:04:24 mysqld ended

    060212 15:07:30 mysqld started
    /usr/sbin/mysqld: ready for connections.
    Version: '4.1.12-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)


    060212 16:28:13 [ERROR] /usr/sbin/mysqld: Can't open file: 'postindex.MYI' (errno: 144)

    I took snippets of the log, the rest were repeated. i did a search on this forum and found some links to repair but to no avail.. mainly because my /tmp is only 1GB and my biggest table is about 900MB (postindex, word etc)

    While we have restored to our last known best backup, i fear to see this happen again, especially when..

    1) We don't know what caused it. MYSQL just couldn't work that fateful morning (server was still working fine, just the service)

    2) We can't fix it...


    I also come across a fixoptions.php posted on this forum, but it didnt work.. i suppose its because we are still running 3.0.3?

    We didn't backup as our forums are pretty huge, and since its working (till now) - we have no intention to fix it


    and now that we uploaded it, one big problem we faced are our avatars.. its all distorted!

    any idea?
  • George L
    Former vBulletin Support
    • May 2000
    • 32996
    • 3.8.x

    #2
    nothing to do with your database you should not need to restore a backup...for now close forums to prevent any corruption

    basically /tmp is out of space you need 3x times free space as your largest mysql database table at least

    find a larger partition via this command in ssh telnet

    df -h

    usually /home is pretty big and create new directory called mysqltemp at /home/mysqltemp and chmod it 0777

    and edit /etc/my.cnf under [mysqld] group add

    tmpdir = /home/mysqltemp

    then restart mysql server and you're new tmpdir for mysql will be at /home/mysqltemp
    :: 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

    • Swakoo
      Member
      • Jan 2006
      • 33

      #3
      Hi Guys again...

      I did some more search on the avatar issue and saw the following links





      Can Steve, or someone, please advise... do I need to run the mentioned commands everytime I do a backup, or is it just a once-off issue? I run my backups everyday, and now I am facing a big pow-wow from members asking abt their avatars....


      Am using vbb 3.0.3 and mysql 4.1.12

      Many thanks

      Comment

      • Swakoo
        Member
        • Jan 2006
        • 33

        #4
        Originally posted by eva2000
        nothing to do with your database you should not need to restore a backup...for now close forums to prevent any corruption

        basically /tmp is out of space you need 3x times free space as your largest mysql database table at least

        find a larger partition via this command in ssh telnet

        df -h

        usually /home is pretty big and create new directory called mysqltemp at /home/mysqltemp and chmod it 0777

        and edit /etc/my.cnf under [mysqld] group add

        tmpdir = /home/mysqltemp

        then restart mysql server and you're new tmpdir for mysql will be at /home/mysqltemp
        Ah shucks, i have already restored.... the damage has been done
        But at least now i know

        my db is 4.3GB+, is that a cause for concern, and thats why i had this problem?
        is postindex and word table essential, or can i clear it from time to time?

        Comment

        • George L
          Former vBulletin Support
          • May 2000
          • 32996
          • 3.8.x

          #5
          in ssh telnet

          df -h

          should tell you where /tmp is and it's size

          just do the above suggestion by me and you should be find and not need messing with postindex/word tables
          :: 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

          • Swakoo
            Member
            • Jan 2006
            • 33

            #6
            hi eva2000,

            i don't see the field /tmpdir=""; is there suppose to be an original value, or am i suppose to add it in?

            and what do you mean by edit /etc/my.cnf under [mysqld] group add
            Not using 'root'?

            Lastly, regarding my image corrupted issue... any guide? should I always run the sql ALTER statement prior to backup, or is it just a run-once-and-forget issue? is there any way to run it from cron, if say i need to run it everytime...?

            I see that "attachment" tables also needs to change datatype, why so..? so now that i have uploaded my previous known good database... what can i expect to be abnormal, apart from avatars?


            All these headaches started after my mysql service is not working properly - forums can't be served (the server is fine though, other apps.. can ping etc)


            With such a huge database, could it have caused the crashed? I can't find any error message so am wondering if you guys got a clue of some sort?

            Many thanks

            Comment

            • George L
              Former vBulletin Support
              • May 2000
              • 32996
              • 3.8.x

              #7
              the corrupt avatar issue - it's best to upgrade to vB 3.0.12 and mysql 4.1.18 prior to doing any backups via mysqldump unfortunately corrupted images can't be repaired

              as to tmpdir you are meant to add it in yourself i.e.

              [mysqld]
              tmpdir = /home/mysqltmp
              safe-show-database
              skip-innodb
              back_log = 100
              max_connections = 650
              key_buffer = 96M
              myisam_sort_buffer_size = 128M
              join_buffer_size = 2M
              read_buffer_size = 2M
              sort_buffer_size = 4M
              table_cache = 1500
              thread_cache_size = 384
              wait_timeout = 120
              connect_timeout = 10
              tmp_table_size = 256M
              max_heap_table_size = 80M
              max_allowed_packet = 64M
              max_connect_errors = 10
              read_rnd_buffer_size = 524288
              bulk_insert_buffer_size = 16M
              thread_concurrency = 4
              query_cache_limit = 4M
              query_cache_size = 80M
              query_cache_type = 1
              query_prealloc_size = 65536
              query_alloc_block_size = 32768

              [mysqld_safe]
              open_files_limit = 8192

              [mysqldump]
              quick
              max_allowed_packet = 16M

              [myisamchk]
              key_buffer = 128M
              sort_buffer = 128M
              read_buffer = 64M
              write_buffer = 64M
              crash probably happened due to /tmp filling up
              :: 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

              • Swakoo
                Member
                • Jan 2006
                • 33

                #8
                under what circumstances, without user (me) intervention, will mysqld start using the temp folder? i read the log, it seems to be "repairing" the tables, but i didn't command it to do so.

                i'm already running 3.0.3

                Comment

                • George L
                  Former vBulletin Support
                  • May 2000
                  • 32996
                  • 3.8.x

                  #9
                  read http://dev.mysql.com/doc/refman/5.0/...ary-files.html
                  :: 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

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