2006 'MySQL server has gone away' error after idle period in Django app polling Kafka

[Adithya Kaade Arvind]

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.

[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?

[Simon Charette]

Please refer to the ​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 ​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.