MySQL error packet for query is too large (12238 > 1024). Can change this value

Today, when deploying a project to a Linux server, there were some inexplicable errors, but there was nothing wrong with the local, and by looking at the Tomcat logs in real time, we found that the error was:

View logs in real time:

1, first switch to: cd usr/local/tomcat5/logs2-F Catalina.out3, so the runtime can view the running log in real time

Error found:

rg.springframework.dao.transientdataaccessresourceexception:### Error QueryingDatabase. Cause:com.mysql.jdbc.PacketTooBigException:Packet forQuery isToo Large (1233 > 1024x768). Can change this value onThe server bySetting the Max_allowed_packet'variable.### the error exist in cn/xm/exam/mapper/employee/out/custom/unitcustommapper.xml### the error may invo Lve cn.xm.exam.mapper.employee.out.custom.unitcustommapper.gethaulunitbycondition-inline### the error occurred While setting parameters### Sql:select Haulunit.unitid, Haulunit.unitbigid, Haulunit.bigid, (SELECT Group_conca T (ProjectName) from Project where ProjectID in (select ProjectID from Haulunitproject where Bigid =haulunit.bigid and UN Itid= Haulunit.unitid)) as Projectnames, Haulunit.manager, Haulunit.managerphone, Haulunit.secure, haulunit.se Curephone, @b:= (Ifnull ((SELECT SUM (minusnum) from Breakrules, Haulemployeeout WHERE breakrules. Bigemployeeoutid = Haulemployeeout. Bigemployeeoutid and Haulemployeeout.unitid = Haulunit.unitid and breaktime like CONCAT (year (Curdate ()), '%')) , 0) as Unitminismum, Haulinfo.bigname, Haulinfo.bigstatus, Haulinfo.bigcreatedate, Unit.name, Unit.add Ress, Unit.contact, Unit.phone, @a:= (SELECT COUNT (Bigemployeeoutid) from Haulemployeeout WHERE Haulemployee Out.bigid = Haulunit.bigid and Haulemployeeout.unitid = Haulunit.unitid and trainstatus='2'as Personnum, TRUNCATE (Ifnull (@b/@a,0), 3) as Jiaquan from Haulunit, haulinfo, Unit WHERE Haulunit.bigid     =haulinfo.bigid and Haulunit.unitid=unit.unitid and Haulunit.bigid =? ORDER by Jiaquan desc limit?,? # # Cause:com.mysql.jdbc.PacketTooBigException:Packet for query is too larg E (1233 > 1024). You can change this value on the server by setting the Max_allowed_packet'variable.; SQL[]; Packet forQuery isToo Large (1233 > 1024x768). Can change this value onThe server bySetting the Max_allowed_packet'variable.; Nested exception is com.mysql.jdbc.PacketTooBigException:Packet for query is too large (1233 > 1024). You can change this value on the server by setting the Max_allowed_packet'variable. At Org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate ( Sqlstatesqlexceptiontranslator.java:107) at Org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate ( Abstractfallbacksqlexceptiontranslator.java: the) at Org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate ( Abstractfallbacksqlexceptiontranslator.java: the) at Org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate ( Abstractfallbacksqlexceptiontranslator.java: the)

-----------------Workaround: ( by looking at the MySQL global variable to find out because MySQL limits the maximum update size )----------------------

MySQL max_allowed_packet setting too small causes record write failure

MySQL restricts the size of packets accepted by the server according to the configuration file.

Sometimes large inserts and updates are limited by the Max_allowed_packet parameter, causing the write or update to fail.

View current configuration

Show VARIABLES like '%max_allowed_packet% ';

The results shown are:

+--------------------+---------+

| variable_name | Value |

+--------------------+---------+

| Max_allowed_packet | 1048576 |

+--------------------+---------+

The above description is currently configured to: 1M

Modify method

1. Modify the configuration file

You can edit the my.cnf to modify (under Windows My.ini) and modify it in the [Mysqld] section or in the MySQL server configuration section.

Max_allowed_packet = 20M

If you can't find the MY.CNF, you can pass

MySQL--help | grep my.cnf

To find the my.cnf file.

Under Linux the file is under/etc/.

2. Modify in MySQL command line

Run in the MySQL command line

Set Global Max_allowed_packet = 2*1024*1024*10

Then exit the command line, restart the MySQL service, and then enter.

Show VARIABLES like '%max_allowed_packet% ';

See if the next Max_allowed_packet is edited successfully

Note: This value is set too high to cause a single record to exceed the limit after the write to the database fails, and subsequent record writes will fail.   third, how to restart the Linux MySQL

1. Start with service: Service mysqld restart

2. Start with mysqld script:/etc/inint.d/mysqld restart

MySQL error packet for query is too large (12238 > 1024). Can change this value