Alembic and SA 2: upgrade ends with a rollback

[Lele Gaifax]

Hi all,

I'm in the process of upgrading another app to SA 2, and got no suprises
in the process: everything works smooth and all tests pass.

Going one step further, implementing a requested feature that involves a
database migration, I'm facing a show stopper, probably due to a silly
oversight of mine that already took a couple of hours of investigation:
the final step of the "alembic upgrade" is invariably a "ROLLBACK",
instead of a "COMMIT":

UPDATE alembic_version SET version_num='aa0288cff765' WHERE alembic_version.version_num = '1e49ee0210d7'
ROLLBACK

Simply downgrading SA to 1.4.46, without any other change, "fixes" the
issue and the migration ends with a "COMMIT" as expected.

The Alembic environment

https://gitlab.com/metapensiero/SoL/-/blob/master/alembic/env.py

is rather old being the same as when I started using Alembic in this
project (ten years, omg!), but even modernizing it using Alembic 1.9.2
template didn't help: when the connection is closed, either explicitly
by the old try:finally: code or implicitly by the new
connectable.connect() context manager, a ROLLBACK is issued.

Do you have an hint on what I'm missing?

Thanks in advance,
bye, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
le...@metapensiero.it | -- Fortunato Depero, 1929.

[Mike Bayer]

It's not supposed to have this problem with a clean env.py.    If you were running commands on the connection() ahead of calling context.begin_transaction(), that would explain things but I dont see that in your example.

short answer call connection.commit() at the end of the block , however that should not be needed with a plain env.py as you have.   so we'd like to know what is happening in this example (or if alembic is just broken and despite all my testing I am somehow missing something basic)

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/87h6wsdr1y.fsf%40metapensiero.it.

[Mike Bayer]

can you add these assertions to env.py?

with context.begin_transaction():

    assert not context.get_context()._in_external_transaction

    assert not connection.in_transaction()

    context.run_migrations()

that's the condition necessary for begin_transaction() to call commit() at the end.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c1edf82e-2051-4e32-b346-1136acfb1124%40app.fastmail.com.

[Mike Bayer]

I'm actually running your app here and am not reproducing the issue.

There's a few things going on here, and I can see the code here in your "master" branch at least hasn't been updated for 2.0.

I made a few small changes, "engine.has_table()" to "inspect(engine).has_table()", some strings print statements that needed to be changed for URL being a tuple "print('The database "%s" has been successfully initialized' % (engine.url, ))`, and I'm able, after doing "make development.db", to run bits of alembic downgrades and upgrades, with some bumps for various downgrades / upgrades not really being in sequence with what "make development.db" set up, or with each other (downgrades are hard).  but for migrations that make it without errors, COMMIT is being emitted at the end, for example:

(.venv) [classic@photon3 SoL:master]$ alembic -n app:main -c development.ini upgrade head

10:58:15 [I] sqlalchemy.engine.Engine: BEGIN (implicit)

10:58:15 [I] sqlalchemy.engine.Engine: PRAGMA main.table_info("alembic_version")

10:58:15 [I] sqlalchemy.engine.Engine: [raw sql] ()

10:58:15 [I] sqlalchemy.engine.Engine: SELECT alembic_version.version_num 

FROM alembic_version

10:58:15 [I] sqlalchemy.engine.Engine: [generated in 0.00008s] ()

10:58:15 [I] sqlalchemy.engine.Engine: ALTER TABLE competitors ADD COLUMN position SMALLINT DEFAULT 0 NOT NULL

10:58:15 [I] sqlalchemy.engine.Engine: [no key 0.00005s] ()

10:58:15 [I] sqlalchemy.engine.Engine: UPDATE alembic_version SET version_num='448c00f2ec1e' WHERE alembic_version.version_num = 'ffec21d0a5a0'

10:58:15 [I] sqlalchemy.engine.Engine: [generated in 0.00010s] ()

10:58:15 [I] sqlalchemy.engine.Engine: COMMIT

10:58:15 [I] sqlalchemy.engine.Engine: BEGIN (implicit)

10:58:15 [I] sqlalchemy.engine.Engine: ALTER TABLE matches ADD COLUMN breaker CHAR(1)

10:58:15 [I] sqlalchemy.engine.Engine: [no key 0.00006s] ()

10:58:15 [I] sqlalchemy.engine.Engine: UPDATE alembic_version SET version_num='1e49ee0210d7' WHERE alembic_version.version_num = '448c00f2ec1e'

10:58:15 [I] sqlalchemy.engine.Engine: [generated in 0.00008s] ()

10:58:15 [I] sqlalchemy.engine.Engine: COMMIT

Overall, the issue that you are seeing is likely that there are .commit() calls missing whereas previously autocommit was occurring.  So if you run your migrations under SQLAlchemy 1.4 with SQLALCHEMY_WARN_20=1 it *may* show some of these, if there were DDL or DML being emitted at least.   But the debug lines for env.py I gave previously should also show if things are not lined up.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/79dac31b-500d-4d47-bb69-baae033d7ba5%40app.fastmail.com.

