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);
[download]

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 Handling huge BLOB fields with DBI and MySQL for a sample program that checks max_allowed_packet to do something practical.

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

Thanks gmax, that was very useful.


js1.

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).

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]
[download]