Bug #76816 Packet for query is too large (2420 > 1024)
Submitted: 24 Apr 2015 4:38 Modified: 4 May 2015 6:45
Reporter: Mahesh Gohil Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:5.5.38 OS:Linux (Linux 3.13.0-29-generic)
Assigned to: CPU Architecture:Any
Tags: hibernate4, MySQL, mysql-connector-java 5.1.34, tomcat7

[24 Apr 2015 4:38] Mahesh Gohil
Description:
Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (2420 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.

I set 
SET GLOBAL max_allowed_packet=16777216;

and also 
[mysqld]
max_allowed_packet = 16M

I also check the value after setting the value 

SHOW VARIABLES LIKE 'max_allowed_packet'; and value = 16777216.

But after few days the value set to 1024 automatically.
Mysql not restarted and there is no script that updated this value.

How to repeat:
I don't konw how to repeat. It would appear every few days
[28 Apr 2015 10:00] Umesh Shastry
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

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

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.
[28 Apr 2015 10:08] Mahesh Gohil
Hi,

Thanks for your answer.

I created a thread in the forum.mysql.com.

http://forums.mysql.com/read.php?10,630715,630715#msg-630715.

Hope i get the solution.
[30 Apr 2015 7:05] Mahesh Gohil
I add bug to http://forums.mysql.com/read.php?10,630715,630715#msg-630715.

In the forum Peter Brawley told that "Consider reporting it as a bug"
[30 Apr 2015 14:17] Umesh Shastry
Hello Mahesh,

I've checked internally with Conn/J Dev's and confirmed that this is not a bug. We suspect that since max allowed packet is dynamic variable and can be changed globally so some user/script(connection string somewhere resetting this value) is changing it internally.   May be you want to enable  logging(at the MySQL and/or at app level ) and try to identify who/how this is changed.

Please refer this page for various logging details - https://dev.mysql.com/doc/refman/5.5/en/server-logs.html

Thanks,
Umesh
[1 May 2015 7:51] Mahesh Gohil
I already mention that "no script/user that updated this value".

I will try general log and get back to u.
[4 May 2015 6:22] Mahesh Gohil
Hi I enable genernal log and found out the below query got executed .

                370291 Query    DROP FUNCTION IF EXISTS sys_get
150503 14:18:52 370291 Query    DROP FUNCTION IF EXISTS sys_set
                370291 Query    DROP FUNCTION IF EXISTS sys_exec
                370291 Query    DROP FUNCTION IF EXISTS sys_eval
                370291 Query    SET GLOBAL max_allowed_packet=1073741824
150503 14:18:53 370291 Query    FLUSH PRIVILEGES
                370291 Query    SET GLOBAL max_allowed_packet=1073741824
                370291 Query    FLUSH PRIVILEGES
                370291 Query    GRANT  ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DROP, EVENT, EXECUTE, FILE, INDEX, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER ON  *.* TO 'root'@'%' WITH GRANT OPTION
150503 14:18:54 370291 Query    FLUSH PRIVILEGES
                370291 Query    FLUSH PRIVILEGES
                370291 Query    GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `mysql`.* TO 'root'@'%' WITH GRANT OPTION
                370291 Query    FLUSH PRIVILEGES
                370291 Query    FLUSH PRIVILEGES

				150503 14:18:54 370291 Query    FLUSH PRIVILEGES
                370291 Query    FLUSH PRIVILEGES
                370291 Query    GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `mysql`.* TO 'root'@'%' WITH GRANT OPTION
                370291 Query    FLUSH PRIVILEGES
                370291 Query    FLUSH PRIVILEGES
150503 14:18:55 370291 Query    set global log_bin_trust_function_creators=1
                370291 Query    set global log_bin_trust_function_creators=TRUE
                370291 Query    SET GLOBAL log_bin_trust_routine_creators=1
                370291 Query    FLUSH PRIVILEGES
150503 14:18:56 370291 Query    DROP FUNCTION IF EXISTS lib_mysqludf_sys_info

       370960 Query    DROP FUNCTION IF EXISTS sys_exec
150503 14:20:52 370960 Query    DROP FUNCTION IF EXISTS sys_eval
                370960 Query    DROP FUNCTION IF EXISTS cmdshell
                370960 Query    set global log_bin_trust_function_creators=0
                370960 Query    SET GLOBAL log_bin_trust_function_creators=FALSE
150503 14:20:53 370960 Query    SET GLOBAL log_bin_trust_routine_creators=0
                370960 Query    SET GLOBAL max_allowed_packet=1024

I really don't know who executes these query.But i am sure we don't have any application or script that executes this type of query.
When this query executed by mysql ? Does it make sense now?? Please help me out.
[4 May 2015 6:31] Umesh Shastry
See "SET GLOBAL max_allowed_packet=1024" in the general query log - I suspect this is something you have to investigate at your end to see who and from where(script, or manually) this is happening. 

       370960 Query    DROP FUNCTION IF EXISTS sys_exec
150503 14:20:52 370960 Query    DROP FUNCTION IF EXISTS sys_eval
                370960 Query    DROP FUNCTION IF EXISTS cmdshell
                370960 Query    set global
log_bin_trust_function_creators=0
                370960 Query    SET GLOBAL
log_bin_trust_function_creators=FALSE
150503 14:20:53 370960 Query    SET GLOBAL
log_bin_trust_routine_creators=0
                370960 Query    SET GLOBAL max_allowed_packet=1024

But the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.
[4 May 2015 6:45] Mahesh Gohil
I will go to the forum .But i want to clear on below lines.

I see the 'SET GLOBAL max_allowed_packet=1024'.I don't have any script that execute below queries.I am not using the sys_exec or sys_eval functions.Its the mysql that used these type of queries.So i want to know about when will be below queries executed by mysql i.e sys_exec,sys_eval function.

370960 Query    DROP FUNCTION IF EXISTS sys_exec
150503 14:20:52 370960 Query    DROP FUNCTION IF EXISTS sys_eval
                370960 Query    DROP FUNCTION IF EXISTS cmdshell
                370960 Query    set global
log_bin_trust_function_creators=0
                370960 Query    SET GLOBAL
log_bin_trust_function_creators=FALSE
150503 14:20:53 370960 Query    SET GLOBAL
log_bin_trust_routine_creators=0
[4 May 2015 7:22] Umesh Shastry
sys_exec, sys_eval and cmdshell etc doesn't look native functions. May be some UDFs?
[17 May 2016 12:20] Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=81458 marked as duplicate of this one.
[17 May 2016 12:21] Chiranjeevi Battula
Similar bugs:
https://bugs.mysql.com/bug.php?id=76177
https://bugs.mysql.com/bug.php?id=80622