[Django] #24810: Reopen database connection automatically when no transaction is active
Django
-------------------------------------+-------------------------------------
Reporter: aaugustin | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: Accepted | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
This is a follow-up of #15802.
When the database closes the connection, for instance because the database
server was restarted, an exception will be raised the next time user code
attempts to use the database. Django doesn't attempt to do anything about
it.
This is expected to result in at most one 500 error for each thread that
serves requests, since database connections are thread-local. This doesn't
sound horrific. After all the database was restarted while the website was
serving requests.
It can also cause management commands to crash, which isn't the end of the
world either since management commands should fall into one of the
following categories:
- short-lived, idempotent and scheduled to run regularly
- long-lived and supervised to run constantly
- run manually
If the connection was in autocommit mode and an operation fails because
the database has closed the connection, theoretically, it's safe the
reopen the connection and retry the operation.
In that case, Django could continue to operate transparently instead of
raising an exception.
This may not be easy to implement.
--
Ticket URL: <https://code.djangoproject.com/ticket/24810>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: aaugustin | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by akaariai):
It must be noted that SQL connections can have state even when not in a
transaction. These could be for example session variables, per-connection
configuration (SET search_path = xxx), temporary tables etc. While Django
doesn't use any of these directly, breaking Django for users who do use
connection state should be avoided.
If we are going to reopen connections automatically we need some safe-
guards against above problems. As we can't detect automatically when users
have session state in their connections, the users should tell us about
this. I think opt-out (or maybe opt-in?) flag in the connection's settings
could work. Something like set `OPTIONS['stateful_connection'] = True`,
and Django will not try to reopen failed connections.
Other than the above remark I don't see a problem with this approach.
Still, without further evidence that these kinds of failures are common, I
believe this will require too much complexity compared to what is gained.
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: aaugustin | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: depaolim@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: aaugustin | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by binary):
Yes, I have the same problem in the daemon tasks that access DB through
django models.
I don't see a problem with the connection state, actually, as we can say
to do all the necessary preparations in the connection_created signal and
then newly created connection can be set up correctly?
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: aaugustin | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: binary@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: aaugustin | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by noky):
It should be noted that in the case where a daemon is accessing the DB via
the ORM, if the db connection is closed for some reason (usually the db
server restarting), then ALL subsequent DB read queries FAIL. At that
point, the process can no longer perform any read query since the db
connection is closed. I'm not concerned with retrying statements that
failed during the time the db was down, but it does seem like a bug that
the ORM completely fails to reconnect AT ALL. The funny thing is: if and
only if the process happens to perform a DB write after the DB connection
gets closed, then the ORM will open a new connection.
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: aaugustin | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by aaugustin):
Yes, these are the reasons why this ticket exists. Patches *that do not
ignore transactional integrity* welcome.
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: aaugustin | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by binary):
Replying to [comment:6 aaugustin]:
> Yes, these are the reasons why this ticket exists. Patches *that do not
ignore transactional integrity* welcome.
So I gave it a humble try.
There is no problem in case the trouble happened inside a transaction
because in case error happened and was caught by the Atomic, the
connection will be closed in one of the places like
[https://github.com/django/django/blob/master/django/db/transaction.py#L230
this]. And next transaction will work without any problem as connection
will be re-established because django is now aware that the current one is
not an option any more.
It's still a problem in autocommit mode though because in this case
connection won't be closed and will remain in a broken state, so in case
we try to obtain a cursor again, we'll get the InterfaceError exception.
So the code like:
{{{
def do_query():
return Model.objects.first()
def loop():
while True:
try:
print do_query()
except:
print 'shit happened'
sleep(5)
}}}
will stop working after first disconnect.
At first I tried to intercept InterfaceError at the moment we are
obtaining a cursor and OperationalErorr at the execute method, but then I
realized that such a problem may happen in other methods, like fetchone or
anywhere else, so eventually I came to just intercept it in the
DatabaseErrorWrapper and close the connection if one of those errors
occured but only in case we are not inside a transaction, as this case is
covered already and it may break that code. Here's the commit:
https://github.com/jbinary/django/commit/568a4eb573c7fce2fdec2d1578b2db4f3d0e51fd
After that, that code above starts to work even after disconnect.
I have no any idea if this is the right approach, and I have no idea how
to write tests for this yet. But if anyone will tell this one is a worthy
approach, I'll try to think about tests too. (but advices are welcome,
ofc)
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: aaugustin | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:8>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Marti Raudsepp (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:9>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: dms-cat (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:10>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Hugo Osvaldo Barrera):
I've been trying to address this with no luck.
My main problem is accurately reproducing this issue -- it seems to
happen from time to time in production for me. The DB connection seems to
die for some reason, and the next request fails with {{{InterfaceError}}}.
However, testing locally I tried to reproduce this by:
1. Start Django and Postgres.
2. Execute a Request (which does a DB call). It works!
3. Soft-exit Postgres
2. Execute a Request (which does a DB call). It still works! 😓
I tried as an alternative killing postgres in (3), but the result is the
same.
As per Sentry, I'm hitting this error a few hundred times a month, so I'd
really like to address this.
Any hints on how to accurately reproduce this?
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:11>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Daniel Hahler):
Hugo,
you should be able to reproduce this when restarting PostgreSQL after
getting the cursor, i.e.:
{{{
from django.db import connection
cursor = connection.cursor()
__import__("time").sleep(5) # restart postgresql
cursor.execute("select 1")
}}}
This then fails with the following for me on Django 3.2.8:
{{{
Traceback (most recent call last):
File "…/django/db/backends/utils.py", line 82, in _execute
return self.cursor.execute(sql)
psycopg2.errors.AdminShutdown: terminating connection due to administrator
command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "…/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File "…/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "…/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "…/django/db/utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "…/django/db/backends/utils.py", line 82, in _execute
return self.cursor.execute(sql)
django.db.utils.OperationalError: terminating connection due to
administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
}}}
(Note that in this case the first exception is of type
{{{AdminShutdown}}}, whereas on Sentry I see {{{psycopg2.OperationalError:
server closed the connection unexpectedly}}} there.)
Just for reference: there exists https://github.com/jdelic/django-dbconn-
retry, but it has its (open) issues also.
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:13>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: New feature => Uncategorized
Comment:
I can second this issues. We see this happening thousands of times, but
still at random (we have a lot of traffic). We have a relatively small
stack, and I scanned it completely to see if we have any place that closes
the connection prematurely. This is not the case. Therefore, I would
contest the ticket status and would propose to move this to bug.
This is certainly unexpected for a framework that comes with a very
sophisticated ORM and is probably not debuggable or mitigable event for
most experienced developers.
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:14>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* type: Uncategorized => New feature
Comment:
I think it was originally specified as a New Feature since it's never been
supported. (It seems moot: any patch will only be applied to the
development branch.)
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:15>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: amureki (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:16>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: medihack (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:17>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by medihack):
This issue also bit me. I have a long-running management command that
accesses the database only a few times a day, and I often get an
OperationalError that the database connection is already closed. I tried
to work around it by using ''db.close_old_connections()'', but then my
tests using pytest failed as it also closed some other connections. I
wonder if ''CONN_HEALTH_CHECKS'' makes a difference here, but I guess that
it only works with a request inside a view.
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:18>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
This ticket relates to #14845 which ought to document the database
connection-creation and management process.
If that can be of any help
[https://code.djangoproject.com/ticket/34914#comment:3 an established
pattern when dealing long running management commands] is to call
`close_old_connections` in strategic locations alike to how it's called
between each HTTP requests that Django serves.
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:19>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* cc: Ülgen Sarıkavak (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/24810#comment:20>