How should I use sqlalchemy session in bottle app to avoid 'Lost connection to MySQL server during query'

Question

My bottle/sqlalchemy app is hosted on pythonanywhere.com, not sure if this matters, I hope not. The app throws 'Lost connection to MySQL server during query' after I leave it for a while. Then I refresh it and it works fine. Please note that I also have the same query implemented without sqlachemy, using only MySQLdb. That implementation works fine all the time, it never throws exception, because it establishes a new connection every time. I assume, in a hosted env like pythonanywhere, I cannot fiddle with mysql config that may affect this error, e.g max_allowed_packet or timeout. How should I create the sqlalchemy engine and the session to solve this problem? bottle_app.py: ``` db_host = 'localhost' db_user = 'root' db_password = 'gggggg' db_dbname = 'test' #web framework imports from bottle import default_app, route, run, template, redirect #sqlalchemy and mysql setup mysql_connect_string = 'mysql+mysqldb://%s:%s@%s/%s?charset=utf8' % (db_user, db_password, db_host, db_dbname) from…

Accepted Answer

Can you please try it and let me know

By using `pool_recycle` I think you can over come it.

http://docs.sqlalchemy.org/en/rel_0_9/dialects/mysql.html#connection-timeouts

replace

`engine = create_engine(mysql_connect_string)`

by

```
engine = create_engine(mysql_connect_string, pool_size=100, pool_recycle=280)
```

Connection Timeouts

MySQL features an automatic connection close behavior, for connections that have been idle for eight hours or more. To circumvent having this issue, use the pool_recycle option which controls the maximum age of any connection:

engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)

How should I use sqlalchemy session in bottle app to avoid 'Lost connection to MySQL server during query'

Solution

Problem