Bug: Inconsistent Database State and Partial Migration Applied After Error in Alembic Auto-Generated Migration

[SimiHack]

Hello Alembic Community,

I am encountering an issue when using Alembic migrations with SQLAlchemy and the Azure Database ODBC Driver. Here's the problem in detail:

Steps to Reproduce:

1. I added a new column (test_column) in the ORM model.
2. I generated a migration with the following command:

   alembic revision --autogenerate -m "Add test_column column in test_table"
   

3. I applied the migration successfully.
4. Subsequently, someone manually added a column that already existed in the database (test_column) along with the new column.
5. When applying the migration, the new column was added, but an error occurred in the terminal stating that the duplicate column already existed in the table:

   OperationalError: (1060, "Duplicate column name 'test_column'")
   

   Despite this error, the new column was still added to the table. The migration didn't fully roll back, and the Alembic version table wasn't updated as expected.

Issue:
Ideally, when an error occurs, the entire migration should be rolled back, and no changes should be applied to the database. However, in this case, the new column was added despite the error. Additionally, the Alembic version table was not updated to reflect the migration's partial application.

Questions:

• Why is this inconsistency happening? Why is the new column added even though the migration failed?
• Is there any way to ensure that Alembic correctly rolls back the entire transaction when an error occurs and avoids applying partial changes to the database?
• Can I resolve this issue without manually modifying the generated migration script or altering the existing database?

Looking forward to your insights!

Thank you.

[zzzeek]

when using the mssql dialect (I assume that's what you're using), transactional DDL is applied by default. you would see this in the logging output that alembic generates. so assuming alembic raised an error, it rolls back the whole transaction which for transactional DDL includes everything it did from the beginning of the connection.

I would advise setting the log level for SQLAlchemy to INFO which will show the point at which ROLLBACK or COMMIT is emitted.

if your database and/or odbc driver is putting the connection into autocommit mode, then this wont work.

[SimiHack]

I tried these still transaction didn't rollback. I see partial migration applied to database (ie. test_mode added to table) and ideally it should rollback entire transaction.

