Issue: Calling db.atomic() for a transaction with a new or existing database fails
Author: rmskinsa
State: closed
Created: 2020-07-06 19:16:55

In `my_models.py`:
```
class RegionType(db.Model):
    name = CharField(unique=True, null=False)
```

In `my_flask_app.py`:
```
db = MySQLDatabase(
    app.config.get('DATABASE_NAME'),
    user=app.config.get('DATABASE_USER'),
    password=app.config.get('DATABASE_PASSWORD'),
    host=app.config.get('DATABASE_HOST'),
    port=int(app.config.get('DATABASE_PORT'))
)
```

In `migrations.py`:
```
from my_models import RegionType
from my_flask_app import db, app

with db.atomic() as transaction:
    try:
        all_region_type = RegionType.create(name='all')
    except Exception as e:
        transaction.rollback()
        app.logger.error(e)
```

I spin the database up in a Docker container. The docker-compose for that looks like:
```
  local_db:
    container_name: local_db
    image: mysql:5.7
    restart: always
    environment:
        MYSQL_ROOT_PASSWORD: xxxxx
        MYSQL_DATABASE: xxxxx
        MYSQL_USER: xxxxx
        MYSQL_PASSWORD: xxxxx
    ports:
      - '3306:3306'
    expose:
      - '3306'
    volumes:
      - ./local_db_data:/var/lib/mysql
```

Have confirmed my application can talk to/interact with the database using the config info given above and that the database is running. When I run the migration above I get:
```
  File "/Users/ryanschuster/envs/DataSvc-jeQi5N6I/lib/python3.6/site-packages/peewee.py", line 3099, in execute_sql
    cursor.execute(sql, params or ())
  File "/Users/ryanschuster/envs/DataSvc-jeQi5N6I/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/Users/ryanschuster/envs/DataSvc-jeQi5N6I/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/Users/ryanschuster/envs/DataSvc-jeQi5N6I/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/Users/ryanschuster/envs/DataSvc-jeQi5N6I/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/Users/ryanschuster/envs/DataSvc-jeQi5N6I/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/Users/ryanschuster/envs/DataSvc-jeQi5N6I/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/Users/ryanschuster/envs/DataSvc-jeQi5N6I/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/Users/ryanschuster/envs/DataSvc-jeQi5N6I/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1305, 'SAVEPOINT s1d7433f1e863451e99871f44bdaa795c does not exist')
```
I see an unanswered 4-year-old Stack Overflow question about this: https://stackoverflow.com/questions/37870898/peewee-savepoint-does-not-exist

When searching for "mysql savepoint does not exist" I'm led to: https://stackoverflow.com/a/19672316

I also saw [this](https://stackoverflow.com/a/42264410) SO post about another way to do transactions with PeeWee. But `set_autocommit` doesn't appear to be an option on `db` anymore as it gives me an error.

Any ideas what's causing this? It _seems_ like a bug to me as I'm doing this exactly the way it's stated in the [docs](https://docs.peewee-orm.com/en/3.4.0/peewee/transactions.html#transactions). But I could've borked something somewhere along the line.

MySQL does not support transactional DDL. If you had checked your database first, you probably would have found:

https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

Note the big **NOTE** section at the top.

For reference, Peewee is tested across all databases, including the use of transactions and savepoints...

Wow, I didn't realize MySQL didn't automatically allow rollbacks! Looks like one must specifically disable autocommit as per [here](https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html).

Digging more in your SO post, I found this link for how to disable auto-commit: http://docs.peewee-orm.com/en/3.6.0/peewee/database.html?highlight=autocommit#autocommit-mode. Will give that a shot. I just want to note that the language there is a bit misleading. "To group multiple statements into a transaction, Peewee provides the atomic() context-manager/decorator. This should cover all use-cases ..." and "Again – I don’t anticipate anyone needing this, but it’s here just in case." aren't entirely accurate since lots of people use MySQL, and it looks like this work-around is required if they want the ability to roll back their commits. Or perhaps I'm missing something? Additionally, PeeWee's documentation doesn't call out that the `db.atomic()` feature being unsupported by MySQL. It may be helpful to note that in the "Transactions" section linked to in the initial post above and to provide the work-around of using `db.manual_commit()`.

I think you're misunderstanding completely.

The point is that mysql does not allow transactions and DDL. It looks like somewhere in code (probably not included in this example) you're creating tables inside an `atomic()` block.

MySQL works fine with transactions, savepoints, commits and rollbacks, etc, etc., which is covered thoroughly in the tests as well.