一劳永逸解决Django下的pymysql的连接失效问题(Lost connection to MySQL server during query)

运行环境

--------------------------------------------------------------------

Python 3.7

Django 3.1.5

PyMySQL 1.0.2

 

  基本Django的web项目长时间运行之后，其中的定时任务或者长时驻留的自定义线程在操作数据库的时候偶尔或者必然发生连接失效的错误

  Traceback (most recent call last):
  File "/usr/local/python3/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/python3/lib/python3.7/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/python3/lib/python3.7/site-packages/pymysql/cursors.py", line 163, in execute
    result = self._query(query)
  File "/usr/local/python3/lib/python3.7/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/usr/local/python3/lib/python3.7/site-packages/pymysql/connections.py", line 505, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/python3/lib/python3.7/site-packages/pymysql/connections.py", line 724, in _read_query_result
    result.read()
  File "/usr/local/python3/lib/python3.7/site-packages/pymysql/connections.py", line 1069, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/python3/lib/python3.7/site-packages/pymysql/connections.py", line 646, in _read_packet
    packet_header = self._read_bytes(4)
  File "/usr/local/python3/lib/python3.7/site-packages/pymysql/connections.py", line 699, in _read_bytes
    CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

  解决方案：

  方案一：

  在使用数据操作并且有可能发生连接失效的错误的代码前调用django的close_old_connections方法，close_old_connections检查所有连接，如果已经失效则回收并且重新创建。

from django.db import close_old_connections
close_old_connections()

  方案二：

  覆盖django的cursor创建函数，通过查阅源码知道所有model操作数据库之前都经过django.db.backends.mysql.base.DatabaseWrapper的create_cursor方法获得cursor。

django.db.backends.mysql.base.DatabaseWrapper的原码：

@async_unsafe
def create_cursor(self, name=None):
    cursor = self.connection.cursor()
    return CursorWrapper(cursor)

那么在那里覆盖合适呢？在安装pymysql的__init__.py文件：

# -*- coding: utf-8 -*-
import pymysql
# 伪装版本号，解决版本兼容问题
pymysql.version_info = (1, 4, 13, "final", 0)
pymysql.install_as_MySQLdb()

from django.db.backends.mysql.base import CursorWrapper, DatabaseWrapper
from django.utils import asyncio

def create_cursor(self, name=None):
    # 解决连接失效的错误，
    # 检查连接的有效性，如果否则回收并且重创建
    self.connection.ping(reconnect=True)
    cursor = self.connection.cursor()
    return CursorWrapper(cursor)

DatabaseWrapper.create_cursor = asyncio.async_unsafe(create_cursor)