Solving the problem of Django database connection loss (explanation with examples)

The content of this article is about solving the problem of Django database connection loss (explanation with examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Problem

When using mysql in Django, the database connection may occasionally be lost. The errors usually include the following two types

1. `OperationalError: (2006, 'MySQL server has gone away')`  
1. `OperationalError: (2013, 'Lost connection to MySQL server during query')`

Query mysql global variables SHOW GLOBAL VARIABLES ;You can see wait_timeout, this variable represents the connection idle time. If the client uses one connection to query the database multiple times, there will be no problem if the query is continuous. If the query pauses for more than wait_timeout after several queries, the database connection will be lost and the database connection will be lost.

Reproduction

Use Django to reproduce the problem next time:

1. Set the wait_timeout of mysql to 10 seconds, and then enter the django shell simulation Query (only part of the following error message is retained)

In[1]：import time
In[2]：from django.contrib.auth.models import User
In[3]：list(User.objects.filter(id=1))
Out[3]：[<User: admin>]
In[4]：time.sleep(15) # 模拟比较慢的代码（其中没有查询数据库的代码），或者空闲什么都不操作一段时间，此时间要比`wait_timeout`大一些
list(User.objects.filter(id=1))
Traceback (most recent call last):
  File "<ipython-input-4-3574ae8220ee>", line 1, in <module>
    list(User.objects.filter(id=1))

  File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 1037, in _read_bytes
    CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
django.db.utils.OperationalError: (2013, 'Lost connection to MySQL server during query')

Seeking

Then the above problem basically shows that the error is caused by too long idle time.
In order to reduce unnecessary database connections and closures, Django reuses database connections. When a request is started, a connection pool is established to store the connection. After that, a connection is reused for each request. My guess is that Django saves the connection longer than wait_timeout. If the save time is shorter, the connection can be re-established to avoid this error.
Yes, the official document has also explained this problem, setting the database CONN_MAX_AGE parameter, example:

DATABASES = {
    "default": {
            'ENGINE': 'django.db.backends.mysql',
            'NAME': '',
            'USER': '',
            'PASSWORD': '',
            'HOST': '',
            'CONN_MAX_AGE': 9  # 比wait_timeout小一些
    }
}

When we tested it, we found that things were not as simple as we thought. Why does the error still occur? Behind all this, is it the distortion of human nature or the loss of morality? Please watch the next episode "Breakthrough".

Breakthrough

After searching CONN_MAX_AGE in the django source code, I followed the clues and found out how django closes the failed connectiondjango. db.close_old_connections()：

# Register an event to reset transaction state and close connections past
# their lifetime.
def close_old_connections(**kwargs):
    for conn in connections.all():
        conn.close_if_unusable_or_obsolete()

signals.request_started.connect(close_old_connections)
signals.request_finished.connect(close_old_connections)

The focus is on the last two lines. This method is executed when specific events are implemented through signal. The two specific events, as the name implies, are the start of the request and the end of the request. The error we reported was in one request, so this method is usually ineffective. It only closes and re-establishes the connection for each request.

Solution

Do not close the django shell that reproduces the problem. Continue to execute the following code:

In[5]：from django.db import close_old_connections
In[6]：close_old_connections()
In[7]：list(User.objects.filter(id=1))
Out[7]: [<User: admin>]

Call django.db.close_old_connections and query again. No more errors.
Then if we want to avoid this error, we must call the django.db.close_old_connections method before executing each database query.

1. Generally, this kind of problem will not occur, because the database query is continuously performed in one request, and there is no need to call this method for every request. This is a groundless worry.

2. Sometimes there is a large amount of data in a request, and the database will be queried and then other processing (not involving the database) will be performed for a period of time. For example, some data will be queried first, then the data will be processed, excel will be generated, the file will be saved, and Generate url. It is known that this takes a very long time, so it is best to call django.db.close_old_connections first to prevent the connection from being lost when the final URL is saved in the database.

The above is the detailed content of Solving the problem of Django database connection loss (explanation with examples). For more information, please follow other related articles on the PHP Chinese website!