CODEWITHSUNDEEP
djangomysql-8.0
btavares
btavares

Reputation: 313

Django/Mysql async error “The client was disconnected by the server because of inactivity”

I’m using Django 4.2 connected to a MySQL 8.0.30 database. The server is nginx , with gunicorn (3 workers) and using WSGI.

I’m running asyncio for a function I need to run asynchronously because it's used by an async API broker, but here I'm just calling directly from a view, so it's direct and short-lived:

from django.db import connection
import asyncio

example_object = asyncio.run(make_query(name))

async def make_query(request, name):
    object = await ModelExample.objects.filter(name=name).afirst()
    return object

I’m having the following error on the afirst() query execution: [4031] The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior

Initially the code works fine, the error only starts after a few hours (maybe 8h). I can temporarily fix it by restarting the (nginx) error. It’s a low traffic website so it’s very possible the function is executed only once a day.

Because it requires 8hours of innactivity, it's not being easy to replicate or debug this.

What I've tried:

Django layer:

In my understanding, Django always opens a connection, executes a query and then closes a connection. This should happen in WSGI too, but the connection getting idle suggest otherwise. So I tried:

  1. Adding close_old_connections() before asyncio.run
  2. Adding the connection.close() after asyncio.run
  3. Setting CONN_HEALTH_CHECKS option to True, in the hopes that “if the health check fails, the connection will be re-established without failing the request”
  4. Changing the CONN_MAX_AGE from the default 0 to “None” in the Django settings file, which according to Django docs, means an unlimited persistent database connection.

None had effect.

If this is a Django issue, then my last guess would be a bug with “afirst()” (added in Django 4.1) when using MySQL somehow.

MySQL layer:

Found some reports attributing this issue to MySQL (also happening in Wordpress and PHP applications), so I tried:

  1. Increasing the wait_timeout value and interactive_timeout variables in my MySQL config file. The SHOW VARIABLES command shows me they are indeed currently set to 31536000.
  2. Mysql 8.0.32 release notes seem to mention this issue here: "Whenever a connection was terminated due to inactivity, the thread pool plugin printed only a generic message about connections timing out; this often made analysis of such timeouts more difficult than necessary. A new INFO_LEVEL message makes it clear that a connection has been terminated due to inactivity in the thread pool, as well as which timeout value was used to make this determination. (Bug #34767607)"

So I updated the MySQL hoping to get more information, will let you know if I do.

Please let me know what you think, ideas or suggestions on how to debug this! Thanks in advance.

Upvotes: 2

Views: 6375

Answers (1)

btavares
btavares

Reputation: 313

Answering this for future people that might have the same problem if they are using DigitalOcean.

It appears that the initial approach of configuring MySQL was correct, but it was not actually being applied to the DB. If you are using DigitalOcean, applying configurations via SSH to the droplet does not correspond to the actual configurations in the database cluster.

To resolve this, you need to use DigitalOcean's API to configure the database. The following changes to the variables seemed to solve the issue for me:

  • connect_timeout to 60
  • interactive_timeout to 604800
  • wait_timeout to 2147483
  • net_read_timeout to 120

Django's async functionalities should work without issues in a low-traffic website with this configuration. Ofc may vary depending on your setup and requirements.

Upvotes: 3

Related Questions