MySQL: Got a packet bigger than ‘max_allowed_packet’ bytes Error Tips, Tricks and Tutorials 30 SEP 2010

mysql-girl-black-halter-topI came across a nasty little bug in my image upload to MySQL blob in base 64 encode function the other day – basically the database was spitting back the following error to me:

Error code: 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes

So of course I looked it up.

It turns out that MySQL views a single SQL statement as a communication packet, with a built in limit of 1GB. However, servers usually have a set maximum packet size they are allowed to accept and clients a maximum packet size they are allowed to send, both of which is controlled by a variable called max_allowed_packet. If it receives a packet bigger than this limit, the server will spit back a 1153 error and close the connection.

If you wish to check what your current maximum packet size is, run the following query:

SHOW VARIABLES LIKE ‘max_allowed_packet’

Of course, you would also want to increase these limits at some point in time, something which is easily achieved like so:

To increase a client program’s default, run: shell> mysql –max_allowed_packet=32MB.

To increase a server’s default, run: shell> mysqld –max_allowed_packet=16M.

Note that this is fairly safe to increase this value as memory is allocated only when needed. So now you know! :)

Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.