Forums

"MySQL Connection not available"

Hi all,

I'm having a problem with my local version of my PythonAnywhere website. I'm using Flask, Flask-SQLAlchemy, and Python 3.5.2, and calls to my MySQL database that had worked in the past (although on a different laptop) are now not working, instead returning the error "MySQL Connection not available".

Somewhat bizarrely, the exact behavior I'm seeing is that when I try to log into my website the first time, the database call returns "None" for the user, even though the user is present in the database. When I then reload the login page and try again, I get sent to an error page that says "MySQL Connection not available".

I have tried running a debugger and running a query that returns all of the database records, and it returns an empty list, even though there should be a single record (my test account).

I have read the Stack Overflow and PythonAnywhere forum posts of others who have had this issue, but those fixes have not worked.

  • I already had SQLALCHEMY_POOL_RECYCLE set to a lower value than the corresponding MySQL variables (as recommended here).
  • I had the SQLALCHEMY_POOL_RECYCLE value set to 299, as recommended by the PythonAnywhere blog here.
  • Changing my MySQL wait_timeout and interactive_timeout values to be much lower (300 from something like 28800), as described here, didn't seem to have any effect.
  • Putting my queries within a try / except with a db.session.rollback() in the except clause (as described here and rec'd by harry here) does stop the app from crashing, but it still says it isn't finding the record that is clearly there.

It seems like there may be two problems here:

  • The query isn't finding the record I know is there.
  • When I try to run the query a second time, it returns the "MySQL Connection not available" error.

Has anyone else run into these issues?

Other things tried:

  • If I run the debugger and try inserting records into the database, they show up in my PyCharm database viewer.
  • Adding the line db.init_app(app) to the bottom of my app.py initialization function stops the error messages from crashing the app, but still doesn't result in the ORM finding the user record.

Alright, well, I finally got it working.

For anyone else who maybe runs into this error, here's what worked for me:

I had recently added a new column to a table, but I somehow ended up with the name of the column as "UUID", and a default of an empty string (it was a String field). I fixed those two things, dropped the schema and re-created all the tables, and everything is working now. I still don't understand why things weren't working, but at least the issue is fixed now.

Given that it was to your local machine- was the database also local? if so maybe the problem was that you had some weird query/schema etc that held a lock on the database, and then subsequent requests piled up until all sql connections were taken up, and then any more requests got that no connection available error.

"was the database also local?"

Yes.


"maybe the problem was that you had some weird query/schema etc that held a lock on the database [such that] any more requests got that no connection available error"

IIRC I had already tried dropping the schema and recreating it and the tables several times. The weird thing was that I could write to the User table just fine. It was only when I tried to read that it didn't work. When I later switched to experimenting from the command line, I noticed that if the uuid column for a particular record was an empty string, I would get an error (a different error from the ones I'd been seeing up until that point), but if I set the value to null, the User.query.all() query would work (I would get the record back).

That is puzzling. Good that you finally got it working though!