Issue: Mysql error 2014 and 2006
Author: LeoDT
State: closed
Created: 2012-04-01 10:49:06

I'm building a small site using peewee and bottle.py.
And I got Mysql error 2014 and 2006 frequently either develop env(windows) or product env(linux). 

I connect mysql before every request, but don't close it. Does this matter?

Mysql error 2014 is "Commands out of sync; you can't run this command now" and 2006 is "MySQL server has gone away".


hey.....


Try closing your connections.  Is your server multithreaded by chance?


If I close the connections, this problem seems solved.
But is it good to reconnect mysql every request?

Multithreaded by chance? How to check this?


Yeah you should not reuse your conns across requests.  Use a connection pooler if you're concerned about performance.  Django opens a connection per request, fwiw.


In case you're interested, I've got a mixin for retrying failed queries in 017e4e4952f0b429dd21f0e90465a0f644cf6015 . Docs:

http://docs.peewee-orm.com/en/latest/peewee/database.html#automatic-reconnect


I know its a very old post but, I cant seem to find this class in latest documentation. 
Was this removed for some reason? (automatic-reconnect)

Yes, but you can implement it yourself. Perhaps I'll add it back, since this trips up a lot of MySQL users -- particularly error 2006.

The thing is...this error indicates that you have a long-lived idle connection. MySQL closes idle connections after a certain time (which I think is configurable). So it may be a problem with the way your application is using db connections.

I've reimplemented it: af914ccad7d09c070000da4153f88aef69b6e44f

You can now use the `ReconnectMixin` along with your `MySQLDatabase` to get auto-reconnect logic for certain types of errors.

Thank you ! :) 

I found that you've re-added a `ReconnectMixin` in the playhouse, but no docs on that was found in the documentation site. I came across that by your answers on stackoverflow. Could you please:

1. Add docs.
2. Tell us when to use ReconnectMixin and when to use a connection pool?

It is not documented in the online docs because its use is not recommended... it is available because MySQL users seem to run into the same problem frequently but are ill-equipped to find a more correct solution.

The class itself has a docstring, though. Hopefully it is clear:

```python
    """
    Mixin class that attempts to automatically reconnect to the database under
    certain error conditions.

    For example, MySQL servers will typically close connections that are idle
    for 28800 seconds ("wait_timeout" setting). If your application makes use
    of long-lived connections, you may find your connections are closed after
    a period of no activity. This mixin will attempt to reconnect automatically
    when these errors occur.

    This mixin class probably should not be used with Postgres (unless you
    REALLY know what you are doing) and definitely has no business being used
    with Sqlite. If you wish to use with Postgres, you will need to adapt the
    `reconnect_errors` attribute to something appropriate for Postgres.
    """
```

The jist of it is that [MySQL server may terminate an idle connection after a certain timeout has elapsed](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout). An idle connection typically indicates your program has failed to clean up resources. This is mentioned in the peewee docs [here](http://docs.peewee-orm.com/en/latest/peewee/database.html#error-2006-mysql-server-has-gone-away) and [here](http://docs.peewee-orm.com/en/latest/peewee/database.html#framework-integration). Automatically reconnecting and retrying the query that was rejected seems like a recipe for trouble to me.

The connection pool is used when you want to avoid the latency introduced by frequent opening/closing of connections - e.g., a web-app. That's pretty much it.