alembic_version never gets updated

[Olamyy]

Describe the bug
I have a mysql database using alembic for migrations.
Everything seems fine until I try to upgrade my schema with alembic upgrade head. This does everything it should(create new tables) except it doesn't add the revision number in the alembic_version. This leads to a loop where it tries to recreate existing tables when I rerun the migration. Generating a new revision also doesn't work because the database is never up to date (again because the alembic_version table doesn't get updated).
To be sure I'm doing everything right, I run alembic upgrade head --sql and the sql actually has the query to insert into the table but for some reason, it never does.

CREATE TABLE alembic_version (
    version_num VARCHAR(32) NOT NULL, 
    CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);

INFO  [alembic.runtime.migration] Running upgrade  -> 02400877007f, Added initial table
-- Running upgrade  -> 02400877007f

CREATE TABLE model (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    name VARCHAR(200) NOT NULL, 
    version VARCHAR(50), 
    description_url VARCHAR(500), 
    PRIMARY KEY (id), 
    UNIQUE (name, version)
);

CREATE INDEX ix_model_name ON model (name);

CREATE TABLE content_cosine_similarity_score (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    translation_id INTEGER NOT NULL, 
    score FLOAT NOT NULL, 
    model_id INTEGER, 
    PRIMARY KEY (id), 
    FOREIGN KEY(model_id) REFERENCES model (id)
);

INSERT INTO alembic_version (version_num) VALUES ('02400877007f');

Summary:

1. Running alembic upgrade head upgrades the database schema to the expected state.

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 02400877007f, Added initial table

2. It does not update the alembic_version table.

Expected behavior
The alembic_version table should be updated.

[zzzeek]

hi -

if you enable INFO logging for the sqlalchemy.engine logger in your alembic.ini, the SQL being emitted will be shown when you run the migrations for real, that is, not using the --sql option. You want to see that the INSERT into alembic_version occurs, that it's in the correct database that you intend, and then also that you see a COMMIT occur afterwards so that the transaction is committed.

things to look at here include how the env.py file has been changed if at all, which may affect if the transaction is not being completed, as well as database connectivity, where the database that's being connected towards from create_engine() is in fact the same database that you see when you look at your MySQL console.

[Olamyy]

Hi @zzzeek , thanks for the reply.
I enabled sqlalchemy logging for alembic and here is what my logs look like.

INFO  [sqlalchemy.engine.Engine] SELECT DATABASE()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] SELECT @@sql_mode
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] SELECT @@lower_case_table_names
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [sqlalchemy.engine.Engine] SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %(table_schema)s AND table_name = %(table_name)s
INFO  [sqlalchemy.engine.Engine] [generated in 0.00008s] {'table_schema': 'test_database', 'table_name': 'alembic_version'}
INFO  [sqlalchemy.engine.Engine] SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %(table_schema)s AND table_name = %(table_name)s
INFO  [sqlalchemy.engine.Engine] [cached since 0.001815s ago] {'table_schema': 'test_database', 'table_name': 'alembic_version'}
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE alembic_version (
	version_num VARCHAR(32) NOT NULL, 
	CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00004s] {}
INFO  [alembic.runtime.migration] Running upgrade  -> 4da9af4cbb4d, create tables
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE model (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(200) NOT NULL, 
	version VARCHAR(50), 
	description_url VARCHAR(500), 
	PRIMARY KEY (id), 
	UNIQUE (name, version)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00004s] {}
INFO  [sqlalchemy.engine.Engine] CREATE INDEX ix_model_name ON model (name)
INFO  [sqlalchemy.engine.Engine] [no key 0.00004s] {}
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE content_cosine_similarity_score (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	translation_id INTEGER NOT NULL, 
	score FLOAT NOT NULL, 
	model_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(model_id) REFERENCES model (id)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00006s] {}
INFO  [sqlalchemy.engine.Engine] INSERT INTO alembic_version (version_num) VALUES ('4da9af4cbb4d')
INFO  [sqlalchemy.engine.Engine] [generated in 0.00008s] {}
INFO  [alembic.runtime.migration] Running upgrade 4da9af4cbb4d -> 22ebe3902746, Added scored
INFO  [sqlalchemy.engine.Engine] UPDATE alembic_version SET version_num='22ebe3902746' WHERE alembic_version.version_num = '4da9af4cbb4d'
INFO  [sqlalchemy.engine.Engine] [generated in 0.00006s] {}

There doesn't seem to be a COMMIT command in there.

Not sure what I'm missing but here's my env.py

from logging.config import fileConfig

from alembic import context
from src.database.db_schema import Base
from src.utils.db import get_sql_driver_url, get_db_session

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.

config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata

target_metadata = Base.metadata


# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = get_sql_driver_url()
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = get_db_session(get_sql_driver_url()).connection()

    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Here's the relevant alembic.ini

# A generic, single database configuration.

[alembic]
# path to migration scripts
script_location = migrations

# template used to generate migration files
# file_template = %%(rev)s_%%(slug)s

# sys.path path, will be prepended to sys.path if present.
# defaults to the current working directory.
prepend_sys_path = .

# timezone to use when rendering the date within the migration file
# as well as the filename.
# If specified, requires the python-dateutil library that can be
# installed by adding `alembic[tz]` to the pip requirements
# string value is passed to dateutil.tz.gettz()
# leave blank for localtime
# timezone =

# max length of characters to apply to the
# "slug" field
# truncate_slug_length = 40

# set to 'true' to run the environment during
# the 'revision' command, regardless of autogenerate
# revision_environment = false

# set to 'true' to allow .pyc and .pyo files without
# a source .py file to be detected as revisions in the
# versions/ directory
# sourceless = false

# version location specification; This defaults
# to migrations/versions.  When using multiple version
# directories, initial revisions must be specified with --version-path.
# The path separator used here should be the separator specified by "version_path_separator" below.
# version_locations = %(here)s/bar:%(here)s/bat:migrations/versions

# version path separator; As mentioned above, this is the character used to split
# version_locations. The default within new alembic.ini files is "os", which uses os.pathsep.
# If this key is omitted entirely, it falls back to the legacy behavior of splitting on spaces and/or commas.
# Valid values for version_path_separator are:
#
# version_path_separator = :
# version_path_separator = ;
# version_path_separator = space
version_path_separator = os  # Use os.pathsep. Default configuration used for new projects.

# the output encoding used when revision files
# are written from script.py.mako
# output_encoding = utf-8

# sqlalchemy.url = driver://user:pass@localhost/dbname


[post_write_hooks]
# post_write_hooks defines scripts or Python functions that are run
# on newly generated revision scripts.  See the documentation for further
# detail and examples

# format using "black" - use the console_scripts runner, against the "black" entrypoint
# hooks = black
# black.type = console_scripts
# black.entrypoint = black
# black.options = -l 79 REVISION_SCRIPT_FILENAME

# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = INFO
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

[zzzeek]

the problem is likely this part:

connectable = get_db_session(get_sql_driver_url()).connection()

if get_db_session() returns an ORM Session, then that connection is already started in a transaction. you would need to call commit() on that Session.

it would be better to get an Engine object directly, rather than a Session, so that with context.begin_transaction(): does the right thing.

[Olamyy]

Using an engine object fixed the issue.
Thanks @zzzeek .