mysql的设置参数中max_allowed_packet过小导致操作异常

最新推荐文章于 2025-07-10 14:38:33 发布

y41992910

最新推荐文章于 2025-07-10 14:38:33 发布

阅读量6.6w

点赞数 10

CC 4.0 BY-SA版权

分类专栏： javaweb_实践问题汇总 mysql

本文链接：https://blog.youkuaiyun.com/y41992910/article/details/88788778

javaweb_实践问题汇总同时被 2 个专栏收录

141 篇文章

订阅专栏

mysql

17 篇文章

订阅专栏

本文介绍了解决MySQL中max_allowed_packet参数导致的问题的方法。包括通过配置文件进行永久性修改及使用命令行实现临时调整。并提供了详细的步骤说明及注意事项。

永久性解决方案:
修改方法1(配置文件持久化修改)：
vim /etc/my.cnf
[mysqld]
max_allowed_packet = 100M

注意：修改配置文件以后，需要重启mysql服务才能生效。

临时解决方案,修改该值
show variables like ‘max_allowed_packet’;

set global max_allowed_packet = 10 * 1024 * 1024;

但是重启Mysql还是会恢复到默认值,所以需要写在配置文件当中

注意：

1.命令行修改时，不能用M、G，只能这算成字节数设置。配置文件修改才允许设置M、G单位。

2.命令行修改之后，需要退出当前回话(关闭当前mysql server链接)，然后重新登录才能查看修改后的值。通过命令行修改只能临时生效，下次数据库重启后又复原了。

3.max_allowed_packet 最大值是1G(1073741824)，如果设置超过1G，查看最终生效结果也只有1G。

排查过程


root     32270     1  0 Feb22 ?        00:00:00 /bin/sh /mariadb/mysql/bin/mysqld_safe --defaults-file=/etc/my10.3.cnf --user=mysql --datadir=/mariadb/mysql/data
mysql    32398 32270  0 Feb22 ?        00:12:58 /mariadb/mysql/bin/mysqld --defaults-file=/etc/my10.3.cnf --basedir=/mariadb/mysql --datadir=/mariadb/mysql/data --plugin-dir=/mariadb/mysql/lib/plugin --user=mysql --log-error=/mariadb/mysql/data/error.log --pid-file=/mariadb/mysql/mysql.pid --socket=/mariadb/mysql/mysql.sock --port=3306

url="jdbc:mariadb://10.1.1.19:3306/certtrust?useUnicode=true&amp;characterEncoding=UTF-8"


[root@vm-11a-01m93 etc]# ifconfig
eth0      Link encap:Ethernet  HWaddr FA:16:3E:E6:F6:38  
          inet addr:10.1.1.19  Bcast:10.1.1.255  Mask:255.255.255.0
          inet6 addr: fe80::f816:3eff:fee6:f638/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1450  Metric:1
          RX packets:244383612 errors:0 dropped:0 overruns:0 frame:0
          TX packets:241543679 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:117353719738 (109.2 GiB)  TX bytes:101645269028 (94.6 GiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:77623616 errors:0 dropped:0 overruns:0 frame:0
          TX packets:77623616 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:14628056786 (13.6 GiB)  TX bytes:14628056786 (13.6 GiB)

错误信息

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: 
### Error querying database.  Cause: java.sql.SQLException: (conn=9760) query size (2079) is &gt;= to max_allowed_packet (1024)
### The error may exist in com&#47;itrus&#47;newportal&#47;certtrustweb&#47;daoext&#47;EnterpriseUserInfoMapperExt.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT                    ent.id AS ent_id,        ent.email,        ent.`name`,        ent.phone_number,        ent.user_additional_field1,        ent.user_additional_field2,        ent.user_additional_field3,        ent.user_additional_field4,        ent.user_additional_field5,        ent.user_additional_field6,        ent.user_additional_field7,        ent.user_additional_field8,        ent.user_additional_field9,        ent.user_additional_field10,        ent.user_country,        ent.user_description,        ent.user_dns,        ent.user_ip,        ent.user_locality,        ent.user_name,        ent.user_organization,        ent.user_orgunit,        ent.user_serialnumber,        ent.user_state,        ent.user_street,        ent.user_surname,        ent.user_title,        ent.product_id,        ent.project_id,        ent.create_time,           uc.id AS uc_id,           uc.key_sn,           uc.cert_status,           uc.cert_cn,           uc.cert_end_time,           uc.cert_make_time,           uc.cert_revoke_reason,           uc.cert_revoke_time,           uc.cert_sn,           uc.cert_source,           uc.cert_start_time,           uc.issuer_dn,           uc.modify_time,           uc.cert_info_id,           uc.ra_account_id               FROM        enterprise_user_info AS ent         LEFT JOIN cert_belong_info AS cbi ON ent.id = cbi.enterprise_user_info_id         LEFT JOIN user_cert AS uc ON cbi.user_cert_id = uc.id          WHERE (cbi.newest_cert = TRUE OR cbi.enterprise_user_info_id IS NULL)                                                                                                                                                                                                                                                                                                                                                                                                          AND ent.product_id = ?                                           AND ent.project_id = ?          ORDER BY ent.create_time DESC                       limit ?,?
### Cause: java.sql.SQLException: (conn=9760) query size (2079) is &gt;= to max_allowed_packet (1024)
; uncategorized SQLException for SQL []; SQL state [HY]; error code [0]; (conn=9760) query size (2079) is &gt;= to max_allowed_packet (1024); nested exception is java.sql.SQLException: (conn=9760) query size (2079) is &gt;= to max_allowed_packet (1024)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:973)
	org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	com.itrus.newportal.filter.SecureFilter.doFilter(SecureFilter.java:231)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)