Django reconnect to DB when connection is lost

I have django project, Mysql DB is on a separate host, connected by TCP. I have this script:

#!/usr/bin/env python

from asyncio import sleep, run

from django import setup as django_setup
django_setup()

from django.db import connections
from django.contrib.auth.models import User
from django.db import OperationalError, close_old_connections

async def test():
    while True:
        try:
            objs=User.objects.all()
            print(await objs.acount())
        except KeyboardInterrupt:
            break
        except OperationalError as e: # Need to reconnect
            print(f'main(): OperationalError: {e}')
            #raise
            # (1053, 'Server shutdown in progress')
            # (2013, 'Lost connection to MySQL server during query')
            # (2006, 'Server has gone away')
            code=e.args[0]
            if code in {1053, 2006, 2013, 2026}: # Only when it's restarting, and once
                #await sync_to_async(conn.connect)()
                close_old_connections()
                print('After reconnect')
                #reconnect()
            else:
                #breakpoint()
                pass
        except Exception as e:
            print(f'in _round() {type(e)}')
        else:
            pass
            #breakpoint()

        print('Sleeping')
        await sleep(5)

try:
    run(test())
except KeyboardInterrupt:
    raise
except:
    print('In run(main())')
    breakpoint()
    pass

Then I use tcpkill to interrupt the connection:

tcpkill 'dst port 3306'

How do I instruct django to reconnect? I doesn't do it automatically, close_old_connection doesn't work, the output is then like this:

3
Sleeping
3
Sleeping
main(): OperationalError: (2013, 'Lost connection to MySQL server during query')
After reconnect
Sleeping
main(): OperationalError: (2013, 'Lost connection to MySQL server during query')
After reconnect
Sleeping
main(): OperationalError: (2013, 'Lost connection to MySQL server during query')
After reconnect
Sleeping

When looking into the code, I see it spawns cursor object from connection object, while doesn't work and then is closed, while probably connection object stays intact and keeps giving unusable cursors. I've tried connection.connect(), but it won't work, because the code is async.

You can do this with Middleware, I expect if this question is tied to users, actually using your app.

from django.db import connection, OperationalError
from django.utils.deprecation import MiddlewareMixin

class ReconnectMiddleware(MiddlewareMixin):
    def process_request(self, request):
        try:
            # test the connection
            connection.cursor()
        except OperationalError:
            # if not close and next time 
            # view hits it will try to reconnect
            connection.close()

Now add the middleware to your settings.py (Likely above everything else)

MIDDLEWARE = [
 '[wherever].ReconnectMiddleware',
 # others...
]

What this does is, on each view you are hitting this Middleware. It checks or closes the connection. So for example, if your DB loses connection and then you load the page and this error occurs, the next time you load the page (if the DB is available), it will reconnect.

Вернуться на верх