MySQL Bugs: #76177: Packet for query is too large (1094

Bug #76177	Packet for query is too large (1094 > 1024)
Submitted:	5 Mar 2015 15:33	Modified:	28 Apr 2015 10:02
Reporter:	chen siwei	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.69	OS:	Linux (2.6.32-358.23.2.el6.x86_64)
Assigned to:		CPU Architecture:	Any

Description:
environment:tomcat7,mysql-connector-java-5.1.17-bin.jar,hibernate3

Hibernate operation: could not execute query; SQL [select product0_.Id as Id0_, product0_.ActiveBeginDate as ActiveB
e2_0_, product0_.ActiveEndDate as ActiveEn3_0_, product0_.ActivePrice as ActivePr4_0_, product0_.Activenum as Activenum0_, product0_.BigPictrue as BigPictru
e0_, product0_.Category as Category0_, product0_.Color as Color0_, product0_.CreatePerson as CreatePe9_0_, product0_.CreatedDate as Created10_0_, product0_.
Description as Descrip11_0_, product0_.Introduce as Introduce0_, product0_.Keywords as Keywords0_, product0_.MarketPrice as MarketP14_0_, product0_.MemberPr
ice as MemberP15_0_, product0_.ModifiedDate as Modifie16_0_, product0_.ModifiedPerson as Modifie17_0_, product0_.Name as Name0_, product0_.Ordernum as Order
num0_, product0_.Productid as Productid0_, product0_.Purchase as Purchase0_, product0_.Relation as Relation0_, product0_.Remark as Remark0_, product0_.Serie
sName as SeriesName0_, product0_.SeriesTop as SeriesTop0_, product0_.SmallPictrue as SmallPi26_0_, product0_.Standard as Standard0_, product0_.Status as Sta
tus0_, product0_.Stock as Stock0_ from Product product0_ where product0_.Productid=?]; Packet for query is too large (1094 > 1024). You can change this valu
e on the server by setting the max_allowed_packet' variable.; nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1094
> 1024). You can change this value on the server by setting the max_allowed_packet' variable.

Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1287 > 1024). You can change this value on the server by setting the max_all
owed_packet' variable.
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3279)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1971)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281)
        at sun.reflect.GeneratedMethodAccessor48.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
        at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
        at $java.sql.Wrapper$$EnhancerByProxool$$a62526b1.executeQuery(<generated>)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
        at org.hibernate.loader.Loader.doQuery(Loader.java:802)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
        at org.hibernate.loader.Loader.doList(Loader.java:2542)

connect string: jdbc:mysql://192.168.100.212:3306/dhc?useUnicode=true&amp;characterEncoding=UTF-8&amp;max_allowed_packet=1073741824

I had changed 'max_allowed_packet' up to 32M, but no use.

mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 33554432 |
+--------------------+----------+

Once this error appeared, all sql longer than 1024 would failed. I had try to restart tomcat, but no effect. In the end, I had to restart mysqld. After a few days, the error reappear. I run this sql at mysql comman line, it work.

How to repeat:
I don't konw how to repeat. It would appear every few days.

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.

Since EOL of version 5.1 is 2013-12-31, I'll close this report as "Unsupported". Please upgrade to 5.5/5.6.

Please note that this is due to the limit of mysql setting and it could be changed. Please refer tohttp://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

I have a similar problem, was it resolved when you updated the server?

We are experiencing the same problem with mysql 5.7.11 connectivity as below

[DEBUG] 2016-03-05 02:50:18.845 [http-bio-8080-exec-1] SqlExceptionHelper - could not extract ResultSet [n/a]
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1122 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3592) ~[mysql-connector-java-5.1.34.jar:5.1.34]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417) ~[mysql-connector-java-5.1.34.jar:5.1.34]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) ~[mysql-connector-java-5.1.3

when executed the following command on terminal it shows the max allowed packet size value as 32MB which is the configured value in the config file

SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 33554432 |
+--------------------+----------+
1 row in set (0.00 sec)

in /etc/mysql/my.cnf I have the config un [mysqld] as below

[mysqld]
/** other configs **/
max_allowed_packet      = 32M

connection url does not seems to be having any configuration on this as it shows the url as jdbc:mysql://localhost:3306/nickelring_test

once the mysql server is restarted this problem goes away but appear again within few days.

Affects me in 5.7.12 version, too.

We also facing same issue, our mysql version is 5.6

Is the bug resolved? I also got this problem, jdbc keep reporting such error, and the max_allow_packet I set is large to 1GB. and the mysql server version is 5.6

This is still reproducible on Distrib 5.6.30. 
However i can't identify a specific scenario. It just happens... randomly after one hour, or 3 days os so on. A mysql server restart solves the problem temporarily.

I'd say this is a bit more critical and deserves a higher severity.

We are also facing this same issue. It is reproducing randomly. I also increased the max_allowed_packet = 256M as suggested by the others. But still the issue is reproducing.

In my previous comment, I mentioned as reproducible after updating to 256M. Sorry for that..I have updated to 256M and monitoring the issue to reproduce. I will keep posted if it is reproduced.

As I posted in the previous comment, the outcome is, the value is again reset to 1M. Found the root cause, in the error.log where I found there are unresolvable ips which means your server is accessible from public. So someone has been hacking and execute some script without our knowledge. So better update the password from the default password and also block the default port from public access as well.

Now not facing any issues.