Django with Peewee Connection Pooling MySQL disconnect

Question

I'm running a Django project with Peewee in Python 3.6 and trying to track down what's wrong with the connection pooling. I keep getting the following error on the development server (for some reason I never experience this issue on my local machine):

Lost connection to MySQL server during query

The repro steps are reliable and are:

1. Restart Apache on the instance.
2. Go to my Django page and press a button which triggers a DB operation.
3. Works fine.
4. Wait exactly 10 minutes (I've tested enough to get the exact number).
5. Press another button to trigger another DB operation.
6. Get the lost connection error above.

The code is structured such that I have all the DB operations inside an independent Python module which is imported into the Django module.

In the main class constructor I'm setting up the DB as such:

from playhouse.pool import PooledMySQLDatabase

def __init__(self, host, database, user, password, stale_timeout=300):
    self.mysql_db = PooledMySQLDatabase(host=host, database=database, user=user, password=password, stale_timeout=stale_timeout)
    db_proxy.initialize(self.mysql_db)

Every call which needs to make calls out to the DB are done like this:

def get_user_by_id(self, user_id):
    db_proxy.connect(reuse_if_open=True)
    user = (User.get(User.user_id == user_id))
    db_proxy.close()
    return {'id': user.user_id, 'first_name': user.first_name, 'last_name': user.last_name, 'email': user.email }

I looked at the wait_timeout value on the MySQL instance and its value is 3600 so that doesn't seem to be the issue (and I tried changing it anyway just to see).

Any ideas on what I could be doing wrong here?

Update:

I found that the /etc/my.cnf configuration file for MySQL has the wait-timeout value set to 600, which matches what I'm experiencing. I don't know why this value doesn't show when I runSHOW VARIABLES LIKE 'wait_timeout'; on the MySQL DB (that returns 3600) but it does seem likely the issue is coming from the wait timeout.

Given this I tried setting the stale timeout to 60, assuming that if it's less than the wait timeout it might fix the issue but it didn't make a difference.

Answer 1

I finally came up with a fix which works for my case, after trying numerous ideas. It's not ideal but it works. This post on Connection pooling pointed me in the right direction.

I created a Django middleware class and configured it to be the first in the list of Django middleware.

from peewee import OperationalError
from playhouse.pool import PooledMySQLDatabase

database = PooledMySQLDatabase(None)

class PeeweeConnectionMiddleware(object):

    CONN_FAILURE_CODES = [ 2006, 2013, ]

    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        if database.database: # Is DB initialized?
            response = None
            try:
                database.connect(reuse_if_open=True)
                with database.atomic() as transaction:
                    try:
                        response = self.get_response(request)
                    except:
                        transaction.rollback()
                        raise
            except OperationalError as exception:
                if exception.args[0] in self.CONN_FAILURE_CODES:
                    database.close_all()
                    database.connect()
                    response = None
                    with database.atomic() as transaction:
                        try:
                            response = self.get_response(request)
                        except:
                            transaction.rollback()
                            raise
                else:
                    raise
            finally:
                if not database.is_closed():
                    database.close()
            return response
        else:
            return self.get_response(request)