Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

max_allowed_packet

by js1 (Monk)
on Jun 14, 2004 at 10:57 UTC ( #366492=perlquestion: print w/ replies, xml ) Need Help??
js1 has asked for the wisdom of the Perl Monks concerning the following question:

Greetings fellow Monks,

Can anyone enlighten me about checking mysql's max_allowed_packet size in a perl script please? I'd like to get the value of max_allowed_packet and check the size of the SQL statement before I excecute it (using DBI).

Thanks,

js1.

Comment on max_allowed_packet
Re: max_allowed_packet
by BrowserUk (Pope) on Jun 14, 2004 at 11:03 UTC
    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).


    Examine what is said, not who speaks.
    "Efficiency is intelligent laziness." -David Dunham
    "Think for yourself!" - Abigail
Re: max_allowed_packet
by gmax (Abbot) on Jun 14, 2004 at 11:37 UTC

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

Re: max_allowed_packet
by tachyon (Chancellor) on Jun 15, 2004 at 08:40 UTC

    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]

    cheers

    tachyon

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://366492]
Approved by neniro
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (6)
As of 2014-07-26 13:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (177 votes), past polls