Issue: MySQL nested atomic bug in 3.X
Author: aconz2
State: closed
Created: 2018-02-01 20:07:31

I have come across a bug involving savepoints from the use of nested atomic blocks.
```
import peewee
from peewee import *
import os
import logging
logging.basicConfig(level=logging.DEBUG)

db = MySQLDatabase(
    os.getenv('db_name'),
    host       = os.getenv('db_host'),
    user       = os.getenv('db_username'),
    password   = os.getenv('db_password'),
    port       = 3306,
    autocommit = True,
    )

class MyBase(Model):
    class Meta:
        database = db

class Foo(MyBase):
    name = CharField()

print(peewee.__version__)

if not Foo.table_exists():
    Foo.create_table()

with db.atomic():
    with db.atomic():
        Foo.select().count()
```

And the output
```DEBUG:peewee:('SHOW TABLES', None)
DEBUG:peewee:('SAVEPOINT `sf880ebb457fd410e95d2053a5d173731`;', None)
DEBUG:peewee:('SELECT Count(*) FROM `foo` AS t1', [])
DEBUG:peewee:('RELEASE SAVEPOINT `sf880ebb457fd410e95d2053a5d173731`;', None)
2.10.2

DEBUG:peewee:('SHOW TABLES', None)
DEBUG:peewee:('SAVEPOINT `s288f7ba54907478e977443cab3435824`;', None)
DEBUG:peewee:('SELECT COUNT(1) FROM (SELECT 1 FROM `foo` AS `t1`) AS `_wrapped`', [])
DEBUG:peewee:('RELEASE SAVEPOINT `s288f7ba54907478e977443cab3435824`;', None)
DEBUG:peewee:('ROLLBACK TO SAVEPOINT `s288f7ba54907478e977443cab3435824`;', None)
3.0.8
Traceback (most recent call last):
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 2465, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 860, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1061, in _read_query_result
    result.read()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1349, in read
    first_packet = self.connection._read_packet()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1018, in _read_packet
    packet.check_error()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1305, 'SAVEPOINT s288f7ba54907478e977443cab3435824 does not exist')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 3293, in __exit__
    self.commit(begin=False)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 3278, in commit
    self.db.execute_sql('RELEASE SAVEPOINT %s;' % self.quoted_sid)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 2472, in execute_sql
    self.commit()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 2278, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 169, in reraise
    raise value.with_traceback(tb)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 2465, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 860, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1061, in _read_query_result
    result.read()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1349, in read
    first_packet = self.connection._read_packet()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1018, in _read_packet
    packet.check_error()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
peewee.InternalError: (1305, 'SAVEPOINT s288f7ba54907478e977443cab3435824 does not exist')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 2465, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 860, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1061, in _read_query_result
    result.read()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1349, in read
    first_packet = self.connection._read_packet()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1018, in _read_packet
    packet.check_error()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1305, 'SAVEPOINT s288f7ba54907478e977443cab3435824 does not exist')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "../test.py", line 31, in <module>
    Foo.select().count()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 3224, in __exit__
    return self._helper.__exit__(exc_type, exc_val, exc_tb)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 3295, in __exit__
    self.rollback()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 3282, in rollback
    self.db.execute_sql('ROLLBACK TO SAVEPOINT %s;' % self.quoted_sid)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 2472, in execute_sql
    self.commit()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 2278, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 169, in reraise
    raise value.with_traceback(tb)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/peewee.py", line 2465, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 860, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1061, in _read_query_result
    result.read()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1349, in read
    first_packet = self.connection._read_packet()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 1018, in _read_packet
    packet.check_error()
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/andrew/.local/share/virtualenvs/3--EvudULI/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
peewee.InternalError: (1305, 'SAVEPOINT s288f7ba54907478e977443cab3435824 does not exist')
```

I tried tracking down the culprit but haven't figured anything out so far.

I'm not sure about all that... This stuff is all tested (with sqlite, mysql and postgres):

https://github.com/coleifer/peewee/blob/41180d6354adcb61280bd4cbf122bd1e5aaa3154/tests/transactions.py#L9-L48

I whipped up a test-case and ran it using Python 3.6 and pymysql, connected to MariaDB 10.0.33. Test passes:

```python
    def test_bare(self):
        with db.atomic():
            with db.atomic():
                self.assertEqual(Register.select().count(), 0)

        try:
            with db.atomic():
                with db.atomic():
                    self.assertEqual(Register.select().count(), 0)
                    4 / 0
        except ZeroDivisionError:
            pass
```

On a closer look, why are you setting `autocommit=True` in your database constructor? Have you tried removing that?

Issue resolved removing `autcommit=True`. I inherited this code so I didn't think twice about that. I'm guessing they meant that to be for peewee and not the underlying database connection.

Thank you!

Ah yes! That's actually a very good point. Peewee removed that parameter in 3.0 so now it's getting passed to the underlying driver. I'll add a warning for this and push a new release so it doesn't bite anyone else. Thank you so much for pointing that out.