MySQL Error : The table 'session' is full

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • weitalia
    Member
    • Jul 2004
    • 68
    • 3.0.1

    MySQL Error : The table 'session' is full

    today i have this error on my board, table session was like 16mb, i dumped the table and i truncate it and it seem to be solved. There are some problem in future with this solution? Why it give me session table full?


    MySQL Error : The table 'session' is full
    Error Number : 1114
  • Zachery
    Former vBulletin Support
    • Jul 2002
    • 59097

    #2
    Sounds like your scheudaled tasks are not runing.

    Comment

    • weitalia
      Member
      • Jul 2004
      • 68
      • 3.0.1

      #3
      it is connected to this?

      Comment

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

        #4
        Originally posted by weitalia
        how large is your largest mysql database on the server, and how large is the largest table in any mysql database not just mysql ?

        /tmp default for mysql unless you set tmpdir to another path in my.cnf needs at least 3x times the free space as your largest mysql database/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

        • weitalia
          Member
          • Jul 2004
          • 68
          • 3.0.1

          #5
          my largest mysql database: 600 Mb
          my largest mysql database's table: 150MB

          /tmp dir is 1GB
          i need to ask to increase it?

          Comment

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

            #6
            fine 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

            • weitalia
              Member
              • Jul 2004
              • 68
              • 3.0.1

              #7
              this way resolve problems of custom partition of /tmp dir
              thanks

              Comment

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

                #8
                No probs
                :: 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

                • fastforward
                  Senior Member
                  • Nov 2000
                  • 1013

                  #9
                  The error you are getting has nothing to with the size of /tmp. The session table is a HEAP table. HEAP tables are stored in memory.

                  The default maximum size for HEAP tables is 16MB. Attempting to use more than this will result in the 'table full' error.

                  You just need to increase the max_heap_table_size parameter in your MySQL configuration.
                  http://britishexpats.com/ - British expatriate community

                  Comment

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

                    #10
                    You can try both suggested fixes and see if it helps

                    but max_heap_table_size change won't help unless you recreate or alter or truncate the heap table, as once HEAP tables hit their 16MB limit they SHOULD automatically convert to MYISAM disk based tables and use /tmp unless you set tmpdir in my.cnf







                    max_heap_table_size

                    This variable sets the maximum size to which MEMORY tables are allowed to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE.
                    #

                    MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY tables are not subject to storage conversion, whereas internal tables are:

                    *

                    If an internal table becomes too large, the server automatically converts it to an on-disk table. The size limit is determined by the value of the tmp_table_size system variable.
                    *

                    MEMORY tables are never converted to disk tables. To ensure that you don't accidentally do anything foolish, you can set the max_heap_table_size system variable to impose a maximum size on MEMORY tables. For individual tables, you can also specify a MAX_ROWS table option in the CREATE TABLE statement.
                    what vB version you're using as well as do you have any custom code in the footer template of your forums ? if you do revert your footer template. You made a change somewhere that stopped the Scheduled Tasks from running and clearing our this table on a regular basis
                    Last edited by George L; Mon 13 Feb '06, 3:14am.
                    :: 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

                    • fastforward
                      Senior Member
                      • Nov 2000
                      • 1013

                      #11
                      Originally posted by eva2000
                      Y... as once HEAP tables hit their 16MB limit they SHOULD automatically convert to MYISAM disk based tables and use /tmp unless you set tmpdir in my.cnf
                      Internal tables will be converted (i.e. system created temporary tables). HEAP tables are never converted to disk.
                      Last edited by fastforward; Mon 13 Feb '06, 4:50am.
                      http://britishexpats.com/ - British expatriate community

                      Comment

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

                        #12
                        whops
                        :: 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

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

                          #13
                          anyway sessions should never grow that large to begin with

                          if you have any custom code in the footer template of your forums, you do revert your footer template. As a change in the footer, somewhere possibly stopped the Scheduled Tasks from running and clearing out this table on a regular basis
                          :: 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

                          • Hardwaregeeks
                            Senior Member
                            • Sep 2002
                            • 106

                            #14
                            Originally posted by eva2000
                            anyway sessions should never grow that large to begin with

                            if you have any custom code in the footer template of your forums, you do revert your footer template. As a change in the footer, somewhere possibly stopped the Scheduled Tasks from running and clearing out this table on a regular basis
                            This post helped me fix a problem. Thanks

                            My host had a major boo boo and for 18 days we had the wrong date on the server. Host was AFK and thankfully we are now with a dedicated server that we can manage. but we just had this error and it was because the cron jobs next scheduled run is for the 18th of March. Good thing we fixed the date bug and I don't mind running the cron jobs manually until then.

                            Comment

                            • hapbird
                              Senior Member
                              • Oct 2005
                              • 101
                              • 3.6.x

                              #15
                              we just ran into this same error

                              Is max_heap_table_size the root issue here?


                              Originally posted by weitalia
                              today i have this error on my board, table session was like 16mb, i dumped the table and i truncate it and it seem to be solved. There are some problem in future with this solution? Why it give me session table full?


                              MySQL Error : The table 'session' is full
                              Error Number : 1114
                              Have a nice day
                              http://www.duneram.com

                              Comment

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