Issue: peewee.InterfaceError: (0, '') | pymysql.err.InterfaceError: (0, '')
Author: MozzieHan
State: closed
Created: 2018-03-21 07:26:23

I am encountering this exception (using peewee-async high-level api) then my asyncio based daemon is idle (about 15 minutes or so), after that time, this exception occurs, I have tried to close and reopen connection, and was surprised that Manager (high-level api) did not track its state (close/reopen is done using its methods).

```
Traceback (most recent call last):
  File "/home/ubuntu/dati-server/match/manager.py", line 56, in clean_up_user
    await exam_end_all_handler(current_match_id, user_id)
  File "/home/ubuntu/dati-server/match/manager.py", line 407, in exam_end_all_handler
    match_result = await MatchUserRecord.set_user_score_and_winner(match_id)
  File "/home/ubuntu/dati-server/models/record.py", line 260, in set_user_score_and_winner
    score1 = await user_record1.get_match_score()
  File "/home/ubuntu/dati-server/models/record.py", line 108, in get_match_score
    answer_records = await objects.execute(UserAnswerRecord.filter(match_id=self.match.id, user_id=self.user_id))
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 1386, in __get__
    return self.get_object_or_id(instance)
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 1377, in get_object_or_id
    obj = self.rel_model.get(self.field.to_field == rel_id)
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 4988, in get
    return sq.get()
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 3220, in get
    return next(clone.execute())
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 3274, in execute
    self._qr = ResultWrapper(model_class, self._execute(), query_meta)
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 2939, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee_async.py", line 1041, in execute_sql
    return super().execute_sql(*args, **kwargs)
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 3837, in execute_sql
    self.commit()
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 3656, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 135, in reraise
    raise value.with_traceback(tb)
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/peewee.py", line 3830, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/pymysql/connections.py", line 859, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/home/ubuntu/dati-server/env/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in _execute_command
    raise err.InterfaceError("(0, '')")
peewee.InterfaceError: (0, '')
```

This looks like a bug in peewee-async or something? Some server issue?

Peewee is tested with mysql every release (and every commit for that matter) so I know mysql isn't horribly broken or anything.

The follow up is at https://github.com/05bit/peewee-async/issues/26

We encounter the same problem with peewee with use recent `flask` version 1.0.2 without relying on `flask-peewee`.

We do not open/close connection between each request, I was expecting to be able to reuse the connection across request.

We do not use the connection pool.

I think we found the issue. Looking up earlier log, we noticed mysqld server closed the only connection we had and we did not reconnect after. 

ref: http://docs.peewee-orm.com/en/latest/peewee/database.html#error-2006-mysql-server-has-gone-away
ref: http://docs.peewee-orm.com/en/latest/peewee/database.html#flask

Ty!

Hi,
 I have the same issue but I'm not using any framework. 
I'm in a asyncio method.

```
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 756, in _write_bytes
    self._sock.sendall(data)
ConnectionResetError: [Errno 104] Connection reset by peer

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/peewee.py", line 3144, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 547, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 814, in _execute_command
    self._write_bytes(packet)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 759, in _write_bytes
    raise err.OperationalError(
pymysql.err.OperationalError: (2006, "MySQL server has gone away (ConnectionResetError(104, 'Connection reset by peer'))")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/discord/client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "/app/my_discord_client.py", line 96, in on_member_update
    GameSessionManager.handle_user_update(before, after)
  File "/app/game_session_manager.py", line 47, in handle_user_update
    target_user = cls.get_or_create_user(user_id=after.id, name=after.name)
  File "/app/game_session_manager.py", line 18, in get_or_create_user
    target_user = DiscordUser.get(id=user_id)
  File "/usr/local/lib/python3.8/site-packages/peewee.py", line 6438, in get
    return sq.get()
  File "/usr/local/lib/python3.8/site-packages/peewee
```


The connection was reset - presumably your connection was idle and mysql closed it after a given amount of time elapsed. Check the reconnectmixin if you want to try automatically reconnecting:

https://github.com/coleifer/peewee/blob/master/playhouse/shortcuts.py#L174

Actually I wrapped at first the database loading with a Singleton pattern. But it not a good idea I presume :)

It was like this:
```python
from peewee import MySQLDatabase

from settings_loader import SettingLoader


class Singleton(type):
    _instances = {}

    def __call__(cls, *args, **kwargs):
        if cls not in cls._instances:
            cls._instances[cls] = super(Singleton, cls).__call__(*args, **kwargs)
        return cls._instances[cls]


class DatabaseLoader(Singleton, object):

    @classmethod
    def get_database(cls, name="bot"):
        settings = SettingLoader().settings
        return MySQLDatabase(database=name,
                             host=settings.database_host,
                             user=settings.database_user,
                             passwd=settings.database_password)
```

I switched to this. We'll see if it's better
```python
class DatabaseLoader(object):

    @classmethod
    def get_database(cls, name="bot"):
        settings = SettingLoader().settings
        return PooledMySQLDatabase(
            database=name,
            max_connections=10,
            stale_timeout=300,
            host=settings.database_host,
            user=settings.database_user,
            passwd=settings.database_password)
```


The database is already a singleton instance, so this is redundant. Also using the pool won't help you.

You think my problem is somewhere else? Removing the singleton wont help?

>I'm in a asyncio method.

