解决com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1504 > 1024)问题

拼搏现实的明天。 2023-06-14 13:59 37阅读 0赞

今天在自己网站上写博客的时候，提示了这个错误  
部分错误如下：

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1504 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.
    	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3678) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2509) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
    	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409) ~[druid-1.1.10.jar!/:1.1.10]
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.1.10.jar!/:1.1.10]
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407) ~[druid-1.1.10.jar!/:1.1.10]
    	at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:619) ~[druid-1.1.10.jar!/:1.1.10]
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407) ~[druid-1.1.10.jar!/:1.1.10]
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.1.10.jar!/:1.1.10]
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407) ~[druid-1.1.10.jar!/:1.1.10]
    	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) ~[druid-1.1.10.jar!/:1.1.10]

网上查了一下，MySQL根据配置文件会限制Server接受的数据包大小。有时候插入、更新或查询时数据包的大小，会受 max\_allowed\_packet 参数限制，导致操作失败。

于是照着操作：  
一、登录mysql客户端

mysql -uroot -p

之后输入密码  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70]

二、查询接受的数据包大小

show VARIABLES like '%max_allowed_packet%';

如果配置文件中没有配置这个的话，会有这个提示：

![在这里插入图片描述][20191119232700415.png]  
如果有配置的话，就是下面这样：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 1]  
20971520是我设置的大小，为20M（这个是我后面修改的）

三、修改设置数据包大小  
找到my.cnf，进行修改

whereis my.ini

![在这里插入图片描述][20191119233032560.png]  
在配置文件里面，直接加入（如果有max\_allowed\_packet，就把大小改成20M，没有就加上这一句）

max_allowed_packet = 20M

四、重启Mysql

service mysql restart

五、进入Mysql客户端，再次查看数据包大小

show VARIABLES like '%max_allowed_packet%';

如果能查询出数据，就说明配置成功，问题解决

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191119232432882.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM=,size_16,color_FFFFFF,t_70
[20191119232700415.png]: https://img-blog.csdnimg.cn/20191119232700415.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191119232759207.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM=,size_16,color_FFFFFF,t_70
[20191119233032560.png]: https://img-blog.csdnimg.cn/20191119233032560.png