A.2.9 Packet too large Error

When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection.

In MySQL 3.23 the biggest possible packet is 16M (due to limits in the client/server protocol). In MySQL 4.0.1 and up, this is only limited by the amount on memory you have on your server (up to a theoretical maximum of 2G).

Here is a simple recipe to get the value of "max_allowed_packet".

my $dbh = DBI->connect('dbi:mysql:dbname', 'user', 'pwd')
   or die "$DBI::errstr\n";
my (undef, $max_allowed_packet) =  $dbh->selectrow_array(
                                qq{show variables LIKE ? },
                                undef,
                                "max_allowed_packet" ) ;

printf "max_allowed_packet => %.2f MB\n", $max_allowed_packet  / (1024*1024);

Sample output:
max_allowed_packet => 3.00 MB

SHOW VARIABLES returns a 2-column dataset, with a label in the first column and a value in the second one. Thus you need to get the second column with the value you need.

Check [DBI Recipes] for more on idioms, and [id://150255] for a sample program that checks max_allowed_packet to do something practical.

 _  _ _  _  
(_|| | |(_|><
 _|   

Re^2: max_allowed_packet

js1

created: 2004-06-14 15:13:33

perlmonks

• reply
• node
• thread
• author

• node
• author
• thread

prlmnks

• node
• thread
• author

Thanks gmax, that was very useful.

js1.

Checking available packet size every time will be slow. Perhaps a better option (memory permitting) is to give yourself some extra room. To increase it just set it in your config:

[root@devel3 root]# cat /etc/my.cnf
[client]
socket=/tmp/mysql.sock

[mysqld]
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
set-variable=wait_timeout=3600
set-variable=key_buffer=640M
set-variable=max_allowed_packet=32M
[snip]