Django ORM处理数据库连接超时最好的解决方案

2023-04-1321:41:08后端程序开发Comments1,576 views字数 5662阅读模式

Django ORM 数据库连接模式是：每个 request 独立的数据库连接。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

就是 Django 每一个 request 都会创建一个数据库连接，并且在 response 之后，关闭数据库连接。每一个 request 的数据库连接都是独立的！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

众所周知，出于节约资源的考虑，客户端在一段时间没有动作之后，服务端会主动关闭连接，此时如果我们继续使用已经被服务器关闭的连接会报什么错误呢？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

当访问数据库，但是服务端已经主动关闭连接了，此时会报什么错误？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

错误类型

mysql 客户端对数据库进行操作时，打开终端窗口，如果一段时间没有操作，再次操作时，常常会报如下错误：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

  • ERROR 2013 (HY000): Lost connection to MySQL server during query
  • ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect...

好的，让我们看看这两个错误对应 pymysql 中的异常类型吧文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

2013 错误码对应的是什么异常类型？

先看第一个 2013 对应的异常类型
constants/CR.py文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

CR_SERVER_LOST = 2013

connections.py文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

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

可以看到对应的错误类型是 OperationalError文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

2006 错误码对应的是什么异常类型？

再看第二个 2006 对应的异常类型
constants/CR.py文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

CR_SERVER_GONE_ERROR = 2006

connections.py文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

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

可以看到对应的错误类型是 OperationalError文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

operational 是什么意思？

看看 有道翻译 中的解释吧文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

operational
英 [ˌɒpəˈreɪʃənl] 美 [ˌɑːpəˈreɪʃənl]
adj. （机器、设备等）正常运转的，可使用的；操作的，运营的，业务的；军事行动的；操作论的，操作主义的文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

从这个翻译来看，这个异常类是一个很宽泛的异常类文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

动手做一做

实验环境如下所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

(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 主动关闭无动作的连接的阈值是变量 time_out 指定的。
使用如下语句查看该值：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

show global variables like '%timeout%';

结果如下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

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>

wait_timeout:服务器关闭非交互连接之前等待活动的秒数，就是你在你的项目中进行程序调用文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

interactive_timeout: 服务器关闭交互式连接前等待活动的秒数,就是你在你的本机上打开mysql的客户端,cmd的那种文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

如果要修改 time_out 的值，可以执行如下的语句：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

set global wait_timeout=10;

方便看 Django 会抛出什么错误，就设为很短的 10 秒吧。
启动 Django ，然后先执行一次查询，（确保连接上数据库），然后等 10 秒后再重复执行一次文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

Django 什么时候会去连接数据库？
通过 Wireshark 抓包和观察会话管理,可以确认的是：
python manage.py shell 的时候，不会去连接数据库，导入模型也不会去连接数据库，只有当进行真正的 CRUD 才会去连接数据库，且是长连接
如何查看会话管理：?文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

SELECT * FROM `information_schema`.`PROCESSLIST` P

连接管理文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

Tweet.objects.all()

结果不出意外的报错了文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

~/.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')

再执行一次看看会不会重连。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

~/.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 没有帮我们重连，只是报错类型不一样了。不管在执行多少次 Tweet.objects.all() 结果都是上面的 InterfaceError: (0, '')文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

解决方案 —— 重新建立连接

Django 提供了 close_old_connections 方法文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html

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)
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/33529.html
  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/bc/33529.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定