2

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

mysql+pymysql://username:password@remote-hostname-xx.cleardb.net/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?

MattSom
  • 2,097
  • 5
  • 31
  • 53
  • Did you ever find a solution to this? I'm having the same issue as well. Thanks! – Jason Logsdon Jan 19 '20 at 19:37
  • 1
    @JasonLogsdon Hey Jason, unfortunately not yet. Maybe the solution is [here](https://docs.sqlalchemy.org/en/13/core/pooling.html#dealing-with-disconnects), but I'm not sure. – MattSom Jan 20 '20 at 22:10
  • Thanks! I'll look through there. Seems like a pretty big issue considering it's the only real mysql db heroku recommends for python. – Jason Logsdon Jan 21 '20 at 14:04

1 Answers1

4

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.

Jason Logsdon
  • 507
  • 5
  • 19
  • 1
    Wow, I'm very grateful for you to provide your findings! Unfortunately I don't have the time to work on the actual project which induced the question, but gonna get back to it in a month. I was also just thinking today about leaving heroku for something more suitable. I'd be very interested in any possible option you may find for the time being :) – MattSom Jan 24 '20 at 16:17
  • 1
    Okay, I'm back with the flask project again. I'm a bit rusty in my head how I left things, but you say pinging the DB every time worked, right? Yeah, it sounds a bit of an overkill, but have you got any slowing-down in time because of this? Also I have to ask if you've left Heroku to find something more suitable for a Flask + MySQL stack. I'm staying for Heroku at the moment just to develop further my backend. Thanks for sharing any information Jason :) – MattSom Feb 16 '20 at 10:05
  • Yup, doing the "pool_pre_ping" has worked fine, no more connection errors. My app isn't query heavy (and I hope to reduce it even more via caching later) so I haven't noticed any slowdown, though I'm still in development and not in front of a decent amount of users. – Jason Logsdon Feb 17 '20 at 12:04
  • Sorry, also still on Heroku. The baked in ssl is enough to let me stay until the dbs become a pain point. – Jason Logsdon Feb 17 '20 at 12:05