Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number


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



Replies are listed 'Best First'.
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.


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



Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://366492]
Approved by neniro
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (5)
As of 2018-03-24 23:59 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (299 votes). Check out past polls.