Issue: With db.atomic decorator, ReconnectMixin does not automatically reconnect
Author: breezechen
State: closed
Created: 2022-10-12 06:53:19

Steps to reproduce the issue:

1. change mysql `wait_timeout` and `interactive_timeout` to 5.
```
version: '3.1'

services:
  db:
    image: mysql
    restart: always
    environment:
      - LANG=C.UTF-8
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_DATABASE=testapp

    volumes:
      - /etc/localtime:/etc/localtime:ro
    ports:
      - 3307:3306
    command:
      - "--default-authentication-plugin=mysql_native_password"
      - "--character-set-server=utf8mb4"
      - "--collation-server=utf8mb4_general_ci"
      - "--explicit_defaults_for_timestamp=true"
      - "--lower_case_table_names=1"
      - "--wait_timeout=5"
      - "--interactive_timeout=5"
```
2.  run the code
```
from time import sleep

from peewee import Model, TextField
from playhouse.pool import PooledMySQLDatabase
from playhouse.shortcuts import ReconnectMixin


class ReconnectMySQLDatabase(ReconnectMixin, PooledMySQLDatabase):
    pass


db = ReconnectMySQLDatabase(
    database="testapp",
    host="127.0.0.1",
    port=3307,
    user="root",
    password="root",
    max_connections=2,
)


class Test(Model):
    test = TextField()

    class Meta:
        database = db


@db.atomic()
def issue_func():
    Test.create(test="test1")
    Test.create(test="test2")


def main():
    db.connect()
    db.create_tables([Test])
    while True:
        sleep(5)
        issue_func()


if __name__ == "__main__":
    main()
```

3. wait 5 seconds, it will crash...
```
Traceback (most recent call last):
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 3221, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 701, in _read_packet
    raise err.OperationalError(
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/playhouse/shortcuts.py", line 250, in execute_sql
    return super(ReconnectMixin, self).execute_sql(sql, params, commit)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 3228, in execute_sql
    self.commit()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 2994, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 191, in reraise
    raise value.with_traceback(tb)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 3221, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 701, in _read_packet
    raise err.OperationalError(
peewee.OperationalError: (2013, 'Lost connection to MySQL server during query')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 412, in inner
    return fn(*args, **kwargs)
  File "/home/breeze/projects/github/peewee-issue/issue.py", line 31, in issue_func
    Test.create(test="test1")
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 6508, in create
    inst.save(force_insert=True)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 6718, in save
    pk = self.insert(**field_dict).execute()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 1946, in inner
    return method(self, database, *args, **kwargs)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 2017, in execute
    return self._execute(database)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 2822, in _execute
    return super(Insert, self)._execute(database)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 2535, in _execute
    cursor = database.execute(self)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 3234, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/playhouse/shortcuts.py", line 264, in execute_sql
    self.close()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 3175, in close
    raise OperationalError('Attempting to close database while '
peewee.OperationalError: Attempting to close database while transaction is open.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 3378, in rollback
    return self._state.conn.rollback()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 479, in rollback
    self._execute_command(COMMAND.COM_QUERY, "ROLLBACK")
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/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/breeze/projects/github/peewee-issue/issue.py", line 44, in <module>
    main()
  File "/home/breeze/projects/github/peewee-issue/issue.py", line 40, in main
    issue_func()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 412, in inner
    return fn(*args, **kwargs)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 4313, in __exit__
    return self._helper.__exit__(exc_type, exc_val, exc_tb)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 4344, in __exit__
    self.rollback(False)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 4331, in rollback
    self.db.rollback()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 3378, in rollback
    return self._state.conn.rollback()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 2994, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 191, in reraise
    raise value.with_traceback(tb)
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/peewee.py", line 3378, in rollback
    return self._state.conn.rollback()
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 479, in rollback
    self._execute_command(COMMAND.COM_QUERY, "ROLLBACK")
  File "/home/breeze/projects/github/peewee-issue/venv/lib/python3.9/site-packages/pymysql/connections.py", line 793, in _execute_command
    raise err.InterfaceError(0, "")
peewee.InterfaceError: (0, '')
```


The behavior of peewee could certainly be better here, but what is the correct thing to do if a connection dies midway through a transaction? Important changes would be lost with no indication given to the caller. I've added a patch that will instead raise the exception without reconnecting if Peewee is in a transaction, that way the caller can handle the situation appropriately.

Got it, thanks.