INFO: Waiting for application startup.
{"level": "ERROR", "time": "2025-01-25 15:13:02,287", "name": "scripts.alembic_startup", "message": "Error: Error upgrading database with Alembic: INFO [sqlalchemy.engine.Engine] SHOW VARIABLES LIKE 'sql_mode'
INFO [sqlalchemy.engine.Engine] [raw sql] {}
INFO [sqlalchemy.engine.Engine] SHOW VARIABLES LIKE 'lower_case_table_names'
INFO [sqlalchemy.engine.Engine] [generated in 0.00020s] {}
INFO [sqlalchemy.engine.Engine] SELECT DATABASE()
INFO [sqlalchemy.engine.Engine] [raw sql] {}
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.00015s] {'table_schema': 'advanceai', 'table_name': 'alembic_version'}
INFO [sqlalchemy.engine.Engine] SELECT alembic_version.version_num
FROM alembic_version
INFO [sqlalchemy.engine.Engine] [generated in 0.00015s] {}
INFO [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO [alembic.runtime.migration] Running upgrade 8161374a0c8c -> 16b6dac876d1, Added column in api_request model
INFO [sqlalchemy.engine.Engine] ALTER TABLE api_request ADD COLUMN test_mode INTEGER
INFO [sqlalchemy.engine.Engine] [no key 0.00012s] {}
INFO [sqlalchemy.engine.Engine] ALTER TABLE api_request ADD COLUMN api_version INTEGER
INFO [sqlalchemy.engine.Engine] [no key 0.00018s] {}
INFO [sqlalchemy.engine.Engine] ROLLBACK
Traceback (most recent call last):
File "D:\project_path.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1771, in _execute_context
self.dialect.do_execute(
File "D:\project_path.venv\Lib\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute
cursor.execute(statement, parameters)
File "D:\project_path.venv\Lib\site-packages\pymysql\cursors.py", line 153, in execute
result = self._query(query)
^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\pymysql\cursors.py", line 322, in _query
conn.query(q)
File "D:\project_path.venv\Lib\site-packages\pymysql\connections.py", line 563, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\pymysql\connections.py", line 825, in _read_query_result
result.read()
File "D:\project_path.venv\Lib\site-packages\pymysql\connections.py", line 1199, in read
first_packet = self.connection._read_packet()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\pymysql\connections.py", line 775, in _read_packet
packet.raise_for_error()
File "D:\project_path.venv\Lib\site-packages\pymysql\protocol.py", line 219, in raise_for_error
err.raise_mysql_exception(self._data)
File "D:\project_path.venv\Lib\site-packages\pymysql\err.py", line 150, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1060, "Duplicate column name 'api_version'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "", line 198, in _run_module_as_main
File "", line 88, in run_code
File "D:\project_path.venv\Scripts\alembic.exe_main.py", line 7, in
File "D:\project_path.venv\Lib\site-packages\alembic\config.py", line 641, in main
CommandLine(prog=prog).main(argv=argv)
File "D:\project_path.venv\Lib\site-packages\alembic\config.py", line 631, in main
self.run_cmd(cfg, options)
File "D:\project_path.venv\Lib\site-packages\alembic\config.py", line 608, in run_cmd
fn(
File "D:\project_path.venv\Lib\site-packages\alembic\command.py", line 403, in upgrade
script.run_env()
File "D:\project_path.venv\Lib\site-packages\alembic\script\base.py", line 583, in run_env
util.load_python_file(self.dir, "env.py")
File "D:\project_path.venv\Lib\site-packages\alembic\util\pyfiles.py", line 95, in load_python_file
module = load_module_py(module_id, path)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\alembic\util\pyfiles.py", line 113, in load_module_py
spec.loader.exec_module(module) # type: ignore
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "", line 940, in exec_module
File "", line 241, in _call_with_frames_removed
File "D:\project_path\alembic\env.py", line 89, in
run_migrations_online()
File "D:\project_path\alembic\env.py", line 83, in run_migrations_online
context.run_migrations()
File "", line 8, in run_migrations
File "D:\project_path.venv\Lib\site-packages\alembic\runtime\environment.py", line 948, in run_migrations
self.get_context().run_migrations(**kw)
File "D:\project_path.venv\Lib\site-packages\alembic\runtime\migration.py", line 627, in run_migrations
step.migration_fn(**kw)
File "D:\project_path\alembic\versions\2025_01_24_2016-16b6dac876d1_added_column_in_api_request_model.py", line 24, in upgrade
op.add_column('api_request', sa.Column('api_version', sa.INTEGER(), nullable=True))
File "", line 8, in add_column
File "", line 3, in add_column
File "D:\project_path.venv\Lib\site-packages\alembic\operations\ops.py", line 2142, in add_column
return operations.invoke(op)
^^^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\alembic\operations\base.py", line 445, in invoke
return fn(self, operation)
^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\alembic\operations\toimpl.py", line 171, in add_column
operations.impl.add_column(table_name, column, schema=schema, **kw)
File "D:\project_path.venv\Lib\site-packages\alembic\ddl\impl.py", line 334, in add_column
self._exec(base.AddColumn(table_name, column, schema=schema))
File "D:\project_path.venv\Lib\site-packages\alembic\ddl\impl.py", line 207, in _exec
return conn.execute(construct, multiparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1263, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\sqlalchemy\sql\ddl.py", line 77, in _execute_on_connection
return connection._execute_ddl(
^^^^^^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1353, in _execute_ddl
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1814, in _execute_context
self.handle_dbapi_exception(
File "D:\project_path.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1995, in handle_dbapi_exception
util.raise(
File "D:\project_path.venv\Lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise
raise exception
File "D:\project_path.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1771, in _execute_context
self.dialect.do_execute(
File "D:\project_path.venv\Lib\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute
cursor.execute(statement, parameters)
File "D:\project_path.venv\Lib\site-packages\pymysql\cursors.py", line 153, in execute
result = self._query(query)
^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\pymysql\cursors.py", line 322, in _query
conn.query(q)
File "D:\project_path.venv\Lib\site-packages\pymysql\connections.py", line 563, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\pymysql\connections.py", line 825, in _read_query_result
result.read()
File "D:\project_path.venv\Lib\site-packages\pymysql\connections.py", line 1199, in read
first_packet = self.connection._read_packet()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "D:\project_path.venv\Lib\site-packages\pymysql\connections.py", line 775, in _read_packet
packet.raise_for_error()
File "D:\project_path.venv\Lib\site-packages\pymysql\protocol.py", line 219, in raise_for_error
err.raise_mysql_exception(self._data)
File "D:\project_path.venv\Lib\site-packages\pymysql\err.py", line 150, in raise_mysql_exception
raise errorclass(errno, errval)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1060, "Duplicate column name 'api_version'")
[SQL: ALTER TABLE api_request ADD COLUMN api_version INTEGER]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
"}
INFO: Application startup complete.

[CaselIT]

that's what alembic is doing. A single transaction is used for both alter command.
You should look at the documentation of the driver you are using and of the db to verify if it support transactional ddl