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
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
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
max_allowed_packet = 256M