I can't debug your code for you, but connection management is straightforward in a non-async or threaded environment. The db (which, yes, is a singleton) maintains a connection-per-thread automatically - this also works for gevent which monkeypatches threadlocal. In asyncio, you would probably want to manage a connection-per-async-context but you're on your own in terms of figuring that out. With the db, you need to call connect and close the connection when you're done with it - its all documented http://docs.peewee-orm.com/en/latest/peewee/database.html#connection-management

Thanks a lot for all those info. I'll try to figure out where it's wrong.


I have the same problem too with fastapi, however I have added the `ReconnectMixin`:

```python3
class DB(ReconnectMixin, MySQLDatabase):
    pass

db = DB(host=Config.DB_HOST, port=Config.DB_PORT, user=Config.DB_USER, passwd=Config.DB_PWD, database=Config.DB_NAME)
```

And added the automatic retry into the middleware:
```python3
async def catch_exceptions_middleware(request: Request, call_next):
    try:
        if db.is_closed():
            db.connect()
        return await call_next(request)
    except Exception:
        logger.error('error occurs, err=%s' % traceback.format_exc())
        return JSONResponse()
```

However, the InterfaceError still occurs, and I wondering why it doesn't work.

@paulzhn my reply above https://github.com/coleifer/peewee/issues/1546#issuecomment-832784804 describes some of the difficulty with async and connection management.

Additionally you may need to extend the list of errors the reconnect mixin catches.

> @paulzhn my reply above [#1546 (comment)](https://github.com/coleifer/peewee/issues/1546#issuecomment-832784804) describes some of the difficulty with async and connection management.
> 
> Additionally you may need to extend the list of errors the reconnect mixin catches.

```python3
  File "/home/paul/.local/lib/python3.8/site-packages/peewee.py", line 3144, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/paul/.local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/home/paul/.local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/home/paul/.local/lib/python3.8/site-packages/pymysql/connections.py", line 547, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/home/paul/.local/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/paul/code/communist_backend/utils/common.py", line 65, in f
    data, code = func(*args, **kwargs)
  File "/home/paul/code/communist_backend/api/available_school_branch.py", line 20, in post
    for i in result:
  File "/home/paul/.local/lib/python3.8/site-packages/peewee.py", line 6874, in __iter__
    self.execute()
  File "/home/paul/.local/lib/python3.8/site-packages/peewee.py", line 1907, in inner
    return method(self, database, *args, **kwargs)
  File "/home/paul/.local/lib/python3.8/site-packages/peewee.py", line 1978, in execute
    return self._execute(database)
  File "/home/paul/.local/lib/python3.8/site-packages/peewee.py", line 2150, in _execute
    cursor = database.execute(self)
  File "/home/paul/.local/lib/python3.8/site-packages/peewee.py", line 3157, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/paul/.local/lib/python3.8/site-packages/peewee.py", line 3151, in execute_sql
    self.commit()
  File "/home/paul/.local/lib/python3.8/site-packages/peewee.py", line 2917, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/paul/.local/lib/python3.8/site-packages/peewee.py", line 190, in reraise
    raise value.with_traceback(tb)
  File "/home/paul/.local/lib/python3.8/site-packages/peewee.py", line 3144, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/paul/.local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/home/paul/.local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/home/paul/.local/lib/python3.8/site-packages/pymysql/connections.py", line 547, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/home/paul/.local/lib/python3.8/site-packages/pymysql/connections.py", line 793, in _execute_command
    raise err.InterfaceError(0, "")
peewee.InterfaceError: (0, '')
```

code:
```python3
class ReconnectDB(MySQLDatabase, ReconnectMixin):
    pass

class Ext:
    db = ReconnectDB(
        Config.DB_NAME,
        host=Config.DB_HOST,
        port=Config.DB_PORT,
        user=Config.DB_USER,
        passwd=Config.DB_PWD
    )
```

My another project with flask and peewee also occurs the problem, the traceback is as above. I think it might not be related to async?

Are you using multiprocessing?

On my side I still have the issue with the `PooledMySQLDatabase`.
I'm using multiprocessing. Another lib receive asyncio events and then preform insertion in the database. So multiple insert can appear at the same time.




If you're using multiprocessing, you need to be sure you're not opening connections until after you fork a new process. See also caveats about asyncio above.

Actually no, It's not multiprocessing. I thought it was but asyncio is actually a coroutine and so using the same process. 
I've added the decorator `@db.connection_context()` to my method but still. I have now the same error as @paulbooth `peewee.InterfaceError: (0, '')`.

I'll try to create my own wrapper to ping and reconnect like described [here](https://stackoverflow.com/questions/56319791/finding-source-of-pymysql-error-err-interfaceerror0).


I'd suggest carefully debugging this because it almost certainly indicates connection mis-management somewhere in the call-stack. This can be especially true with asyncio, e.g. https://github.com/coleifer/peewee/issues/1546#issuecomment-832784804

Can we reopen the issue? I came across the same problem in my personal project using sanic(asyncio) + peewee.

Restarting the server solved the problem.

As I've said before, I'm quite confident this is always attributable to user error or mismanagement of the connection lifecycle.

* https://github.com/coleifer/peewee/issues/1546#issuecomment-864622366
* https://github.com/coleifer/peewee/issues/1546#issuecomment-832674172