Re: Max_allowed_packet problem

[Richard Law]

Hi Rick,

Thanks for your reply. Somehow I missed it.

We temporarily worked around the error by moving the problematic tables to a Solaris MySQL server and adjusting our connections. While I love Solaris, this system is designed to run on any *nix and now that the crisis is over, I would like to find out why the error is occurring on Ubuntu and not Solaris.

Your reply was spot on based on the code I posted. The table in question is only 88M, although we do have similar tables as much as several GB. I subsequently restricted the prepared statement and tried your suggestion to no avail.

Here's my latest test code and output:

#!/usr/local/bin/perl -w

use DBI;

my $dbh = DBI->connect("dbi:mysql:mill;mysql_read_default_group=mysqld1", "****", "*********") ||
die "Could not connect to mysql";
$dbh->{mysql_auto_reconnect} = 1;
my $dbh2 = DBI->connect("dbi:mysql:mill;mysql_read_default_group=mysqld1", "...", "...") ||
die "Second connection failed.";
$dbh2->{mysql_auto_reconnect} = 1;

($var, $max) = $dbh->selectrow_array("show variables like 'max_allowed_packet'")
;
print "$var: $max\n";

$ssel = $dbh->prepare("select url, length(body) from ore.ME",
{ mysql_use_result => 1 });
$ssel->execute();
$xx = 0;
while ($xx < 10 and ($url, $size) = $ssel->fetchrow_array()) {
print "The record I'm about to get is $size characters.\n";

$bsel = $dbh2->prepare("select body from ore.ME where url=?",
{ mysql_use_result => 1 });
$bsel->execute($url) or die "execute() FAILED! " . $bsel->errstr;
($body) = $bsel->fetchrow_array;
if ($body) { print "Got " . length($body) . " characters.\n"; }
else { print "Got NOTHING for $url.\n"; }
$bsel->finish();
$xx++;
}

$ssel->finish();
$dbh->disconnect();
$dbh2->disconnect();
exit(0);

-=-=-=-

law@chinacat> testmaxpack
max_allowed_packet: 1073741824
The record I'm about to get is 7991 characters.
Got 7991 characters.
The record I'm about to get is 8024 characters.
Got 8024 characters.
The record I'm about to get is 8454 characters.
DBD::mysql::st fetchrow_array failed: Got packet bigger than 'max_allowed_packet' bytes at ./testmaxpack line 25.
Got NOTHING for http://www.mainelegislature.org/LawMakerWeb/summary.asp?ID=280054253.
The record I'm about to get is 9610 characters.
DBD::mysql::st fetchrow_array failed: Got packet bigger than 'max_allowed_packet' bytes at ./testmaxpack line 25.
Got NOTHING for http://www.mainelegislature.org/LawMakerWeb/dockets.asp?ID=280054253.
The record I'm about to get is 8342 characters.
DBD::mysql::st fetchrow_array failed: Got packet bigger than 'max_allowed_packet' bytes at ./testmaxpack line 25.
Got NOTHING for http://www.mainelegislature.org/LawMakerWeb/summary.asp?ID=280054008.
The record I'm about to get is 11866 characters.
DBD::mysql::st fetchrow_array failed: Got packet bigger than 'max_allowed_packet' bytes at ./testmaxpack line 25.
Got NOTHING for http://www.mainelegislature.org/LawMakerWeb/dockets.asp?ID=280054008.
The record I'm about to get is 8039 characters.
Got 8039 characters.
The record I'm about to get is 8069 characters.
Got 8069 characters.
The record I'm about to get is 8024 characters.
Got 8024 characters.
The record I'm about to get is 8060 characters.
Got 8060 characters.
law@chinacat>

-=-=-=-

I find the error perplexing given what should be small, one column response packets. The fact that mysql client doesn't generate this error nor does the Solaris instance leads me to believe it's in the DBI or DBD::mysql perl modules.

Any and all suggestions are welcomed. If further info or a table dump would help, let me know.

Thanks!

Richard