CODEWITHSUNDEEP
pythonherokuflask-sqlalchemypymysqlcleardb
MattSom
MattSom

Reputation: 2377

pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

I had problems connecting to the heroku ClearDB with my flask-restful application, using SQLAlchemy with my given DB_URI:

mysql+pymysql://username:[email protected]/heroku_c52490fb3111cda?reconnect=true

And this was the error:

app[web.1]:     return Connection(*args, **kwargs)
app[web.1]: TypeError: __init__() got an unexpected keyword argument 'reconnect'

The solution was to remove the ?reconnect=true parameter. But there where also warnings that this could lead to connection losing issues, and indeed, it instantly happened to me as well:

2019-11-01T11:00:28.244117+00:00 app[web.1]:     CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
2019-11-01T11:00:28.244119+00:00 app[web.1]: pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')
2019-11-01T11:00:28.244122+00:00 app[web.1]: 
2019-11-01T11:00:28.244124+00:00 app[web.1]: The above exception was the direct cause of the following exception:
    .
    .
    .
2019-11-01T11:00:28.244267+00:00 app[web.1]:     CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
2019-11-01T11:00:28.244268+00:00 app[web.1]: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')
2019-11-01T11:00:28.24427+00:00 app[web.1]: [SQL: SELECT users.id AS users_id, users.email AS users_email, users.password AS users_password, users.`admin` AS users_admin 
2019-11-01T11:00:28.244272+00:00 app[web.1]: FROM users]
2019-11-01T11:00:28.244349+00:00 app[web.1]: (Background on this error at: http://sqlalche.me/e/e3q8)
2019-11-01T11:00:28.245066+00:00 app[web.1]: 10.45.155.176 - - [01/Nov/2019:11:00:28 +0000] "GET /users HTTP/1.1" 500 0 "-" "-"

For the first try I get every time a 500: internal server error. The second try is usually sending back the 200: ok code, but this behaviour is naturally breaking the service of my API.

Checking the logs, the error is clear:

(pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')

Now, I've also got a tip to use SQLAlchemy connection pooling to fix the issue.

My question is: How could I resolve this with SQLAlchemy pooling or should I even really use that to fix this reconnection error?

Upvotes: 3

Views: 3608

Answers (1)

Jason Logsdon
Jason Logsdon

Reputation: 517

Well, the only work around I could find for this by talking to the ClearDB people is to add in the pessimistic ping when creating the engine. For my initialization I now use:

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.pool import QueuePool
db = SQLAlchemy(engine_options={"pool_size": 10, "poolclass":QueuePool, "pool_pre_ping":True})

It's obviously not an ideal solution since it pings the DB every time before you do a query. After these discussions I don't know if it's even possible to use mysql with Flask on heroku in a semi-optimized manner, especially using their "recommended" database provider.

I'm going to roll with this for now since I don't think my app will be query heavy, and I'm in early stages of development. Long term I may just end up switching off of Heroku entirely if I start to run into longer term issues from this.

Upvotes: 4

Related Questions