I am running a query against a MySQL database from a Flask app being run with uWSGI with multiple workers. I've noticed that sometimes when I query a resource by id, the id of the returned row is different than the one I queried with.
I thought that query isolation meant that this was not possible. However, it appears that MySQL is getting the queries mixed up. I am not able to reproduce this when not using uWSGI, but this may just be because it is running on localhost rather than a server when testing the Flask server by itself.
Why is there a mismatch between the input id and the result id?
from flask import Flask
import pymysql.cursor, random
class Database:
def __init__(self, user, password, host, database):
self.connection = pymysql.connect(
user=user,
password=password,
host=host,
database=database,
cursorclass=pymysql.cursors.DictCursor
)
def query(self, sql, **kwargs):
with self.connection.cursor() as cursor:
cursor.execute(sql, kwargs)
return cursor
app = Flask(__name__)
database = Database('user', 'password', 'localhost', 'database')
@app.route('/resources/<path:id>')
def resource(id):
item = database.query(
'SELECT resources.id FROM resources WHERE resources.id = %(id)s',
id=id
).fetchone()
identifier = random.random()
print(identifier, 'ID 1:', id)
print(identifier, 'ID 2:', item['id'])
if int(item['id']) != int(id):
print('Error found!!!')
return 'Done', 200
if __name__ == '__main__':
app.run()
[pid: 2824|app: 0|req: 1/1] xxx.xxx.xxx.xxx () {44 vars in 737 bytes} [Wed Oct 19 18:38:07 2016] GET /resources/10 => generated 4 bytes in 6 msecs (HTTP/1.1 200) 2 headers in 78 bytes (1 switches on core 0)
0.687535338604848 ID 1: 11
0.687535338604848 ID 2: 11
[pid: 2821|app: 0|req: 1/2] xxx.xxx.xxx.xxx () {44 vars in 737 bytes} [Wed Oct 19 18:38:07 2016] GET /resources/11 => generated 4 bytes in 5 msecs (HTTP/1.1 200) 2 headers in 78 bytes (1 switches on core 0)
0.9216930740141296 ID 1: 13
0.9216930740141296 ID 2: 13
[pid: 2823|app: 0|req: 1/3] xxx.xxx.xxx.xxx () {44 vars in 737 bytes} [Wed Oct 19 18:38:07 2016] GET /resources/13 => generated 4 bytes in 6 msecs (HTTP/1.1 200) 2 headers in 78 bytes (1 switches on core 0)
0.9053128320497649 ID 1: 12
0.9053128320497649 ID 2: 14
Error found!!!
0.794023616025622 ID 1: 15
0.794023616025622 ID 2: 15
[pid: 2824|app: 0|req: 2/4] xxx.xxx.xxx.xxx () {44 vars in 737 bytes} [Wed Oct 19 18:38:07 2016] GET /resources/15 => generated 4 bytes in 1 msecs (HTTP/1.1 200) 2 headers in 78 bytes (1 switches on core 0)
[pid: 2822|app: 0|req: 1/5] xxx.xxx.xxx.xxx () {44 vars in 737 bytes} [Wed Oct 19 18:38:07 2016] GET /resources/12 => generated 4 bytes in 31 msecs (HTTP/1.1 200) 2 headers in 78 bytes (1 switches on core 0)
0.3608322871408709 ID 1: 14
0.3608322871408709 ID 2: 16
Error found!!!
[pid: 2825|app: 0|req: 1/6] xxx.xxx.xxx.xxx () {44 vars in 737 bytes} [Wed Oct 19 18:38:07 2016] GET /resources/14 => generated 4 bytes in 18 msecs (HTTP/1.1 200) 2 headers in 78 bytes (1 switches on core 0)
0.8346421078513786 ID 1: 16
0.8346421078513786 ID 2: 17
Error found!!!
For anyone else facing this issue, I have found the following solution.
According to http://uwsgi-docs.readthedocs.io/en/latest/ThingsToKnow.html.
uWSGI tries to (ab)use the Copy On Write semantics of the fork() call whenever possible. By default it will fork after having loaded your applications to share as much of their memory as possible. If this behavior is undesirable for some reason, use the lazy-apps option. This will instruct uWSGI to load the applications after each worker’s fork().
After taking a look at uWSGI, Flask, sqlalchemy, and postgres: SSL error: decryption failed or bad record mac, I realised my problem was to do with the fact that multiple processes were being created.
However, because uWSGI loads all the processes from one master worker by default (and doesn't run the whole of the Flask application each time), it turns out that all the workers end up sharing a database connection (which doesn't end well!).
The solution is to include the lazy-apps parameter, which forces all the code to be run when each worker is created.