MySQL server has gone away 问题

导入数据遇到的:ERROR : (2006, 'MySQL server has gone away') 意思就是连接断开了。

主要看下面几个参数:

1.是否重启过,如果uptime太短就是最近重启过

MariaDB [(none)]> show global status like 'uptime';  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 68600 |
+---------------+-------+
1 row in set (0.93 sec)  

2.查看无操作多久后会自动关闭

MariaDB [(none)]> show global variables like '%timeout';  
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| thread_pool_idle_timeout   | 60       |
| wait_timeout               | 28800    |
+----------------------------+----------+
11 rows in set (0.01 sec)  

如果wait_timeout太短就在my.ini或者my.cnf中修改一下:

wait_timeout=2880000  
interactive_timeout = 2880000  
max_allowed_packet = 512M  

3.进程主动kill掉,主要就是这个了,由于是导入数据,肯定就是这个max_allowed_packet太小了

MariaDB [(none)]> show global status like 'com_kill';  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 10    |
+---------------+-------+
1 row in set (0.00 sec)  
MariaDB [(none)]> show global variables like 'max_allowed_packet';  
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.01 sec)  

那就设置大一点儿就行了,这个也可以在上面的那个文件里修改:

MariaDB [(none)]> set global max_allowed_packet=1024*1024*16;  
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> show global variables like 'max_allowed_packet';  
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)