Alembic migration errors on SQLite
We use SQLite3 as the database in SecureDrop. We use SQLAlchemy to talk the database and Alembic for migrations. Some of those migrations are written by hand.
Most of my work time in the last month went to getting things ready for
Ubuntu Focal 20.04. We currently use
Ubuntu Xenial 16.04. During this, I noticed 17
test failures related to the Alembic on Focal but works fine on Xenial. After
digging a bit more, these are due to the missing reference to temporary tables
we used during migrations. With some more digging, I found this entry on the
Compatibility Note: The behavior of ALTER TABLE when renaming a table was enhanced in versions 3.25.0 (2018-09-15) and 3.26.0 (2018-12-01) in order to carry the rename operation forward into triggers and views that reference the renamed table. This is considered an improvement. Applications that depend on the older (and arguably buggy) behavior can use the PRAGMA legacy_alter_table=ON statement or the SQLITE_DBCONFIG_LEGACY_ALTER_TABLE configuration parameter on sqlite3_db_config() interface to make ALTER TABLE RENAME behave as it did prior to version 3.25.0.
This is what causing the test failures as SQLite upgraded to
3.11.0 on Xenial.
According to the docs, we can fix the error by adding the following in the
diff --git a/securedrop/alembic/env.py b/securedrop/alembic/env.py index c16d34a5a..d6bce65b5 100644 --- a/securedrop/alembic/env.py +++ b/securedrop/alembic/env.py @@ -5,6 +5,8 @@ import sys from alembic import context from sqlalchemy import engine_from_config, pool +from sqlalchemy.engine import Engine +from sqlalchemy import event from logging.config import fileConfig from os import path @@ -16,6 +18,12 @@ fileConfig(config.config_file_name) sys.path.insert(0, path.realpath(path.join(path.dirname(__file__), '..'))) from db import db # noqa +@event.listens_for(Engine, "connect") +def set_sqlite_pragma(dbapi_connection, connection_record): + cursor = dbapi_connection.cursor() + cursor.execute("PRAGMA legacy_alter_table=ON") + cursor.close() + try: # These imports are only needed for offline generation of automigrations. # Importing them in a prod-like environment breaks things.
Later, John found an even simpler way to do the same for only the migrations impacted.