Django's best solution for handling connection timeouts

Let's talk about a fact first: the Django ORM database connection mode is: a separate database connection for each request.

That is, Django creates a database connection for each request, and closes the database connection after the response. The database connection for each request is independent!

---

As we all know, for the sake of saving resources, the server will actively close the connection after the client does not act for a period of time. At this time, if we continue to use the connection that has been closed by the server, what error will be reported?

When accessing the database, but the server has actively closed the connection, what error will be reported at this time?

error type

When the mysql client operates on the database, open the terminal window. If there is no operation for a period of time, the following error is often reported when operating again:

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

Ok, let's see what type of exception these two errors correspond to in pymysql

What type of exception does the 2013 error code correspond to?

First look at the exception type corresponding to the first 2013
constants/CR.py

 CR_SERVER_LOST = 2013

connections.py

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

You can see that the corresponding error type is OperationalError

What type of exception does the 2006 error code correspond to?

Look at the exception type corresponding to the second 2006
constants/CR.py

 CR_SERVER_GONE_ERROR = 2006

connections.py

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

You can see that the corresponding error type is OperationalError

What does operational mean?

Take a look at the explanation in Youdao's translation

operational
British [ˌɒpəˈreɪʃənl] American [ˌɑːpəˈreɪʃənl]
adj. (machines, equipment, etc.) normally functioning, usable; operational, operational, business; military operations; operationalism, operationalism

From this translation, this exception class is a very broad exception class

---

do it

The experimental environment is as follows:

 (twitter) ╭─[e-mail] ~/Desktop/code/python/twitter ‹main*› ╰─➤ python --version Python 3.9.7 (twitter) ╭─[e-mail] ~/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: [e-mail] 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

View and modify default time_out parameters

The threshold for Mysql to actively close the connection without action is specified by the variable time_out .
Use the following statement to view the value:

 show global variables like '%timeout%';

The result is as follows

 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 : The number of seconds the server waits for activity before closing a non-interactive connection, which is when you make a program call in your project

interactive_timeout : The number of seconds the server waits for activity before closing the interactive connection, which is when you open the mysql client on your local machine, the kind of cmd

If you want to modify the value of time_out , you can execute the following statement:

 set global wait_timeout=10;

To see what errors Django throws, set it to a very short 10 seconds.
Start Django, then execute the query first, (make sure to connect to the database), then wait 10 seconds and repeat the execution

When will Django connect to the database?
By capturing packets with Wireshark and observing session management, it can be confirmed that:
When python manage.py shell , it will not connect to the database, import the model will not connect to the database, only when the real CRUD will connect to the database, and it is a long connection How to view session management:👇

 SELECT * FROM `information_schema`.`PROCESSLIST` P

connection management

 Tweet.objects.all()

The result is unexpectedly wrong

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

Execute it again to see if it will reconnect.

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

Well, no Django didn't help us reconnect, but the error type is different. No matter how many times Tweet.objects.all() is executed, the result is the above InterfaceError: (0, '')

Solution - re-establish the connection

Django provides the close_old_connections method

 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)

Reference article:

The difference between interactive_timeout and wait_timeout in MySQL
[Fun with MySQL 2] MySQL connection mechanism analysis and operation and maintenance
The solution to automatic disconnection of MySQL database connection timeout
Solve the situation that the mysql server actively disconnects in the no-operation timeout
Mysql view the number of connections (total number of connections, active number, maximum concurrent number)
PEP 249 -- Python Database API Specification v2.0