问题:mysql批量插入10W条记录,异常:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (32,164,587 > 4,194,304). 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 (32,164,587 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.
; ]; Packet for query is too large (32,164,587 > 4,194,304). 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 (32,164,587 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.`
处理:
1. 查询max_allowed_packet值:SHOW VARIABLES where Variable_name in ('max_allowed_packet')
2.修改max_allowed_packet值
[root@java]# mysql --help | grep 'my.cnf'
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
修改/etc/my.cnf,新增配置max_allowed_packet=1073741824
3.重启mysql服务service mysqld restart,查看服务状态service mysqld status
值范围:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html