Peewee: Peewee does not automatically reconnect to MySQL

[Xandaros]

peewee does not properly handle a dropped connection to the MySQL server. In my case, I am using peewee 3.10.0 and mariadb 10.3.12.

A simple program like this:

import peewee

db = peewee.MySQLDatabase(database="test",
                          host="127.0.0.1",
                          user="test",
                          password="test")


class Test(peewee.Model):
    test = peewee.TextField()

    class Meta:
        database = db


db.connect()
db.create_tables([Test])

input("")

print(list(Test.select()))

can already fail. If you start this program and, while it is waiting for input, restart the MySQL server, the select will fail.
I would expect peewee to handle this gracefully and quietly reconnect.

In this state, db.is_closed() returns False and a db.connect() will result in an error, saying it is already connected. The only way to recover from this is to explicitly close the connection with db.close().

While the example I give here is rather contrived - you don't restart your database server all that often - I encountered this problem when my program was unable to access the database after running for about a day.

The error you get:

Traceback (most recent call last):
  File "/usr/lib/python3.7/site-packages/peewee.py", line 2683, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 516, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 727, in _read_query_result
    result.read()
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 1066, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 683, in _read_packet
    packet.check_error()
  File "/usr/lib/python3.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1927, 'Connection was killed')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "test.py", line 21, in <module>
    print(list(Test.select()))
  File "/usr/lib/python3.7/site-packages/peewee.py", line 5952, in __iter__
    self.execute()
  File "/usr/lib/python3.7/site-packages/peewee.py", line 1604, in inner
    return method(self, database, *args, **kwargs)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 1675, in execute
    return self._execute(database)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 1826, in _execute
    cursor = database.execute(self)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 2696, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 2690, in execute_sql
    self.commit()
  File "/usr/lib/python3.7/site-packages/peewee.py", line 2481, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 178, in reraise
    raise value.with_traceback(tb)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 2683, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 516, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 727, in _read_query_result
    result.read()
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 1066, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 683, in _read_packet
    packet.check_error()
  File "/usr/lib/python3.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
peewee.InternalError: (1927, 'Connection was killed')

Subsequent attempts to use the database will result in a slightly different error:

Traceback (most recent call last):
  File "/usr/lib/python3.7/site-packages/peewee.py", line 2683, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 515, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 745, 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 "test.py", line 29, in <module>
    print(list(Test.select()))
  File "/usr/lib/python3.7/site-packages/peewee.py", line 5952, in __iter__
    self.execute()
  File "/usr/lib/python3.7/site-packages/peewee.py", line 1604, in inner
    return method(self, database, *args, **kwargs)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 1675, in execute
    return self._execute(database)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 1826, in _execute
    cursor = database.execute(self)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 2696, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 2690, in execute_sql
    self.commit()
  File "/usr/lib/python3.7/site-packages/peewee.py", line 2481, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 178, in reraise
    raise value.with_traceback(tb)
  File "/usr/lib/python3.7/site-packages/peewee.py", line 2683, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 515, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/usr/lib/python3.7/site-packages/pymysql/connections.py", line 745, in _execute_command
    raise err.InterfaceError("(0, '')")
peewee.InterfaceError: (0, '')

I sometimes see a broken pipe error, too.

[coleifer]

Part of this is how the PyMySQL driver is implemented.

However, Peewee doesn't automatically reconnect to databases by default. Check out playhouse.shortcuts.ReconnectMixin if you want automatic database reconnects.

class ReconnectMySQLDatabase(ReconnectMixin, MySQLDatabase):
    pass

# Your db should now automatically attempt to reconnect for certain types of errors.
db = ReconnectMySQLDatabase(...)

[coleifer]

You may need to extend the list of reconnect_errors as an attribute on ReconnectMixin.

[Xandaros]

How is that not default? Isn't peewee meant to be easy to use?

That's why I initially chose it, at least. People said it was easy but doesn't necessarily allow for more complex queries.

That aside, I can't find anything about a ReconnectMixin in the docs. Opening the playhouse docs and searching for reconnect with ctrl-f, it comes up empty. Searching for reconnect using the search bar in the top-left, it also comes up empty...

[coleifer]

I'm sorry you were so triggered by actually needing to think about software engineering. Blindly reconnecting is never going to be the default, as far as I'm concerned.

Yeah it was removed in 3.x and re-added recently (3.8 I think). It's there if you want it, but my opinion is its the DB drivers job to manage the connection (the socket/file/etc). If pymysql doesn't handle it, try opening the bug over there.

[coleifer]

Another consideration is what if the database connection drops out mid-transaction? If Peewee were to automatically re-connect, possibly the first half of the transaction would be lost.