[Lele Gaifax]

"Mike Bayer" <mike_not_...@zzzcomputing.com> writes:

> can you add these assertions to env.py?
>
> with context.begin_transaction():
> assert not context.get_context()._in_external_transaction
> assert not connection.in_transaction()
> context.run_migrations()
>
> that's the condition necessary for begin_transaction() to call
> commit() at the end.

With those in place, the first one fails with this traceback:

16:56:36 [I] sqlalchemy.engine.Engine: BEGIN (implicit)
16:56:36 [I] sqlalchemy.engine.Engine: PRAGMA foreign_keys=OFF
16:56:36 [I] sqlalchemy.engine.Engine: [raw sql] ()
16:56:36 [I] sqlalchemy.engine.Engine: ROLLBACK
Traceback (most recent call last):
File "/home/lele/wip/sol5/env/bin/soladmin", line 33, in <module>
sys.exit(load_entry_point('SoL', 'console_scripts', 'soladmin')())
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/lele/wip/sol5/src/sol/scripts/admin.py", line 640, in main
sys.exit((args.func(args) or 0) if hasattr(args, 'func') else 0)
^^^^^^^^^^^^^^^
File "/home/lele/wip/sol5/src/sol/scripts/admin.py", line 195, in upgrade_db
command.upgrade(cfg, "head")
File "/home/lele/wip/sol5/env/lib/python3.11/site-packages/alembic/command.py", line 378, in upgrade
script.run_env()
File "/home/lele/wip/sol5/env/lib/python3.11/site-packages/alembic/script/base.py", line 569, in run_env
util.load_python_file(self.dir, "env.py")
File "/home/lele/wip/sol5/env/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
module = load_module_py(module_id, path)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/lele/wip/sol5/env/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
spec.loader.exec_module(module) # type: ignore
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "<frozen importlib._bootstrap_external>", line 940, in exec_module
File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
File "/home/lele/wip/sol5/alembic/env.py", line 88, in <module>
run_migrations_online()
File "/home/lele/wip/sol5/alembic/env.py", line 80, in run_migrations_online
assert not context.get_context()._in_external_transaction
AssertionError

That "soladmin" launcher is the CLI tool to manage the instance, that in
this case is just a thin wrapper around alembic.upgrade():
https://gitlab.com/metapensiero/SoL/-/blob/master/src/sol/scripts/admin.py#L173

And sorry for not having mentioned that another different cog I'm trying
out is running it with Python 3.11.

ciao, lele.

[Mike Bayer]

I'm able to add the assertions and run "make upgrade-db" here without the assertions failing.

Can I get an exact version of what code you are running:

1. branch in gitlab, and/or local changes present

2. exact version of alembic

3. exact version of sqlalchemy

4. running on a clean dev sqlite db or is this some production DB ?

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/878ri3eq4g.fsf%40metapensiero.it.

[Lele Gaifax]

"Mike Bayer" <mike_not_...@zzzcomputing.com> writes:

> I'm actually running your app here and am not reproducing the issue.

Oh, what an honor! :-)

> There's a few things going on here, and I can see the code here in
> your "master" branch at least hasn't been updated for 2.0.

Sorry, I'm committing current state and pushing it right now.

>

Will be back on your other remarks later.

Thanks&bye, lele.

[Lele Gaifax]

"Mike Bayer" <mike_not_...@zzzcomputing.com> writes:

> There's a few things going on here, and I can see the code here in
> your "master" branch at least hasn't been updated for 2.0.

See https://gitlab.com/metapensiero/SoL/-/commits/sol5

[Lele Gaifax]

"Mike Bayer" <mike_not_...@zzzcomputing.com> writes:

> I'm able to add the assertions and run "make upgrade-db" here without the assertions failing.
>
>
> Can I get an exact version of what code you are running:
>
> 1. branch in gitlab, and/or local changes present
> 2. exact version of alembic
> 3. exact version of sqlalchemy
> 4. running on a clean dev sqlite db or is this some production DB ?

With latest commit in the branch sol5
(https://gitlab.com/metapensiero/SoL/-/commits/sol5), the problem should
emerge.

Alembic is at 1.9.2 and SA at 2.0.0rc2 (pinned in requirements/base.txt).

I'm testing against a copy of the production DB, but it actually does
not make any difference, as the same happens with an empty development
db.

Thanks&bye, lele.

[Mike Bayer]

take a look at my console session here.  this includes those assertions I mentioned.    I had to break it out on a fetch of a zip file which I wasn't sure was hanging or not, but it created all the tables, included COMMIT, then I did a little bit of alembic downgrade / upgrade, COMMIT succeeds, the two assertions succeed

https://gist.githubusercontent.com/zzzeek/6ea76bb4c3bf50995bc7cc49b84ad88f/raw/9128670e40d4f5b2a3a77a66fb39d500d9a27012/gistfile1.txt

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/87v8l7d95g.fsf%40metapensiero.it.