Issue: Peewee does not automatically reconnect to MySQL Error: peewee.InterfaceError: (0, '')
Author: Baloneo
State: closed
Created: 2021-08-02 02:10:27

```
class ReconnectMySQLDatabase(ReconnectMixin, MySQLDatabase):
    pass


db = ReconnectMySQLDatabase(current_config.MYSQL_DB_NAME, host=current_config.MYSQL_DB_IP,
                            port=current_config.MYSQL_DB_PORT, user=current_config.MYSQL_DB_USER,
                            password=current_config.MYSQL_DB_PASSWORD)

try:
    with db.atomic():
        _insert_into_table(data, ENERGY_TABLE_NAME_DEFAULT())
        _insert_into_table(data, ENERGY_TABLE_NAME_MONTH())
        _insert_into_table(data, ENERGY_TABLE_NAME_DAY())

except Exception as e:
    logger.exception('Error')
```

1 stop mysql server
```
docker-compose down -f mysql-docker-compose.yml
```
2 log outout
```
Traceback (most recent call last):
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/peewee.py", line 3297, in commit
    return self._state.conn.commit()
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/pymysql/connections.py", line 469, in commit
    self._execute_command(COMMAND.COM_QUERY, "COMMIT")
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/pymysql/connections.py", line 793, in _execute_command
    raise err.InterfaceError(0, "")
pymysql.err.InterfaceError: (0, '')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/peewee.py", line 4209, in __exit__
    self.commit(False)
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/peewee.py", line 4188, in commit
    self.db.commit()
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/peewee.py", line 3297, in commit
    return self._state.conn.commit()
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/peewee.py", line 2917, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/peewee.py", line 190, in reraise
    raise value.with_traceback(tb)
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/peewee.py", line 3297, in commit
    return self._state.conn.commit()
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/pymysql/connections.py", line 469, in commit
    self._execute_command(COMMAND.COM_QUERY, "COMMIT")
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/pymysql/connections.py", line 793, in _execute_command
    raise err.InterfaceError(0, "")
peewee.InterfaceError: (0, '')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/peewee.py", line 3301, in rollback
    return self._state.conn.rollback()
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/pymysql/connections.py", line 479, in rollback
    self._execute_command(COMMAND.COM_QUERY, "ROLLBACK")
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/pymysql/connections.py", line 793, in _execute_command
    raise err.InterfaceError(0, "")
pymysql.err.InterfaceError: (0, '')

```

3 start MySql service
```
docker-compose -f mysql-docker-compose.yml  up -d 
```
still insert error.

But sometimes stop mysql service, the error output is:
```
Traceback (most recent call last):
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/peewee.py", line 3080, in connect
    self._state.set_connection(self._connect())
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/peewee.py", line 3982, in _connect
    conn = mysql.connect(db=self.database, **self.connect_params)
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/home/baloneo/kexin-code/data-collection-service/.venv/lib/python3.8/site-packages/pymysql/connections.py", line 664, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '192.168.1.80' ([Errno 111] Connection refused)")

```
It can automatically reconnect.


db.close()
```
'Attempting to close database while '
peewee.OperationalError: Attempting to close database while transaction is open
```

```

class ReconnectMixin(object):
    """
    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.
    """
    reconnect_errors = (
        # Error class, error message fragment (or empty string for all).
        (OperationalError, '2006'),  # MySQL server has gone away.
        (OperationalError, '2013'),  # Lost connection to MySQL server.
        (OperationalError, '2014'),  # Commands out of sync.
        (InterfaceError, "(0, '')"),  # add error 

        # mysql-connector raises a slightly different error when an idle
        # connection is terminated by the server. This is equivalent to 2013.
        (OperationalError, 'MySQL Connection not available.'),
    )

    def __init__(self, *args, **kwargs):
        super(ReconnectMixin, self).__init__(*args, **kwargs)

        # Normalize the reconnect errors to a more efficient data-structure.
        self._reconnect_errors = {}
        for exc_class, err_fragment in self.reconnect_errors:
            self._reconnect_errors.setdefault(exc_class, [])
            self._reconnect_errors[exc_class].append(err_fragment.lower())

    def execute_sql(self, sql, params=None, commit=SENTINEL):
        try:
            return super(ReconnectMixin, self).execute_sql(sql, params, commit)
        except Exception as exc:
            exc_class = type(exc)
            if exc_class not in self._reconnect_errors:
                raise exc

            exc_repr = str(exc).lower()
            print('exc_repr', exc_repr, 'ccc', self._reconnect_errors[exc_class])
            for err_fragment in self._reconnect_errors[exc_class]:
                if err_fragment in exc_repr:
                    break
            else:
                raise exc

            if not self.is_closed():
                print('in_transaction --->', db.in_transaction())
                self.close()
                self.connect()

            return super(ReconnectMixin, self).execute_sql(sql, params, commit)
```
`self.is_closed()` is `False`, but `db.in_transaction()` is `True`, in close function:
```
    def close(self):
        with self._lock:
            if self.deferred:
                raise InterfaceError('Error, database must be initialized '
                                     'before opening a connection.')
            if self.in_transaction():
                raise OperationalError('Attempting to close database while '
                                       'transaction is open.')
            is_open = not self._state.closed
            try:
                if is_open:
                    with __exception_wrapper__:
                        self._close(self._state.conn)
            finally:
                self._state.reset()
            return is_open
```

always raise OperationalError, caused to never reconnect successfully, Is there any good solution?




The reconnecting mixin does not play well with transactions, but this should make sense to you if you think about it. What is peewee supposed to do upon reconnect? Re-execute any previous queries that were executed in the transaction?

In cases like these you probably need to handle it at the application level. Attempting to steamroll over the problems by reconnecting is not a good solution in any event.