解决django运行中连接mysql数据库超时报错pymysql.err.InterfaceError

完整报错

运行的好好的，突然某个时间开始，使用django的model读取数据库时就会报错：

Traceback (most recent call last):

  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
           │    │      │       │    └ ()
           │    │      │       └ 'SELECT `device_discover_deviceinfomodel`.`index` FROM `device_discover_deviceinfomodel`'
           │    │      └ <function CursorWrapper.execute at 0x7f0bafd01bf8>
           │    └ <django.db.backends.mysql.base.CursorWrapper object at 0x7f0a16d53eb8>
           └ <django.db.backends.utils.CursorDebugWrapper object at 0x7f0ba15754e0>

  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
           │    │      │       │      └ ()
           │    │      │       └ 'SELECT `device_discover_deviceinfomodel`.`index` FROM `device_discover_deviceinfomodel`'
           │    │      └ <function Cursor.execute at 0x7f0bb14da048>
           │    └ <pymysql.cursors.Cursor object at 0x7f0ba13b9780>
           └ <django.db.backends.mysql.base.CursorWrapper object at 0x7f0a16d53eb8>

  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
             │    │      └ 'SELECT `device_discover_deviceinfomodel`.`index` FROM `device_discover_deviceinfomodel`'
             │    └ <function Cursor._query at 0x7f0bb14da488>
             └ <pymysql.cursors.Cursor object at 0x7f0ba13b9780>

  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
    │    │     └ 'SELECT `device_discover_deviceinfomodel`.`index` FROM `device_discover_deviceinfomodel`'
    │    └ <function Connection.query at 0x7f0bb1506268>
    └ <pymysql.connections.Connection object at 0x7f0a1c061cf8>

  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 547, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
    │    │                │       │          └ b'SELECT `device_discover_deviceinfomodel`.`index` FROM `device_discover_deviceinfomodel`'
    │    │                │       └ 3
    │    │                └ <module 'pymysql.constants.COMMAND' from '/usr/local/lib/python3.6/site-packages/pymysql/constants/COMMAND.py'>
    │    └ <function Connection._execute_command at 0x7f0bb1506950>
    └ <pymysql.connections.Connection object at 0x7f0a1c061cf8>

  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 793, in _execute_command
    raise err.InterfaceError(0, "")
          │   └ <class 'pymysql.err.InterfaceError'>
          └ <module 'pymysql.err' from '/usr/local/lib/python3.6/site-packages/pymysql/err.py'>

pymysql.err.InterfaceError: (0, '')

这行代码之前没有问题，但是突然某个时间点开始就疯狂出错

解决方案

在使用model前，使用：

import django.db
django.db.close_old_connections()

比如：

import django.db
django.db.close_old_connections()
print(list(django.contrib.auth.models.User.objects.all()))

问题解析

这是因为长时间没有使用连接池里的连接，导致一部分连接已经失效，因此手动清理一下老旧的连接即可