MYSQL批量插入记录异常的处理方法

问题：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