Add support for MySQL connection recycling

[arnuschky]

We're getting the following error on our flask project:

  SQLError: (OperationalError) (2006, ‘MySQL server has gone away’)

This problem was implicitly mentioned in #222 for example.

SQLalchemy addresses the problem as follows: pallets-eco/flask-sqlalchemy@05c5193
Associated issue: pallets-eco/flask-sqlalchemy#2

Would it be possible to implement this in PeeWee as well?

[arnuschky]

Now that I think of it, this issue maybe more appropriate for flask-peewee, but I am not sure.

[coleifer]

I'm a little unclear on how your particular issue is arising. If you're using flask-peewee's Database object, it should be opening and closing the connection on each request. If you're using a multi-threaded wsgi server have you tried configuring your database with threadlocals=True? That would eliminate the possibility that a second thread is closing the conn opened by the first thread.

[arnuschky]

Yes, we are using gunicorn with multiple workers and without threadlocals=True. Must have missed it in the docs (now I even found another old issue regarding this)! I guess that his issue is invalid then, but I will test first.

[beanyoung]

You could use sql_error_handler to handle this problem. Anyway, this problem is caused by the connection is idle for a long time(longer than timeout seconds, default 28800 seconds)

[coleifer]

@arnuschky let me know if threadlocals resolved it!

[arnuschky]

It seems to resolve the problem. Thanks!

[erigo]

threadlocals=True can not resolved this issue~
after 28800 seconds, Error Msg ‘MySQL server has gone away’ appear agian!

---

read peewee doc again, use mysql_db.connect() & mysql_db.close() for each request in CherryPy.

[coleifer]

You can also check out http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#pool for connection pooling and recycling.

[erigo]

Thanks for your big help. playhouse.pool is the final solution which I needed~

[serkandaglioglu]

I use peewee with flask.

Peewee database connection :

db = PooledMySQLDatabase(database,**{
            "passwd": password, "user": user,
            "max_connections":None,"stale_timeout":None,
            "threadlocals" : True
        })

@app.before_request
def before_request():
    db.connect()

@app.teardown_request
def teardown_request(exception):
    db.close()

After mysql error that "MySQL server has gone away (error(32, 'Broken pipe'))", select queries works without problem, but insert,update,delete queries don't work.

On insert,update,delete queries works behind(in mysql) but peewee throw this errors.

(2006, "MySQL server has gone away (error(32, 'Broken pipe'))")

[coleifer]

That's odd, not sure what that might mean.

[serkandaglioglu]

@coleifer I have two different applications with peewee and flask. They have same problem. Select queries has no problem but insert,update,delete always throws error which above. I will do test with an basic peewee and flask app and I will write the result of the test.