5

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.

Mayank R Jain
  • 3,127
  • 1
  • 29
  • 43
  • on the client - is it `maxAllowedPacket` or `max_allowed_packet`? – pherris Jan 29 '16 at 08:16
  • What kind of query are you running? If you are manipulating BLOBs, you need to use a different technique to push the data back and forth. A 50MiB packet seems outrageous. – Christopher Schultz Jan 30 '16 at 21:25
  • Unrelated note: `innodb_additional_mem_pool_size` is a [worthless setting](http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_additional_mem_pool_size) unless you are using MySQL's internal memory allocator instead of standard `malloc` (which you should not be doing). That setting has been removed in later versions of MySQL. – Christopher Schultz Jan 30 '16 at 21:27

2 Answers2

3

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.

mainframer
  • 20,411
  • 12
  • 49
  • 68
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

pherris
  • 17,195
  • 8
  • 42
  • 58
  • 1
    I restarted the MySQL server - no effect - same error. Is there any other way tony define the variable on the client side besides the connection url? How do i check – Mayank R Jain Jan 29 '16 at 08:21
  • are you running replication? did you check the format of the param on the client? looks like it should be snake case instead of camel case `max_allowed_packet` – pherris Jan 29 '16 at 08:23
  • No replication - one server – Mayank R Jain Jan 29 '16 at 08:23
  • Checking with Param in url string in snake case... Will let you know if this works – Mayank R Jain Jan 29 '16 at 08:28
  • Some addtl. reading says that most clients are set higher than 1MB by default, so not feeling bullish about that being the issue. One interesting note is this doc: https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet which says that the config value should be an integer - you might try `max_allowed_packet = 51200` instead of `50M` (although other parts of the docs say this will work). Also, you are bouncing the MySQL instance when you make the changes to my.cnf, right? – pherris Jan 29 '16 at 08:34
  • Yes I restart after each change – Mayank R Jain Jan 29 '16 at 08:39
  • So the snake case does not help - got the error again - 2039 > 1024 this time – Mayank R Jain Jan 30 '16 at 10:18
  • Also changed the my.cnf to read 5230000 instead of 50M restarted - same error after 30 mins of usage. What am I missing? – Mayank R Jain Jan 30 '16 at 12:34
  • Isn't 5230000 5.3 gb? 1 gb is the max. Sorry, I'm not 100% sure. – pherris Jan 30 '16 at 13:17