Packet for query is too large MySQL

Question

I have a tomcat app connecting to a MySQL Db / java application

I keep getting

Packet for query is too large 1080>1024

I tried changing my.cnf: in my.cnf the Max packet size is defined as 50 MB and

socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir     = /usr
datadir = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
lower_case_table_names  = 1
skip-external-locking



bind-address = 0.0.0.0



key_buffer      = 16M
max_allowed_packet = 50M
thread_stack        = 192K
thread_cache_size       = 8
group_concat_max_len=100000
innodb_lock_wait_timeout=300
innodb_buffer_pool_size=22G
innodb_locks_unsafe_for_binlog = ON
innodb_additional_mem_pool_size=40M

I have even tried passing the Param as part of the connection string

jdbc:mysql://serverIP:3306/dbname?maxAllowedPacket=2048000

to the jdbc driver - still I keep getting

Packet for query is too large 1080>1024

This keeps coming every few hours.

What should I check?

MySQL version is 5.5

Thanks for the help.

Answer 1

It looks like something isn't configured right... Did you restart MySQL after making the config changes? You have to configure the size on both ends:

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

However, the server's default size is 1mb (1024 kb). Given your error says 1080>1024 I'm guessing your configuration changes didn't take place (at least not on both client and server).

This happens because either a query, row or a binary log event are greater than 1mb:

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

I'd try bumping to 5mb on the client and the server (including your replication instance if you have one). You'll have to bounce MySQL for changes to take effect.

https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

Answer 2

For those who just want to temparery increase the size of max_allowed_packet and don't want to make this change permanent, try execute sql:

use your_db;
set global max_allowed_packet = 1024*1024*10; # set size to 10M  

to verify whether it takes effect or not, you need to open a new query session and execute:

show VARIABLES like '%max_allowed_packet%';

Please note this change is temparery and will restore to default when mysql restart.