Issue: MySQL connection lost even autoconnect is enabled.
Author: Nossiac
State: closed
Created: 2019-09-04 04:19:46

Using peewee with mysql-connector.

By default, a IDLE MySQL connection will timeout after 8 hours. When timeout happens, peewee runs into an exception:

```
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/connection.py", line 809, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
peewee.OperationalError: (-1, 'MySQL Connection not available.', None)
```

I changed mysql connection timeout to 10s, so it's quite easy to reproduce.

BTW, the option `autoconnect=True` didn't work for me. In the source code I found this:
```
    def cursor(self, commit=None):
        if self.is_closed():
            if self.autoconnect:
                self.connect()
            else:
                raise InterfaceError('Error, database connection not opened.')
        return self._state.conn.cursor()
```

When timeout happens,  I get:

    self.is_closed() == False
    self._state.conn.is_connected() == False 

It seems the low-level broken connection is not detected by peewee `self.is_closed()`.
I made a dirty patch here to test `self._state.conn.is_connected()`. it's working, but I'm sure that's not the right cure.

Hope you can take a look.



Thanks, I haven't experimented too much with mysql-connector. There's a little helper class in `playhouse.mysql_ext`, but that's about it as most Peewee users stick with pymysql.

> I changed mysql connection timeout to 10s, so it's quite easy to reproduce.

Yeesh, that seems extreme. What if you have a long-running calculation during a transaction, e.g. a batch process or something?

> I made a dirty patch here to test self._state.conn.is_connected(). it's working, but I'm sure that's not the right cure.

This is definitely not the right place to fix it.

The `autoconnect`, `is_closed()`, etc., all refer to the *internal* connection state managed by peewee. e.g., autoconnect is enabled so that attempting to issue a query will establish a connection if one does not exist. Peewee leans on the driver to manage the actual underlying connection (the pool is slightly different, because that actually checks the "liveness" of connections).

So if you want automatic reconnect, I'd suggest using something like the `ReconnectMixin` from `playhouse.shortcuts`. It is designed to work with `pymysql` primarily, but could be extended for mysql-connector.

The reconnecting mixin will catch an error raised when obtaining a cursor and executing a query. It then can, depending on the error class and error message, reconnect and retry the query automatically. People use it for long-lived MySQL connections that may be closed by the server.

Usage:

```python

class ReconnectMySQLDatabase(ReconnectMixin, MySQLDatabase):
    pass

db = ReconnectMySQLDatabase(...)
```

I was able to replicate and resolve the issue by extending the ReconnectMixin list of errors to include the error raised by mysql-connector when an idle connection is killed by the server. You should now be able to use the `ReconnectMixin` from `master` and it will handle automatic re-connection.