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.

Inger

After posting this I discovered pooled connections. I know it's meant for multithreaded applications, which mine is not, but it was easy enough to implement, and I'm hoping it helps. Of course, it will be tomorrow before I know if it did, and I'm still open to better solutions. – Inger

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.

Inger

Curious to know what kind of issues did you run into in case of connection pool? And talking about Flask-MySQL, are you creating the connection just once or on every query? – Treehopper

I get the connection only once, thought I get the cursor every query. It's been a long time and I forget what the issue was with the connection pool. I think it was randomly throwing 500 errors every so often, but I don't recall much more than that. – Inger

I had read that the connection isn't thread safe...so confused whether reusing the same connection is a robust approach – Treehopper

That very well may have been why I had occasional issues with the connection pool. That would certainly make sense. Flask-MySql library manages the connection behind the scenes, so that would explain why it worked better. – Inger