永久性解决方案:
修改方法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&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 >= to max_allowed_packet (1024)
### The error may exist in com/itrus/newportal/certtrustweb/daoext/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 >= to max_allowed_packet (1024)
; uncategorized SQLException for SQL []; SQL state [HY]; error code [0]; (conn=9760) query size (2079) is >= to max_allowed_packet (1024); nested exception is java.sql.SQLException: (conn=9760) query size (2079) is >= 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)