在最近做项目经常会遇到这样的一个错误,现在在这里做个记录,方便以后查看。
环境:Centos 7、Docker、Mysql
异常信息:### Error querying database. Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (1,047 > 1,024). You can change this value on the server by setting the 'max_allowed_packet' variable.
......
### Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (1,047 > 1,024). You can change this value on the server by setting the 'max_allowed_packet' variable.
; Packet for query is too large (1,047 > 1,024). You can change this value on the server by setting the 'max_allowed_packet' variable.; nested exception is com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (1,047 > 1,024). You can change this value on the server by setting the 'max_allowed_packet' variable."
根据错误信息,很容易可以知道是查询数据库的时候出错了,PacketTooBigException 数据包太大异常,可以修改参数 'max_allowed_packet' 参数的值。知道原因和解决方法了,下面进行修改这个参数。
1. 进入mysql 容器
docker exec -it mysql bash
2. 进入 /etc/mysql 目录(如果不是docker 容器的 mysql,也可以找到这个目录,修改 my.cnf 文件)
cd /etc/mysql
3. 修改 my.cnf 文件(如果未安装vim,需要先安装 vim)
安装vim(vim 未安装时)
apt-get update
apt-get install vim
修改my.cnf
vim my.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config should go here
!includedir /etc/mysql/conf.d/
max_allowed_packet=20M
修改 max_allowed_pactet 为 20M 即可。
修改完成后,需要重启 mysql
4. 重启 mysql
先退出容器
exit
查看mysql容器
docker ps -a
重启mysql容器
docker restart df1464244dc9
5. 查看 max_allowed_pactet 是否修改成功
show VARIABLES like '%max_allowed_packet%';
若 max_allowed_pactet 的值为 20971520 即表示修改成功,再进行测试功能即可成功。