Not Getting "Packet Too Large" Error With Huge Packets

My situation is pretty straight forward.

I have a server configured with max_allowed_packet set to 16M. It’s running Percona Server 5.1.52.

I wrote a simple script in perl to do huge bulk inserts. I know roughly the size the packets are going to be by knowing how big the data string I’m sending through DBI is.

No matter what size the bulk insert MySQL seems to accept the packet and do the insert, but I’m expecting it to give me a Packet Too Large error for anything over 16M.

Here’s where it gets really weird…

If I set the max_allowed_packet to 16777215 (one byte less that 16M) or anything lower I get the error for packets over that size and obviously don’t get the error for packets under that size.

So, it appears as if at anything below 16M the packet limit is obeyed, but 16M or greater and it’s completely ignored.

Any thoughts as to what could be causing this? It’s really bizarre and the opposite problem most people have with max_allowed_packet.

Is it possible that the mysql client could be doing some auto-chunking? The server only appears to be running one big query, so auto-chunking seems really unlikely since it’d probably show up as more than one insert.

Any variables I could check to get more information about what’s going on?

mattwood wrote on Fri, 10 December 2010 04:52

Your problem sounds very strange.
The only explanation I can think of is:
Are you sure that the data is actually reaching the server when the packet size is over 16M?
The max_allowed_packet is set both for the server and for the client.
The default for the server is 1MB which pretty often needs to be increased, while the default for the client is just 16MB.
So my thought is that you are only checking for error messages from the server which at 16MB-1 byte will still occur since the server will complain, while at 16MB+ the client lib will instead drop the data and you don’t get an error from the server.

Or something like that scenario.

Basically you need to be aware that you have to change both on the server and on the client side.

It’s really starting to feel like I’m dealing with a bug in 5.1.52.

I’ve got two servers set up that are configured nearly identically. One is running 5.1.51 and the other is running 5.1.52. One gives me the “Packet too large” error when it should, and the other doesn’t.

Here’s how to reproduce:

First you need a large packet, it doesn’t need to be anything valid since it doesn’t matter if MySQL actually evaluates it. We expect it to reject it outright anyway. I used this perl script to build a 17mb file.

#!/usr/bin/perlopen OUTFILE, ‘>’, ‘big_packet.sql’ or die $!;print OUTFILE (‘1’ x 17825792);close OUTFILE;

Configure your server to have a max_allowed_packet of 16M.

Then just send it over:

mysql -u root -p < big_packet.sql

You’ll have to successfully authenticate for MySQL to look at the packet.

On 5.1.51 I get this response:

ERROR 1153 (08S01) at line 1: Got a packet bigger than ‘max_allowed_packet’ bytes

On 5.1.52 I get this response:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘11111111111111111111111111111111111111111111111111111111111111111111111111111111’ at line 1

On 5.1.52 MySQL seems to be evaluating it, when I think it should just be rejecting it.

This is just my dumb test case, when I replace the text file filled with 1s with a huge bulk insert, 5.1.52 accepts and inserts the rows but 5.1.51 does not.

I experimented with different values of max_allowed_packet in the mysql client, but they didn’t seem to have any effect.

Am I missing something big here? Or should I report a MySQL bug?

I decided to go ahead and report it as a bug, and it was verified this morning.

So, I suppose that’s that for now.