MySQL server has gone away

环境:

Os:Centos 7

DB:mysql 5.7.39

 

导入大量数据的时候报错误:

[root@localhost ~]# mysql -h localhost -uroot -pmysql --default-character-set=utf8 -D dev_test</tmp/db_test_20240723
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2006 (HY000) at line 7130: MySQL server has gone away
[root@localhost ~]#

 

解决办法:

mysql> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 33554432   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

 

mysql> show variables like '%interactive_timeout%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 86400 |
+---------------------+-------+
1 row in set (0.00 sec)


mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 120      |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 86400    |
+--------------------------+----------+
3 rows in set (0.00 sec)

 

查看wait_timeout和interactive_timeout这种都正常,就是max_allowed_packet=32MB 应该是设置小了，尝试设置大些,问题解决.

set global max_allowed_packet=1073741824;