[Django] #30441: Connection leak if CONN_MAX_AGE == None
Django
-----------------------------------------+------------------------
Reporter: cryptogun | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 2.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
PostgreSQL default max connection is 100.
if `CONN_MAX_AGE == None` in setting.py:
Every time I authenticate() a user, the connection is opened but not
closed.
{{{user = authenticate(request=request, username='admin',
password='123456')}}}
if `CONN_MAX_AGE == 0`:
The connection will be closed properly.
{{{
Traceback (most recent call last):
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\utils\autoreload.py", line 225, in wrapper
fn(*args, **kwargs)
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\core\management\commands\runserver.py", line 120, in
inner_run
self.check_migrations()
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\core\management\base.py", line 442, in check_migrations
executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\migrations\executor.py", line 18, in __init__
self.loader = MigrationLoader(self.connection)
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\migrations\loader.py", line 49, in __init__
self.build_graph()
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\migrations\loader.py", line 212, in build_graph
self.applied_migrations = recorder.applied_migrations()
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\migrations\recorder.py", line 61, in applied_migrations
if self.has_table():
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\migrations\recorder.py", line 44, in has_table
return self.Migration._meta.db_table in
self.connection.introspection.table_names(self.connection.cursor())
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\backends\base\base.py", line 256, in cursor
return self._cursor()
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\backends\base\base.py", line 233, in _cursor
self.ensure_connection()
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\backends\base\base.py", line 217, in ensure_connection
self.connect()
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\utils.py", line 89, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\backends\base\base.py", line 217, in ensure_connection
self.connect()
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\backends\base\base.py", line 194, in connect
self.connection = self.get_new_connection(conn_params)
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\django\db\backends\postgresql\base.py", line 178, in
get_new_connection
connection = Database.connect(**conn_params)
File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-
packages\psycopg2\__init__.py", line 126, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
django.db.utils.OperationalError: FATAL: remaining connection slots are
reserved for non-replication superuser connections
}}}
Bug reproduce (with attached project):
1. set CONN_MAX_AGE to None:
{{{
# settings.py
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
'CONN_MAX_AGE': None,
}
}
}}}
2. Print and trace db connection:
Modify `site-packages\django\db\backends\base\base.py`
{{{
# site-packages\django\db\backends\base\base.py
# Add a print line:
def ensure_connection(self):
"""Guarantee that a connection to the database is established."""
if self.connection is None:
with self.wrap_database_errors:
print('open connection +++++++++++++++++++')
self.connect()
}}}
{{{
# Add a print line:
def close(self):
"""Close the connection to the database."""
self.validate_thread_sharing()
self.run_on_commit = []
# Don't call validate_no_atomic_block() to avoid making it
difficult
# to get rid of a connection in an invalid state. The next
connect()
# will reset the transaction state anyway.
if self.closed_in_transaction or self.connection is None:
return
try:
print('close connection ......................')
self._close()
finally:
if self.in_atomic_block:
self.closed_in_transaction = True
self.needs_rollback = True
else:
self.connection = None
}}}
3. Open a new incognito browser <kbd>Ctrl</kbd><kbd>Shift</kbd> +
<kbd>n</kbd>.
4. Goto landing page.
5. Check Django log output.
`open connection +++++++++++++++++++`
No close log printed.
6. Goto 3. and try as many times as you want.
7. Switch to PostgreSQL backend, refresh 100 times, and you will get the
above `OperationalError`.
8. Set CONN_MAX_AGE to `0` in settings.py.
9. Retry 3~5
Now the close was executed.
{{{
open connection +++++++++++++++++++
[04/May/2019 16:09:53] "GET / HTTP/1.1" 200 2
close connection ......................
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30441>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: cryptogun | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 2.2
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by cryptogun):
* Attachment "connection_leak.zip" added.
Demo project.
Django
Reporter: cryptogun | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 2.2
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Description changed by cryptogun:
Old description:
New description:
PostgreSQL default max connection is 100.
if `CONN_MAX_AGE == None` in setting.py:
Every time I authenticate() a user, a new connection is opened but not
Bug reproduce (with demo project attached):
3. Open a new incognito browser `Ctrl + Shift + n`.
4. Goto landing page.
5. Check Django log output.
`open connection +++++++++++++++++++`
No close log printed.
6. Goto 3. and try as many times as you want.
7. Switch to PostgreSQL backend, refresh 100 times, and you will get the
above `OperationalError`.
8. Set CONN_MAX_AGE to `0` in settings.py.
9. Retry 3~5
Now the close was executed.
{{{
open connection +++++++++++++++++++
[04/May/2019 16:09:53] "GET / HTTP/1.1" 200 2
close connection ......................
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30441#comment:1>
Django
Reporter: cryptogun | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 2.2
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by cryptogun):
* Attachment "connection_leak.zip" added.
Demo project.
--
Django
-------------------------------------+-------------------------------------
Reporter: cryptogun | Owner: nobody
Type: Bug | Status: new
(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
Changes (by felixxm):
* version: 2.2 => master
* component: Uncategorized => Database layer (models, ORM)
* stage: Unreviewed => Accepted
Comment:
I can reproduce this issue only when I reload page before previous request
is handled, e.g.
{{{
open connection +++++++++++++++++++
[06/May/2019 08:02:37] "GET / HTTP/1.1" 200 2
open connection +++++++++++++++++++
open connection +++++++++++++++++++
open connection +++++++++++++++++++
[06/May/2019 08:02:37] "GET / HTTP/1.1" 200 2
[06/May/2019 08:02:37] "GET / HTTP/1.1" 200 2
[06/May/2019 08:02:37] "GET / HTTP/1.1" 200 2
}}}
Accepted for future investigation.
--
Ticket URL: <https://code.djangoproject.com/ticket/30441#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mahdi Zareie):
correct me if I'm wrong, `CONN_MAX_AGE` is a numeric parameter, and None
is not a valid value for a numeric parameter. isn't it reasonable to raise
ImproperlyConfigured exception when someone set this parameter to None?
--
Ticket URL: <https://code.djangoproject.com/ticket/30441#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mahdi Zareie):
Replying to [comment:3 Mahdi Zareie]:
> correct me if I'm wrong, `CONN_MAX_AGE` is a numeric parameter, and None
is not a valid value for a numeric parameter. isn't it reasonable to raise
ImproperlyConfigured exception when someone set this parameter to None?
I just understood what's going on, according to the documentations:
> The default value is 0, preserving the historical behavior of closing
the database connection at the end of each request. To enable persistent
connections, set CONN_MAX_AGE to a positive number of seconds. For
unlimited persistent connections, set it to None.
--
Ticket URL: <https://code.djangoproject.com/ticket/30441#comment:4>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mahdi Zareie):
* status: new => closed
* resolution: => invalid
Comment:
I reproduce the problem using the example source code you have provided
when I used development server, it said `django.db.utils.OperationalError:
FATAL: sorry, too many clients already`.
But I failed in reproducing the same error when I tried the same scenario
using gunicorn, according to documentation:
> The development server creates a new thread for each request it handles,
negating the effect of persistent connections. Don’t enable them during
development.
so I think it's not a bug, it's the expected behavior if you are using
development server.
--
Ticket URL: <https://code.djangoproject.com/ticket/30441#comment:5>