django批量插入数据到mariadb报错

1. django使用bulk_create批量插入数据报错，报错内容（我插入的列表长度为3000+）

• BrokenPipeError: [Errno 32] Broken pipe
• pymysql.err.OperationalError: (2006, “MySQL server has gone away (BrokenPipeError(32, ‘Broken pipe’))”)
• django.db.utils.OperationalError: (2006, “MySQL server has gone away (BrokenPipeError(32, ‘Broken pipe’))”)

2. 问题原因

mysql服务端接收数据包的配置参数太小，默认为1048576字节
查询当前数据包接收值：

MariaDB [(none)]> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+

max_allowed_packet 值设置过小将导致单个记录超过限制后写入数据库失败，且后续记录写入也将失败

2. 处理方法

修改mysql的max_allowed_packet参数为适当大小，我设置为1G

1. 临时修改

# 设置该参数为1G
MariaDB [(none)]> set global max_allowed_packet = 1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)

# 立即查询，发现没变化
MariaDB [(none)]> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

# 退出一下mariadb，重新登录查询
MariaDB [(none)]> exit
Bye
(inspect) [root@python inspect]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 设置成功
MariaDB [(none)]> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1073741824 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> 

2. 永久修改
   修改/etc/my.cnf，在[mysqld]字段中添加：
   max_allowed_packet = 1G

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_allowed_packet = 1G
......

重启mariadb