Python with flask and MySQL time out after period of inactivity

Question

I'm creating a REST API. I'm using Python, Flask and MySQL to fetch data from the database and present them in the JSON format. But i've got an issue.

The REST API is served with NGINX and uWSGI and all works well. The index page stays available and doesn't time out but pages that make a connection to the database do. It takes about 15 minutes for pages to become unresponsive. This issue does not occur in a RDBMS like phpmyadmin or navicat.

What could it be and how can i fix it? The REST API code below.

# using python version 2.7.10
from flask import Flask, jsonify, request, session
import mysql.connector.pooling

#Make a connection with the DB

dbconfig = {
  "host" : "12.34.5.78",
  "database": "db",
  "user":     "user",
  "password": "pass"
}

conn = mysql.connector.connect(pool_name = "mypool",
                          pool_size = 6,
                          **dbconfig)

#Define the root
app = Flask(__name__)

#Landings page
@app.route('/')
def index():
    return "Hello World."

# return all resources by name
@app.route('/resources', methods=['GET'])
def allResourceNames():
    conn1 = mysql.connector.connect(pool_name="mypool")
    reader = conn1.cursor()
    query = ("SELECT name FROM resources")
    reader.execute(query)
    resources = []
    for name in reader:
        resources.append({'name' : name[0]})
    reader.close()
    conn1.close()
    return jsonify({"resources" : resources})

if __name__ == "__main__":
    app.run(debug=True)

It seems to me like you are using one connection over the course of multiple requests. When that connection becomes invalidated, new requests cannot be served since you are not creating a new connection. I think you should look into connection pooling. Maybe this question can help http://stackoverflow.com/questions/32658679/how-to-create-a-mysql-connection-pool-or-any-better-way-to-initialize-the-multip — Busturdust, Nov 01 '16 at 14:59
I took note of what you guys said. I have update the code. It still dies after a couple minutes... — Stephen, Nov 01 '16 at 22:23

score 1 · Answer 1 · answered Nov 01 '16 at 17:23

All the comments above are correct. Here's how you can modify your code to close DB connection after each request. It will create a new db connection each time when you call get_db and close it after each request.

# using python version 2.7.10
import mysql
from flask import Flask, jsonify, request, session, g

def connect_db():
    conn = mysql.connector.connect(user='user', password='pass',
                                   host='12.34.5.67', database='db')
    return conn

def get_db():
    if not hasattr(g, 'db'):
        g.db = connect_db()
    return g.db

#Define the root
app = Flask(__name__)


# close db at end of each request
@app.teardown_appcontext
def close_db(_):
    if hasattr(g, 'db'):
        g.db.close()


#Landings page
@app.route('/')
def index():
    return "Hello World."

# return all resources by name
@app.route('/resources', methods=['GET'])
def allResourceNames():
    reader = get_db()
    query = ("SELECT name FROM resources")
    reader.execute(query)
    resources = []
    for name in reader:
        resources.append({'name' : name[0]})
    return jsonify({"resources" : resources})

if __name__ == "__main__":
    app.run(debug=True)

Link to docs

Could you take a look at my changed code above. I'm using pooling to create a couple connections. I close and open connections at run time. Weirdly enough this is still not working... — Stephen, Nov 01 '16 at 23:17
Did you modify your code as my answer for `get_db` and `connect_db` being two functions to create a connection to mysql? — , Nov 02 '16 at 16:11

Python with flask and MySQL time out after period of inactivity

1 Answers1