Support Home | Knowledge Base Home | Submit Article | Search Articles | Browse Articles  Sessions table is full  Description: How to fix the dreaded 'phpbb_sessions' is full error. Also has information on HEAP and MyISAM tables, sessions and why the error occurs in the first place. Author: Black Fluffy Lion Date: Thu Jan 16, 2003 7:14 pm Type: Fix Keywords: sessions, table, full, empty, myisam, heap, phpbb_sessions, delete, floodings, session_id Category: Miscellanea Contents The problem The error message Why it occurs HEAP tables Why the table is full Sessions table flooding The fix Which fix to use Automatic table emptying script Emptying the table manually... ...using phpMyAdmin ...using command line Limiting the number of sessions per IP Increasing the table's MAX_ROWS... ...using phpMyAdmin ...using command line Making the table's type MyISAM... ...using phpMyAdmin ...using command line The problem The error message First of all, before trying to fix it, let's have a look at the error itself: Quote: Error creating new session DEBUG SQL Error : 1114 The table 'phpbb_sessions' is full INSERT INTO phpbb_sessions (session_id, session_user_id, session_start, session_time, session_ip, session_page, session_logged_in) VALUES ('544c13e2eea831bb6402a4b35f78e96a', -1, 1041341439, 1041341439, '7f000001', 0, 0) What does this mean?? As it says, there has been an error creating a new session. A session is a piece of data which tracks the user as they move around the fora. This is used for tasks such as to see whether the user is logged in (for checking if they have permission to view a certain page), what page they are viewing (for Who is Online) and what their IP is (for the admin panel). The session is stored in two places - the client side and the server side. On the client side, the session is stored either as a cookie on the user's computer, or appended to URLs in the forum (e.g. index.php?sid=session_id). On the server side, the session is stored in the database, in the sessions table (phpbb_sessions by default). This is where the problem arises - the table phpbb_sessions is full. Why it occurs Now, why does this happen in the first place?? 'I'm innocent!', I hear you say, 'I haven't added any MODs or anything, I just woke up one day to see that error message!'. Well, you are innocent. This error is nothing to do with you (or any of your users) breaking the fora, don't worry. As mentioned above, the problem is that the table in your database which stores the sessions is full. But why does this happen?? Read on, all will be explained below... HEAP tables To understand why this error occurs, first you need to understand about how the sessions table is designed. If possible, the sessions table's type is HEAP, instead of the default table type of MyISAM. HEAP tables have the advantage over MyISAM of having all the data stored in memory, not on hard-disk. This makes them faster than MyISAM tables, which store all their data on disk (although both table types' lock is at table level). The only part of a HEAP table which is stored on hard-disk are the column and table definitions, which are stored in a file with a .frm extension. Another important point to note about HEAP tables is that all the data in them will be lost if MySQL crashes or is restarted, unlike MyISAM tables, which will keep all their data. All of these properties make the HEAP table ideal for a table which contains temporary information. The sessions table contains just that - data in the sessions table will only be valid for the amount of time that the session length is set to in the admin panel (the default is 1 hour or 3600 seconds). Losing a row in the sessions table would just lose the user's sessions (thus making them have to login again and the new post markers would be lost), whereas losing a row in phpbb_users, a MyISAM table, would delete a user's account permanently, which is far more serious. Although deleting a row has the same effect on both types of table (the data is irrecoverable), because session data is intended to be temporary, the results of deleting data are far less harmful and serious. In fact, once a session is no longer valid (the session length has been reached since its last update or insertion), it is utterly useless. It cannot be used anymore, since the session is effectively dead. HEAP is a feature which was introduced at MySQL version 3.23.0, so if you're using an earlier version then your sessions table will be MyISAM. Despite all the differences between MyISAM and HEAP, having your sessions table as MyISAM instead of HEAP shouldn't produce any noticeable changes, except perhaps being a bit slower. Related link: MySQL: HEAP tables - documentation on MySQL's HEAP table type. Why the table is full To prevent HEAP tables from eating up all the memory by having too many rows in them, a limit, MAX_ROWS, is placed on the table. This limit specifies a number which is the maximum number of rows that the table can have. The default value of MAX_ROWS varies from host to host, but it's often 550. This means that when you try and insert another row into the sessions table when it already has 550 rows there, the table is full error occurs. There is a safe guard to prevent you from accidentally creating HEAP tables which eat up all the memory by forgetting to set MAX_ROWS or by setting to something huge - you cannot create HEAP tables that are bigger than the value of max_heap_table_size, a MySQL variable. In versions prior to 3.23.2, if the size of HEAP table exceeds the size of the variable tmp_table_size, the table is full error can occur. In newer versions of MySQL, HEAP tables are automatically converted to MyISAM when they reach the size of tmp_table_size. Either MAX_ROWS or tmp_table_size can be used to limit the size of a table, but it is usually the size of MAX_ROWS which will cause the error. By default, phpBB does not have a system to automatically empty the sessions table when it gets full, though one may be in the next major release, 2.2. Related link: MySQL: The table is full Error - all the possible reasons for receiving a 'table is full' error. Sessions table flooding Because the sessions table isn't automatically emptied, issues can occur with 'session flooding'. When a user views the index page, a session_id is created, and a new row is inserted into the sessions table. Then the user clicks on a link (to the memberlist, or to view a forum or whatever) and everything is dandy - the user's session is the same as the one which was created on the index page so no extra row is inserted. But then, the user decides to open up a new index.php, without using their existing session, thus creating a new row in the database. This is easy to do if the user's browser is not set to accept cookies, because the session_id will be passed with the URL (e.g. index.php?sid=session_id). By deleting the sid which is appended to the URL, the user makes their session row in the database old and no longer valid, but it still exists, filling up the table. The user can then just repeat this process, filling up the sessions table every time a new session is created, yet not deleting the old session. By doing this, the limit set by MAX_ROWS can soon be reached, bringing on the table is full error. Note that just because your sessions table is full it doesn't mean that someone has actively and maliciously trying to fill it. Chances are that it is just full because of all the users that visit your site, and that no-one has been trying to fill it up in an attempt to bring your site down. Sometimes it can just be a confused search engine spider, who doesn't like dynamic session ids, filling up the sessions table. The fix Which fix to use Warning: Before reading any of the fixes below, please read this section which will tell you which fix to use depending on the circumstances. Also, before proceeding, please backup your database (or at least the sessions table) and your phpBB files (or at least includes/sessions.php)! There are various ways to fix the problems spoken about above, each is suited to a different situation. The first thing you should try is the automatic table emptying script. If that doesn't work, then try emptying the table manually. If, after all that, the sessions table fills too quickly, try limiting the number of sessions per IP or increasing the table's MAX_ROWS. If all else fails, then try making the table's type MyISAM. Automatic table emptying script This fix is the first one you should try and nearly always works. Thanks to Ashe for writing it and morpheus2matrix for providing it. Open includes/sessions.php using a text/code editor such as Wordpad, and find the following code (line 152). Code: message_die(CRITICAL_ERROR, 'Error creating new session', '', __LINE__, __FILE__, $sql); Replace this with the following code. Code: $error = TRUE; if (SQL_LAYER == "mysql" || SQL_LAYER == "mysql4") {     $sql_error = $db->sql_error($result);     if ($sql_error["code"] == 1114)     {         $result = $db->sql_query('SHOW TABLE STATUS LIKE "'.SESSIONS_TABLE.'"');         $row = $db->sql_fetchrow($result);         if ($row["Type"] == "HEAP" || $row["Engine"] == "MEMORY")         {             if ($row["Rows"] > 2500)             {                 $delete_order = (SQL_LAYER=="mysql4") ? " ORDER BY session_time ASC" : "";                 $db->sql_query("DELETE QUICK FROM ".SESSIONS_TABLE."$delete_order LIMIT 50");             }             else             {                 $db->sql_query("ALTER TABLE ".SESSIONS_TABLE." MAX_ROWS=".($row["Rows"]+50));             }             if ($db->sql_query($sql))             {                 $error = FALSE;             }         }     } } if ($error) {     message_die(CRITICAL_ERROR, "Error creating new session", "", __LINE__, __FILE__, $sql); } This script will delete the oldest 50 rows (no longer valid sessions) from the sessions table if there are more than 2500 rows in the table currently. If there are less than 2500 rows in the table, then 50 is added to the table's MAX_ROWS (see Why the table is full for more on MAX_ROWS). This basically means that the table's capacity gets bigger and bigger until the maximum amount of rows hits 2500. From then on, instead of increasing the table's capacity, the script deletes the oldest 50 (and no longer needed) sessions. Emptying the table manually... Doing this will delete all the rows in your sessions table. It should fix the problem, but you'll need to do it every time the table gets full, which is why the automatic script talked about above is preferable. This fix requires a database administration tool such as phpMyAdmin, or command line access to be available. Remember to backup your database first! ...using phpMyAdmin Using phpMyAdmin, login and click your username in the top of the left hand frame or select your database there. In the main frame, make sure you're in the Structure tab. Then scroll down to your sessions table, phpbb_sessions by default. To the right of it, you should see some links. Click on the rightmost one, Empty. A javascript box should popup, saying: Quote: Do you really want to : DELETE FROM `phpbb_sessions` Verify that it does actually say that, and that you aren't emptying the wrong table (if it is wrong then click 'Cancel' and then you'll get a chance to empty the right table). If it is right, then click OK to empty the sessions table. All done. ...using command line To execute some SQL to empty the table, use the following query: Code: DELETE FROM phpbb_sessions; Make sure you change the phpbb_ part of phpbb_sessions if you specified a different table prefix during installation. Limiting the number of sessions per IP This fix should only be used if you are experiencing problems with too many sessions being created for one user (see Sessions table flooding). Thanks to R45 for writing it. Open includes/sessions.php using a text/code editor such as Wordpad, and find the code (around lines 136-145): Code:     //     // Create or update the session     //     $sql = "UPDATE " . SESSIONS_TABLE . "         SET session_user_id = $user_id, session_start = $current_time, session_time = $current_time, session_page = $page_id, session_logged_in = $login         WHERE session_id = '" . $session_id . "'             AND session_ip = '$user_ip'";     if ( !$db->sql_query($sql) || !$db->sql_affectedrows() )     {         $session_id = md5(uniqid($user_ip)); After the above (but making sure you don't delete the above code), add: Code:         $sql = "SELECT COUNT(*) as numrows FROM ". SESSIONS_TABLE ." WHERE session_ip = '$user_ip'";         $result = $db->sql_query($sql);         if ( !$result )         {             message_die(CRITICAL_ERROR, 'Error checking existing sessions', '', __LINE__, __FILE__, $sql);         }                 $numrows = $db->sql_fetchrow($result);         if($numrows['numrows'] > 4)         {             $delete_limit = $numrows['numrows'] - 4;             switch( SQL_LAYER )             {                 case 'mysql4':                     $sql = "DELETE FROM ". SESSIONS_TABLE ." WHERE session_ip = '$user_ip' ORDER BY session_start ASC LIMIT $delete_limit";                     break;                 default:                     $sql = "SELECT session_start FROM ". SESSIONS_TABLE ." WHERE session_ip = '$user_ip' ORDER BY session_start DESC LIMIT 4";                     $result = $db->sql_query($sql);                                         if ( !$db->sql_query($sql) )                     {                         message_die(CRITICAL_ERROR, 'Error select session data', '', __LINE__, __FILE__, $sql);                     }                                         $session_rows = $db->sql_fetchrowset($result);                                         $sql = "DELETE FROM ". SESSIONS_TABLE ." WHERE session_ip = '$user_ip' AND session_start < ".$session_rows[3]['session_start'];             }                                 if ( !$db->sql_query($sql) )             {                 message_die(CRITICAL_ERROR, 'Error deleting old sessions', '', __LINE__, __FILE__, $sql);             }         } This code will see how many sessions already exist for the user. If it finds five or more, then the oldest session (or sessions, depending on the version of MySQL and how many sessions already exist) belonging to that user's IP are deleted, to make room for the new session. To change the check to allow a different number of sessions per IP, change the highlighted part of this line: Quote: if($numrows['numrows'] > 4) For example, to delete the sessions only if the user already has more than ten sessions, the following code would be used: Quote: if($numrows['numrows'] > 10) Note that limiting sessions on a per IP basis is not suitable if you have many users behind a proxy. If you had twenty people behind one proxy gateway accessing the fora at the same time, then the total number of people acccessing the fora from behind that proxy would be limited, not the amount of connections one user creates on the fora. There is code that limits the amount of sessions in a 60 second period already present in phpBB 2.2, the next major release of phpBB, but no anti-session flooding code will be added to phpBB 2.0.x. Increasing the table's MAX_ROWS... Increasing the table's MAX_ROWS isn't a real solution as much as it is a workaround. It increases the session table's capacity (see Why the table is full for more on MAX_ROWS), so that the table has more space to insert rows into. ...using phpMyAdmin Using phpMyAdmin, login and click your username in the top of the left hand frame or select your database there. In the main frame, click on the SQL tab at the top. In the box entitled 'Run SQL query/queries on database', enter: Quote: ALTER TABLE phpbb_sessions MAX_ROWS = 2500; Edit the 2500 to the number of rows you wish the table to have. The MySQL variable max_heap_table_size should prevent you from creating a table too big, so as long as you don't go over that then it should be OK. Make sure you change the phpbb_ part of phpbb_sessions if you specified a different table prefix during installation. When you've altered the code to your heart's desire, click the Go button further down the page to execute the query. ...using command line To execute some SQL to increase the MAX_ROWS of the table, use this query: Quote: ALTER TABLE phpbb_sessions MAX_ROWS = 2500; As above, edit the 2500 to the number of rows you wish the table to have. The MySQL variable max_heap_table_size should prevent you from creating a table too big, so as long as you don't go over that then it should be OK. Make sure you change the phpbb_ part of phpbb_sessions if you specified a different table prefix during installation. Making the table's type MyISAM... This is the last thing you should try to do if the table is full, and only when the above fixes have failed. Making the table MyISAM instead of HEAP shouldn't produce any noticable differences, except perhaps be a little slower. A MyISAM table should be a lot harder to fill (2 or 4 GB of space to fill by default), if MAX_ROWS is set to something high enough. See also HEAP tables for more on the differences between MyISAM and HEAP tables. ...using phpMyAdmin Using phpMyAdmin, login and click your username in the top of the left hand frame or select your database there. In the main frame, make sure you're in the Structure tab. In the main frame, scroll down until you see the table phpbb_sessions. Click on the Properties link by it. At the top, select the Options tab. There should be a bullet point saying 'Table type', with a select list next to it with the Heap option selected. Click on the select menu and select MyISAM from the list (it's usually the first and top option). Then click on the Go button. ...using command line To change the sessions table to MyISAM instead of HEAP, use the following SQL: Code: ALTER TABLE phpbb_sessions TYPE = MYISAM; Make sure you change the phpbb_ part of phpbb_sessions if you specified a different table prefix during installation. Last updated 19 January 2006 Username: Password: News | Features | Demo | Downloads | Support | Community | Styles | Mods | Links | Merchandise | About | Home  © Copyright 2002 The phpBB Group.