I need to know some ball park number for this variable for my drupal 7 site. I recently got a "drupal "PDOException: SQLSTATE[08S01]: Communication link failure: 1153" error. I am a newbie to mysql and am using shared hosting. I probably need to ask the administrator to do it for me.
Thanks in advance.
I saw an interesting answer to a question about the biggest BLOB you may have. Here is the statement I saw in ServerFault : innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones.
Based on that ServerFault post from Nils-Anders Nøttseter, you should query the table and find out which BLOB is the biggest, multiply that number by 11 or more, and use that answer as the max_allowed_packet going forward.
It's funny that I addressed another question where I suggested sizing the max_allowed packet to hopefully solve the issue.
According to the Book
Here is what Page 99 Paragraphs 1-3 says about MySQL Packets:
MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.
The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().
This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.
Knowing this about MySQL Packets allows a DBA to size them up to accommodate multiple BLOBs inside one MySQL Packet, even if they are obnoxiously large.
Regarding your situation, you should find out what is the biggest BLOB in your database, multiple that number by 11 and set your max_allowed_packet to that number. You should be able to set it for the server without a mysql restart (Personally, I would set it 256M because it would address other problems regarding migration and replication, which is beyond the scope of this forum). To set it to 256M for your database for all incoming connections, please run this:
SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;
Afterwards, add this setting to my.cnf under the [mysqld] section:
[mysqld]
max_allowed_packet = 256M
Taken from Drupal's Database Server Requirements page:
It may be necessary to set the system variable max_allowed_packet to at least 16M. Some inexpensive hosting plans set this value too low (the MySQL default is only 1M). In that case, you may need to choose a better hosting plan. A value of 1M may be sufficient for Drupal 5.
Should be fine for a site on shared hosting.
It can be a little tricky to set max_allowed_packet. I have yet to come across a method to calculate out that value. Typically, I run into it when trying to restore a database snapshot that includes rows from the {cache} tables.
When I have this problem, I will pick a value between 4M and 8M. I keep bumping up the value until it stops. However, I don't know if a shared host will change this value for you.
