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

深藏阁楼爱情的钟 2022-02-28 07:44 155阅读 0赞

永久性解决方案:  
修改方法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)