Max_allowed_packet problem

[Richard Law]

Hi All,

We're trying to migrate a code base from Solaris to Ubuntu and running into an odd max_allowed_packet problem. We have seen those before long ago and adjusted accordingly in my.cnf to the 1G max. We don't see this error on the Solaris box nor in the mysql client. We're running 5.5.41-0ubuntu0.14.04.1 with the latest DBI and DBD::mysql.

The table definition:

CREATE TABLE `ME` (
`url` varchar(255) NOT NULL DEFAULT '',
`dltime` int(11) DEFAULT NULL,
`depth` int(11) DEFAULT NULL,
`content_type` varchar(128) DEFAULT NULL,
`body` mediumtext,
`meta` mediumtext,
`title` varchar(255) DEFAULT NULL,
`linktext` varchar(255) DEFAULT '',
`visited` datetime DEFAULT NULL,
`size` int(10) unsigned DEFAULT NULL,
`descript` text,
`keywords` text,
`docroot` varchar(255) DEFAULT NULL,
`mineid` mediumint(9) NOT NULL DEFAULT '0',
`lodeid` mediumint(9) NOT NULL DEFAULT '0',
PRIMARY KEY (`url`),
KEY `visited` (`visited`),
KEY `linktext` (`linktext`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The test code:
#!/usr/local/bin/perl -w

use DBI;

my $dbh = DBI->connect("dbi:mysql:mill;mysql_read_default_group=mysqld1", "xxxxxxx", "xxxxxxxx") ||
die "Could not connect to mysql";

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

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

$bsel->execute();
($body) = $bsel->fetchrow_array;
print "$body\n";
$xx++;
}

$dbh->disconnect();
exit(0);

Group mysqld1 from my.cnf:

[mysqld1]
pid-file = /var/run/mysqld/mysqld.pid
socket = /run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer_size = 4G
max_allowed_packet = 1G
net_buffer_length = 1M
thread_stack = 192K
thread_cache_size = 16
myisam-recover = BACKUP
myisam_sort_buffer_size = 16M
max_sort_length = 32M
max_connections = 160
table_cache = 512
thread_concurrency = 16

And, finally, the beginning of the output we get:

law@chinacat> testmaxpack
max_allowed_packet: 1073741824
The record I'm about to get is 7991 characters.
DBD::mysql::st execute failed: Got packet bigger than 'max_allowed_packet' bytes at ./testmaxpack line 18.
DBD::mysql::st fetchrow_array failed: fetch() without execute() at ./testmaxpack line 19.
Use of uninitialized value $body in concatenation (.) or string at ./testmaxpack line 20.

The record I'm about to get is 8024 characters.
DBD::mysql::st execute failed: MySQL server has gone away at ./testmaxpack line 18.
DBD::mysql::st fetchrow_array failed: fetch() without execute() at ./testmaxpack line 19.
Use of uninitialized value $body in concatenation (.) or string at ./testmaxpack line 20.


We're baffled by the small record length and the error. Does anyone have ideas on how to figure this one out? Of course, we're under pressure to fix this asap.

Thanks for any suggestions!