[Django] #35812: 2006 'MySQL server has gone away' error after idle period in Django app polling Kafka

[Django]

#35812: 2006 'MySQL server has gone away' error after idle period in Django app
polling Kafka
-------------------------------------+-------------------------------------
Reporter: Adithya Kaade | Type:
Arvind | Uncategorized
Status: new | Component:
| Uncategorized
Version: 4.2 | Severity: Normal
Keywords: 2006 'MySQL server | Triage Stage:
has gone away' | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
**Issue Summary:**

In a custom Django app, a 2006: 'MySQL server has gone away' error occurs
when querying the MySQL database after an idle period. The app polls
messages from a Kafka topic with low traffic, so after a long idle period,
the first query to the MySQL database fails with this error. However,
immediately after the failure, subsequent queries succeed without issue.

**Steps to Reproduce:**

Create a custom Django app using python manage.py startapp.
Inside the app, implement an infinite loop to poll messages from a
Kafka topic.
Messages are rarely inserted into the Kafka topic.
After a long idle period, when a new message is received and the app
tries to query the MySQL database, the first query fails with the error
(2006, 'Server has gone away').
Immediately after this failure, subsequent queries succeed without any
errors.

**Expected Behavior:**

When CONN_HEALTH_CHECKS = True is set, the connection to the database
should be checked and re-established before the query is executed,
preventing the error.
Actual Behavior:

The first query fails with a 2006: 'MySQL server has gone away' error
after an idle period.
Setting CONN_HEALTH_CHECKS = True did not resolve the issue.
Setting CONN_MAX_AGE = 30 also did not prevent the error.
All subsequent queries after the first failure succeed immediately
without issues.

**Environment:**

Django Version: 4.2.13
Python Version: 3.10.14
MySQL Version: 8.0

**Additional Information:**

The issue only occurs for the first query after an idle period;
subsequent queries work as expected.
Tested with both CONN_HEALTH_CHECKS and CONN_MAX_AGE settings, but the
issue persists.
--
Ticket URL: <https://code.djangoproject.com/ticket/35812>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35812: 2006 'MySQL server has gone away' error after idle period in Django app
polling Kafka
-------------------------------------+-------------------------------------

Reporter: Adithya Kaade | Owner: (none)
Arvind |
Type: Bug | Status: new
Component: Uncategorized | Version: 4.2
Severity: Normal | Resolution:

Keywords: 2006 'MySQL server | Triage Stage:
has gone away' | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Adithya Kaade Arvind):

* type: Uncategorized => Bug

--
Ticket URL: <https://code.djangoproject.com/ticket/35812#comment:1>

[Django]

#35812: 2006 'MySQL server has gone away' error after idle period in Django app
polling Kafka
-------------------------------------+-------------------------------------
Reporter: Adithya Kaade | Owner: (none)
Arvind |
Type: Bug | Status: new

Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Normal | Resolution:
Keywords: 2006 'MySQL server | Triage Stage:
has gone away' | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Adithya Kaade Arvind):

* cc: Adithya Kaade Arvind (added)
* component: Uncategorized => Database layer (models, ORM)

--
Ticket URL: <https://code.djangoproject.com/ticket/35812#comment:2>

[Django]

#35812: 2006 'MySQL server has gone away' error after idle period in Django app
polling Kafka
-------------------------------------+-------------------------------------
Reporter: Adithya Kaade | Owner: (none)
Arvind |
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: 2006 'MySQL server | Triage Stage:
has gone away' | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Adithya Kaade Arvind:

Old description:

New description:

--
Ticket URL: <https://code.djangoproject.com/ticket/35812#comment:3>

[Django]

#35812: 2006 'MySQL server has gone away' error after idle period in Django app
polling Kafka
-------------------------------------+-------------------------------------
Reporter: Adithya Kaade | Owner: (none)
Arvind |
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: 2006 'MySQL server | Triage Stage:
has gone away' | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by famagusta):

As per the source code for MySQL backend, the is_usable method doesn't
check connection health
https://github.com/django/django/blob/main/django/db/backends/mysql/base.py

But the postgres backend does. I wonder why the difference.

Also, as per mysqlclient documentation, the use of ping function (in
is_usable) can take reconnection arguments
https://dev.mysql.com/doc/connector-python/en/connector-python-api-
mysqlconnection-ping.html

would this solve the issue?
--
Ticket URL: <https://code.djangoproject.com/ticket/35812#comment:4>

[Django]

#35812: 2006 'MySQL server has gone away' error after idle period in Django app
polling Kafka
-------------------------------------+-------------------------------------
Reporter: Adithya Kaade | Owner: (none)
Arvind |

Type: Bug | Status: closed

Component: Database layer | Version: 4.2
(models, ORM) |

Severity: Normal | Resolution: invalid

Keywords: 2006 'MySQL server | Triage Stage:
has gone away' | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* resolution: => invalid
* status: new => closed

Comment:

Please refer to the
[https://docs.djangoproject.com/en/5.1/ref/databases/#caveats database
documentation about caveats]

> If a connection is created in a long-running process, outside of
Django’s request-response cycle, the connection will remain open until
explicitly closed, or timeout occurs. You can use
`django.db.close_old_connections()` to close all old or unusable
connections.

In other words, outside of the request-response cycle Django has no entry
point to determine when the connection should be ping'ed to determine if
it should be closed and re-opened and performing a ping before every
single query is not desirable so `CONN_HEALTH_CHECKS`
[https://docs.djangoproject.com/en/5.1/ref/settings/#conn-health-checks
has no effect there].

> If set to True, existing persistent database connections will be health
checked before they are reused **in each request** performing database
access.

You should explicitly `close_old_connections` before every code path
outside of request-response cycle that interacts with the database and
could be idling for longer than your configured connection timeouts. In
your case, being a Kafka consumer, I assume that would be every time you
consume a new message.
--
Ticket URL: <https://code.djangoproject.com/ticket/35812#comment:5>