Forums

Hello,

I know this has been posted before, I read all the questions and answers on the same topic. Unfortunately, none of the answers applied to me.

To summarize:

1. I'm using flask and sqlalchemy

2. I'm using sqlite because mysql kept denying connections, and half of the requests resulted in "Something went wrong :-(" and an error from sqlalchemy about how 'mysql has gone away'. Great success, yes?

3. The database url is specified as a full path:

   os.environ['KATZ_DATABASE_URL'] = 'sqlite:///var/www/sites/katzstudio/db/katz.db'

4. The db file has access a+rwx

5. The db folder has access a+rwx. Hell, even the app folder has a+rwx rights.

6. Logging in with ssh and running commands against the database through the python shell (using the app db connection) works fine

7. Querying the database with the sqlite3 client works fine

8. Yes, the path I used with ssh and the path used by the app are the same. I used copy & paste. I also checked the logs.

9. Yes, I have restarted the app. A bazillion times. After every change.

What doesn't work, is the application. Am I missing something? And please, don't recommend using mysql unless there's a clear solution to solving the connection problems.

Hmm, that's really weird. It definitely should work, and you've done all of the checks I'd think about.

The only thing I can imagine is that because your web app has multiple worker processes (specifically, 5), they're interfering with each other's file access and so one can open the DB but the other can't. I was under the impression that SQLite could handle this kind of thing, but perhaps that's not the case.

The best thing to do with multiple workers is to use MySQL, and we definitely do have a lot of people using it with SQLalchemy and Flask. Did you try using the pool_recycle parameter when you tried it previously? See this help page. Basically, the problem is that by default SQLAlchemy assumes that idle connections will be dropped after about 20 minutes, which is a bit high -- PythonAnywhere's DB servers will drop them after 5 minutes.

Thanks for replying.

I've switched to MySQL after posting, since it was, at least, partially working. I also added the pool_recycle parameter which I didn't try before (another person posted before that it didn't work for their setup). I used:

SQLALCHEMY_POOL_RECYCLE = 299
SQLALCHEMY_POOL_TIMEOUT = 20

Which seems to work for now. Hopefully, I won't have to continue this thread :)

Now that I think about it, perhaps 299 is too high (this is what the other person is using). If the drop delay is 300s, a recycle time of 299 might occasionally reuse a dropped connection due to time synchronization problems. I'll lower it to 280s just to be safe.

299 should be fine, but 280 will work well too -- you'll be reconnecting to the database very slightly more frequently than necessary, but probably not enough to noticeably effect performance.

BTW where did you see the post where someone said it didn't work for them? There must have been some other problem interacting with this, and it would be good to make sure that the situation is clear in that thread so that other people aren't put off this solution.

This is the message I was talking about: https://www.pythonanywhere.com/forums/topic/2599/#id_post_15135

Apparently, I was mistaken. He was using a SQLALCHEMY_POOL_RECYCLE value of 499 not 299.

Thanks! I've posted at the end of that thread.