Mysql server gone away : forum search

小熊笨笨

Could not delete old search id sessions

DEBUG MODE

SQL Error : 1153 Got a packet bigger than 'max_allowed_packet'

DELETE FROM immidb_search_results WHERE session_id NOT IN ('6f4f47328c7666e26adedd9312b5ddcf' ...

I noticed that whenever the phpbb sessions tables was reaching 30,000 records, the above error was thrown.

Turns out, all those records were being checked against the search_result table for phpbb. This made a query sent to mysql that was very long, over 1MB to be precise.

Why does this matter?

By default, there is a query limit restriction set to 1MB in mysql.
I quote the solution from mysql:

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld gets a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by starting mysqld with the -O max_allowed_packet=# option (default 1M). The extra memory is allocated on demand, so mysqld will allocate more memory only when you issue a big query or when mysqld must return a big result row!  
Src: http://www.mysql.com/doc/en/Gone_away.html

I have asked the hostgator increased MYSQL's default max packet size beyond 1M .  they refused to do that for security reasons, so that's out .

Right now I'm having to empty the sessions table every 4 days. I really would appreciate some help.

小熊笨笨

Automatic sessions table emptying script

Open includes/sessions.php using a text/code editor such as Wordpad, and find the following code (line 152).

1. message_die(CRITICAL_ERROR, 'Error creating new session', '', __LINE__, __FILE__, $sql);

复制代码

Replace this with the following code.

1. $error = TRUE;
   
2. if (SQL_LAYER == "mysql" || SQL_LAYER == "mysql4")
   
3. {
   
4.     $sql_error = $db->sql_error($result);
   
5.     if ($sql_error["code"] == 1114)
   
6.     {
   
7.         $result = $db->sql_query('SHOW TABLE STATUS LIKE "'.SESSIONS_TABLE.'"');
   
8.         $row = $db->sql_fetchrow($result);
   
9.         if ($row["Type"] == "HEAP")
   
10.         {
    
11.             if ($row["Rows"] > 2500)
    
12.             {
    
13.                 $delete_order = (SQL_LAYER=="mysql4") ? " ORDER BY session_time ASC" : "";
    
14.                 $db->sql_query("DELETE QUICK FROM ".SESSIONS_TABLE."$delete_order LIMIT 50");
    
15.             }
    
16.             else
    
17.             {
    
18.                 $db->sql_query("ALTER TABLE ".SESSIONS_TABLE." MAX_ROWS=".($row["Rows"]+50));
    
19.             }
    
20.             if ($db->sql_query($sql))
    
21.             {
    
22.                 $error = FALSE;
    
23.             }
    
24.         }
    
25.     }
    
26. }
    
27. if ($error)
    
28. {
    
29.     message_die(CRITICAL_ERROR, "Error creating new session", "", __LINE__, __FILE__, $sql);
    
30. }

复制代码

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.

LittlePrince

already patched?