当前位置：首页 > 文章列表 > 数据库 > MySQL > Django 处理连接超时最好的解决方案

Django 处理连接超时最好的解决方案

来源：SegmentFault 2023-01-23 10:34:28 0浏览收藏

所属专题：Django 企业级 Web 实战专题 - 从模型、缓存到 API 与测试

小伙伴们对数据库编程感兴趣吗？是否正在学习相关知识点？如果是，那么本文《Django 处理连接超时最好的解决方案》，就很适合你，本篇文章讲解的知识点主要包括MySQL、python。在之后的文章中也会多多分享相关知识点，希望对大家的知识积累有所帮助！

先说一个事实：Django ORM 数据库连接模式是：每个 request 独立的数据库连接。

就是 Django 每一个 request 都会创建一个数据库连接，并且在 response 之后，关闭数据库连接。每一个 request 的数据库连接都是独立的！

众所周知，出于节约资源的考虑，客户端在一段时间没有动作之后，服务端会主动关闭连接，此时如果我们继续使用已经被服务器关闭的连接会报什么错误呢？

当访问数据库，但是服务端已经主动关闭连接了，此时会报什么错误？

错误类型

CR_SERVER_LOST = 2013

raise err.OperationalError(
    CR.CR_SERVER_LOST,
    "Lost connection to MySQL server during query",
)

可以看到对应的错误类型是

CR_SERVER_GONE_ERROR = 2006

raise err.OperationalError(
    CR.CR_SERVER_GONE_ERROR, "MySQL server has gone away (%r)" % (e,)
)

可以看到对应的错误类型是

(twitter) ╭─bot@mbp13m1.local ~/Desktop/code/python/twitter  ‹main*› 
╰─➤  python --version                                                   
Python 3.9.7
(twitter) ╭─bot@mbp13m1.local ~/Desktop/code/python/twitter  ‹main*› 
╰─➤  pip show django                   
Name: Django
Version: 3.2.9
Summary: A high-level Python Web framework that encourages rapid development and clean, pragmatic design.
Home-page: https://www.djangoproject.com/
Author: Django Software Foundation
Author-email: foundation@djangoproject.com
License: BSD-3-Clause
Location: /Users/bot/.local/share/virtualenvs/twitter-73pfN55g/lib/python3.9/site-packages
Requires: asgiref, pytz, sqlparse
Required-by: django-filter, django-oss-storage, djangorestframework

查看和修改默认的 time_out 参数

Mysql 主动关闭无动作的连接的阈值是变量

show global variables like '%timeout%';

结果如下

mysql root@192.168.31.203:d_twitter_db> show global variables like '%timeout%';                                                                                                                                                                                                                           
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| connect_timeout                   | 10       |
| delayed_insert_timeout            | 300      |
| have_statement_timeout            | YES      |
| innodb_flush_log_at_timeout       | 1        |
| innodb_lock_wait_timeout          | 50       |
| innodb_rollback_on_timeout        | OFF      |
| interactive_timeout               | 28800    |
| lock_wait_timeout                 | 31536000 |
| mysqlx_connect_timeout            | 30       |
| mysqlx_idle_worker_thread_timeout | 60       |
| mysqlx_interactive_timeout        | 28800    |
| mysqlx_port_open_timeout          | 0        |
| mysqlx_read_timeout               | 30       |
| mysqlx_wait_timeout               | 28800    |
| mysqlx_write_timeout              | 60       |
| net_read_timeout                  | 30       |
| net_write_timeout                 | 60       |
| replica_net_timeout               | 60       |
| rpl_stop_replica_timeout          | 31536000 |
| rpl_stop_slave_timeout            | 31536000 |
| slave_net_timeout                 | 60       |
| wait_timeout                      | 28800    |
+-----------------------------------+----------+
22 rows in set
Time: 0.028s
mysql root@192.168.31.203:d_twitter_db>

set global wait_timeout=10;

方便看 Django 会抛出什么错误，就设为很短的 10 秒吧。
启动 Django ，然后先执行一次查询，（确保连接上数据库），然后等 10 秒后再重复执行一次

Django 什么时候会去连接数据库？
通过 Wireshark 抓包和观察会话管理,可以确认的是：
SELECT * FROM `information_schema`.`PROCESSLIST` P
连接管理

Tweet.objects.all()

结果不出意外的报错了

~/.local/share/virtualenvs/twitter-jSQKCUr2/lib/python3.9/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
    773         else:
    774             result = MySQLResult(self)
--> 775             result.read()
    776         self._result = result
    777         if result.server_status is not None:

~/.local/share/virtualenvs/twitter-jSQKCUr2/lib/python3.9/site-packages/pymysql/connections.py in read(self)
   1154     def read(self):
   1155         try:
-> 1156             first_packet = self.connection._read_packet()
   1157 
   1158             if first_packet.is_ok_packet():

~/.local/share/virtualenvs/twitter-jSQKCUr2/lib/python3.9/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
    699                 if packet_number == 0:
    700                     # MariaDB sends error packet with seqno==0 when shutdown
--> 701                     raise err.OperationalError(
    702                         CR.CR_SERVER_LOST,
    703                         "Lost connection to MySQL server during query",

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

再执行一次看看会不会重连。

~/.local/share/virtualenvs/twitter-jSQKCUr2/lib/python3.9/site-packages/django/db/backends/mysql/base.py in execute(self, query, args)
     71         try:
     72             # args is None means no string interpolation
---> 73             return self.cursor.execute(query, args)
     74         except Database.OperationalError as e:
     75             # Map some error codes to IntegrityError, since they seem to be

~/.local/share/virtualenvs/twitter-jSQKCUr2/lib/python3.9/site-packages/pymysql/cursors.py in execute(self, query, args)
    146         query = self.mogrify(query, args)
    147 
--> 148         result = self._query(query)
    149         self._executed = query
    150         return result

~/.local/share/virtualenvs/twitter-jSQKCUr2/lib/python3.9/site-packages/pymysql/cursors.py in _query(self, q)
    308         self._last_executed = q
    309         self._clear_result()
--> 310         conn.query(q)
    311         self._do_get_result()
    312         return self.rowcount

~/.local/share/virtualenvs/twitter-jSQKCUr2/lib/python3.9/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
    545         if isinstance(sql, str):
    546             sql = sql.encode(self.encoding, "surrogateescape")
--> 547         self._execute_command(COMMAND.COM_QUERY, sql)
    548         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    549         return self._affected_rows

~/.local/share/virtualenvs/twitter-jSQKCUr2/lib/python3.9/site-packages/pymysql/connections.py in _execute_command(self, command, sql)
    791         """
    792         if not self._sock:
--> 793             raise err.InterfaceError(0, "")
    794 
    795         # If the last query was unbuffered, make sure it finishes before

InterfaceError: (0, '')

好吧，没有 Django 没有帮我们重连，只是报错类型不一样了。不管在执行多少次

from django.db import close_old_connections
from django.db.utils import OperationalError

def job(self):
    try:
        something()
    except OperationalError as error:
        self.logger.error(f'job error: {error}', exc_info=True)
        close_old_connections()
    except Exception as error:
        self.logger.error(f'job error: {error}', exc_info=True)