PyMySQL in Flask/Apache sometimes returning empty result

Question

I have a Flask application, being run in Apache, that relies on PyMySQL. The application provides a series of REST commands. It is running under Python 3.

Without providing the entire source, the program is structured as:

#!flask/bin/python
import json
import pymysql
from flask import *

# Used to hopefully share the connection if the process isn't restarted
mysql_connection = None   

# Gets the mysql_connection, or opens it
GetStoreCnx():
    global mysql_connection
    if (mysql_connection != None):
        store_connection_string = ""
        # Get the connection string from the config file
        with open('config/storedb.json', 'r') as f:
            store_connection_string = json.load(f)
        mysql_connection = pymysql.connect(**store_connection_string)
    return mysql_connection;


class Server(Flask):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)

# Return results via REST
@app.route('/results1', methods=['GET'])
def get_result1():
    cnx = GetStoreCnx();
    cursor = cnx.cursor();
    query = """
        SELECT 
            result_name,
            successful
        FROM
            results
        """
    cursor.execute(query)
    cnx.commit()
    result = cursor.fetchall()
    return json.dumps(result)

# Run server, if needed
if __name__ == '__main__':
    app.run(host='0.0.0.0', debug=True)

There are a few more REST calls - but they all essentially do the same thing (ie - get a connection, create a cursor, run a basic select query - which does sometimes have more then 2 fields, execute the query, fetch the result and return it as a JSON object). The commits there should be unnecessary, but it seems to be a running issue with PyMySQL that was resulting in getting old data.

The issue is that these REST calls sometimes return empty JSON sets (ie. []), Further investigation showed that the execute call sometimes returns a completely empty result but does not throw an exception. This happens regularly - but not all the time. Some calls do return values successfully. When I try to keep the call going until it returns a result (as:

while(cursor.execute(query) < 1):
    pass

) the process enters an endless loop, eventually (quickly) preventing apache from servicing any more requests.

The server is (at the moment) only servicing about 5 calls a second. The issue does not appear if I use the development server.

Is there any way I can prevent this error? Is it a fault in PyMySQL? Is something I'm doing preventing correct connections to MySQL?

Answer 1

You are creating one global mysql connection which is used by your application, hovever pymysql declares a threadsafety of 1, which according to the dbapi2 specification means:

1   Threads may share the module, but not connections. 

As conncurrent requests in flask will be served by different threads you shouldn't share the connection. The reason you don't experience any problems when using the developement server is that it runs single threaded.

To avoid this you could either:

• create a new connection for each thread, store it as thread local for further use
• create a new connection for each request, store it in flask.g for further use

To do so your GetStoreCnx function could be changed like this:

import threading
thread_local = threading.local()

def GetStoreCnx():        
    if not hasattr(thread_local, 'mysql_connection'):
        store_connection_string = ""
        # Get the connection string from the config file
        with open('config/storedb.json', 'r') as f:
            store_connection_string = json.load(f)
        mysql_connection = pymysql.connect(**store_connection_string)
        thread_local.mysql_connection = mysql_connection
    return thread_local.mysql_connection;

SQLAlchemy does something similar with its scoped_session(). This should also work with flask.g instead of thread_local for one connection per request.