mysql OperationalError(2013, 'Lost connection to MySQL server during query')

最新推荐文章于 2024-11-16 00:04:07 发布

原创最新推荐文章于 2024-11-16 00:04:07 发布 · 5.1k 阅读

5 ·

CC 4.0 BY-SA版权

MySQL 同时被 2 个专栏收录

5 篇文章

订阅专栏

数据库—错误

3 篇文章

订阅专栏

本文介绍了解决MySQL查询过程中出现的连接丢失问题。首先检查并调整max_allowed_packet参数以增大其值，其次通过调整多种timeout参数来延长超时时间，包括读写超时等。

原因：在查询的过程中与mysql服务器失去连接

解决：

1. 查看max_allowed_packet的值，尽量将其然后改得尽量大一些

mysql> show global variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| max_allowed_packet | 268435456 | 
+--------------------+-----------+
1 row in set (0.00 sec)

2. 将timeout的值调大以后

mysql> show global variables like '%timeout%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    | 
| delayed_insert_timeout     | 300   | 
| innodb_lock_wait_timeout   | 100   | 
| innodb_rollback_on_timeout | OFF   | 
| interactive_timeout        | 28800 | 
| net_read_timeout           | 30    | 
| net_write_timeout          | 60    | 
| slave_net_timeout          | 3600  | 
| table_lock_wait_timeout    | 200   | 
| wait_timeout               | 28800 | 
+----------------------------+-------+
10 rows in set (0.00 sec)

mysql> set global net_read_timeout = 120; 
Query OK, 0 rows affected (0.03 sec)

mysql> set global net_write_timeout = 900;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%timeout%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    | 
| delayed_insert_timeout     | 300   | 
| innodb_lock_wait_timeout   | 100   | 
| innodb_rollback_on_timeout | OFF   | 
| interactive_timeout        | 28800 | 
| net_read_timeout           | 120   | 
| net_write_timeout          | 900   | 
| slave_net_timeout          | 3600  | 
| table_lock_wait_timeout    | 200   | 
| wait_timeout               | 28800 | 
+----------------------------+-------+
10 rows in set (0.00 sec)