Python loses connection to MySQL database after about a day

Question

I am developing a web-based application using Python, Flask, MySQL, and uWSGI. However, I am not using SQL Alchemy or any other ORM. I am working with a preexisting database from an old PHP application that wouldn't play well with an ORM anyway, so I'm just using mysql-connector and writing queries by hand.

The application works correctly when I first start it up, but when I come back the next morning I find that it has become broken. I'll get errors like mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query or the similar mysql.connector.errors.OperationalError: 2055: Lost connection to MySQL server at '10.0.0.25:3306', system error: 32 Broken pipe.

I've been researching it and I think I know what the problem is. I just haven't been able to find a good solution. As best as I can figure, the problem is the fact that I am keeping a global reference to the database connection, and since the Flask application is always running on the server, eventually that connection expires and becomes invalid.

I imagine it would be simple enough to just create a new connection for every query, but that seems like a far from ideal solution. I suppose I could also build some sort of connection caching mechanism that would close the old connection after an hour or so and then reopen it. That's the best option I've been able to come up with, but I still feel like there ought to be a better one.

I've looked around, and most people that have been receiving these errors have huge or corrupted tables, or something to that effect. That is not the case here. The old PHP application still runs fine, the tables all have less than about 50,000 rows, and less than 30 columns, and the Python application runs fine until it has sat for about a day.

So, here's to hoping someone has a good solution for keeping a continually open connection to a MySQL database. Or maybe I'm barking up the wrong tree entirely, if so hopefully someone knows.

Answer 1

I have it working now. Using pooled connections seemed to fix the issue for me.

mysql.connector.connect(
    host='10.0.0.25',
    user='xxxxxxx', 
    passwd='xxxxxxx', 
    database='xxxxxxx',
    pool_name='batman',
    pool_size = 3
)

def connection():
    """Get a connection and a cursor from the pool"""
    db = mysql.connector.connect(pool_name = 'batman')
    return (db, db.cursor())

I call connection() before each query function and then close the cursor and connection before returning. Seems to work. Still open to a better solution though.

Edit

I have since found a better solution. (I was still occasionally running into issues with the pooled connections). There is actually a dedicated library for Flask to handle mysql connections, which is almost a drop-in replacement.

From bash: pip install Flask-MySQL

Add MYSQL_DATABASE_HOST, MYSQL_DATABASE_USER, MYSQL_DATABASE_PASSWORD, MYSQL_DATABASE_DB to your Flask config. Then in the main Python file containing your Flask App object:

from flaskext.mysql import MySQL
mysql = MySQL()
mysql.init_app(app)

And to get a connection: mysql.get_db().cursor()

All other syntax is the same, and I have not had any issues since. Been using this solution for a long time now.