How to prevent a temporary table remaining in batch operation mode with SQLite3.

[c-bata]

Problem details

Not sure if this is an alembic's issue, but I encounter the problem that after performing a schema migration in batch operation mode in SQLite3, if something goes wrong and an exception occurs, only the temporary table remains halfway at optuna/optuna#4015.

I created a repository to reproduce the problem here.
https://github.com/c-bata/alembic-tmp-table-issue

In this repository, I added following script to alter table schema and then raise an exception.

def upgrade() -> None:
    # Schema Migration
    with op.batch_alter_table("foo") as batch_op:
        batch_op.alter_column("bar", nullable=True)

    # Write a data migration here like the following pattern.
    # https://alembic.sqlalchemy.org/en/latest/cookbook.html#conditional-migration-elements
    ...
    raise Exception("An exception is raised during the data migration")

When an exception is raised, Alembic will issue a ROLLBACK statement, but the tables in SQLite3 will be as follows.

$ sqlite3 alembic.db
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> .tables
_alembic_tmp_foo  alembic_version   foo

sqlite> .schema foo
CREATE TABLE foo (
	foo_id INTEGER NOT NULL,
	bar VARCHAR(512) NOT NULL,
	PRIMARY KEY (foo_id)
);

sqlite> .schema _alembic_tmp_foo
CREATE TABLE _alembic_tmp_foo (
	foo_id INTEGER NOT NULL,
	bar VARCHAR(512),
	PRIMARY KEY (foo_id)
);

_alembic_tmp_foo, a temporary table for batch operation mode, is still remaining. Therefore, this script cannot be rerun even if the data migration is fixed to avoid the exception.

$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 90823bd3809e -> 8e32f2af1bd2, Add schema & data migration
Traceback (most recent call last):
  ...
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table _alembic_tmp_foo already exists
[SQL:
CREATE TABLE _alembic_tmp_foo (
	foo_id INTEGER NOT NULL,
	bar VARCHAR(512),
	PRIMARY KEY (foo_id)
)

]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Possible Solutions and Workarounds

Automatically issues DROP TABLE _alembic_tmp_foo; statement

I don't know if this is reasonable choice, but I think it would work to automatically execute DROP TABLE _alembic_tmp_foo when exiting the batch_alter_table context manager.

Manually remove a temporary table if exists

Another possible workaround is checking the existence of _alembic_tmp_foo. It is a bit tricky though.

from alembic import op
import sqlalchemy as sa


def upgrade() -> None:
    # Workaround for the problem
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    if "_alembic_tmp_foo" in inspector.get_table_names():
        op.drop_table("_alembic_tmp_trial_values")
    
    # Schema Migration
    with op.batch_alter_table("foo") as batch_op:
        batch_op.alter_column("bar", nullable=True)
    
    # Data Migration
    raise Exception("An exception is raised during data migration")

[zzzeek]

well without transactional DDL (Which note, SQLite actually has, it's just prevented by a bug in the pysqlite driver), alembic doesn't have a generalized route to "clean up " a failed migration, this implies the migration is only halfway through and there could be any number of tables and column changes that succeeded, and now would have to be "rolled back" - without transactional DDL, there's no path to that.

The scenario you describe where a SQLite batch migration fails midway, yet the only issue is the temporary table remaining, seems contrived. what about all the tables/columns/changes that are now present and would have to be rolled back also in order to run the migration again?

all of that said your best bet is to just use transactional DDL in sqlite. the recipe at https://docs.sqlalchemy.org/en/14/dialects/sqlite.html#pysqlite-serializable will allow transactional DDL to work with sqlite. then, in your env.py script you will want to set transactional_ddl to true.

[c-bata]

@zzzeek Thank you for your explanation!
I tried the recipe you suggested and then a temporary table will be cleanup.
https://github.com/c-bata/alembic-tmp-table-issue/pull/1/files

Could you please tell me a few more things if you have time?

1. In Optuna project, we must also support MySQL and PostgreSQL. According to the documentation, the default transactional_ddl value for MySQL is False. Is there something side-effect to MySQL by making transactional_ddl true in our env.py?
2. Even if I made transactional_ddl false like this, a temporary table not remains. So the problem looks resolved. What this option (transactional_ddl=True) actually does?

[zzzeek]

OK so transaction_ddl is false, right, Alembic still runs each migration in a transaction regardless, so yes that will still take care of artifacts in the failed migration.

re: mysql, I have two (three... four?) thoughts on that. first off, mysql doesnt do transactional DDL. so if you are doing traditional migrations on MySQL/ MariaDB, meaning you're using ALTER TABLE commands, your application would not be robust against failed migrations, and there's really no way around that. if the app is deployed to customers and failed migrations + the need to transparently recover are a requirement, I would say MySQL/MariaDB are non-starters for those requirements.

So then with "batch", the "batch" mode by default only does "batch" migrations on SQLite, using regular ALTER style migrations on other backends, unless you've configured it to do batch style on all backends. overall, I would never recommend "Batch" mode for mysql/postgresql except for specific tables that have millions of rows and you're trying to do the "Facebook no downtime migration" thing, and TBH alembic's "batch" mode is not that mature to be relied upon at that level; it's much more of a one-off thing to help SQLite users. "batch" mode is not as easy to work with particularly when tables have constraints on them.

That said, if you were running batch migrations on MySQL/MariaDB, I guess, havent thought about it much, that since "Batch" doesn't actually affect the original tables until the end, doing basically a few rename operations, you can almost get batch migrations to be modestly reversible at least on a per-table basis, provided failures aren't within the actual rename part of things. Or maybe we can just say, a failed "batch" migration is easier to manually revert, on a per-table basis, than a traditional migration. So if you were trying to get failure-tolerant migrations on MySQL/MariaDB, in theory "batch" might be a path to get there. but yes, you'd need to roll your own approach towards cleaning up a failed batch migration, meaning, you'd need to look at the current table status, see how far the renames got, them manually emit any renames / drops of temporary tables to reverse that failed migration.

That approach is well within the "we absolutely have this requirement that is solid as granite, that we need remote customer-side migrations which can fail at any time and they have to be fully fault-tolerant and they have to run on MySQL". I would strongly recommend revising those requirements instead, rather than home-rolling a "revert a failed batch migration" system.