alembic upgrade not idempotent?

[bert...@cryptosense.com]

Hi,

Here's something quite fundamental I don't understand about alembic.  I've initialized my database and created a revision meant to be executed online.  I went like this:

alembic init app/alembic
# edit configuration
alembic revision -m 'Data modification'
# edit revision script
alembic upgrade

If I run alembic upgrade a second time, it runs the upgrade function again, which results in an error because my function should not be run on the new database (it's not idempotent). Why doesn't alembic figure out that the upgrade has already been applied? This is especially annoying in production where I just want to make sure the database is up to date without having to guess which revision script to execute.

Best,

--
Bertrand

[Mike Bayer]

I'm not understanding what you are actually doing. It is an error to
run "alembic upgrade" without a target:

$ alembic upgrade
usage: alembic upgrade [-h] [--sql] [--tag TAG] revision
alembic upgrade: error: too few arguments


When you specify a target, say "head", it updates the version table:

$ .venv/bin/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 -> e12f6fb70dcc, rev 1

and then the next time, it will do nothing:

$ .venv/bin/alembic upgrade head
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.


so it appears like you have some entirely customized setup going on,
which itself would be where your issue lies.







>
> Best,
>
> --
> Bertrand
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[bert...@cryptosense.com]

Sorry for the confusion, I meant "alembic upgrade head" indeed.  As you confirmed the expected behavior, I did a bit of debugging to find out where I might have done a mistake, and I found the problem.

I wrote a test revision that looked like the following:

def upgrade():
    bind = op.get_bind()
    db = Session(bind)
    db.execute('alter table cats rename to dogs')

With level = DEBUG for [logger_sqlalchemy], I get:

> alembic upgrade head
INFO  [sqlalchemy.engine.base.Engine] SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO  [sqlalchemy.engine.base.Engine] ()
INFO  [sqlalchemy.engine.base.Engine] SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO  [sqlalchemy.engine.base.Engine] ()

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

INFO  [sqlalchemy.engine.base.Engine] PRAGMA table_info("alembic_version")
INFO  [sqlalchemy.engine.base.Engine] ()
INFO  [sqlalchemy.engine.base.Engine] PRAGMA table_info("alembic_version")
INFO  [sqlalchemy.engine.base.Engine] ()
INFO  [sqlalchemy.engine.base.Engine]
CREATE TABLE alembic_version (
        version_num VARCHAR(32) NOT NULL
)


INFO  [sqlalchemy.engine.base.Engine] ()
INFO  [sqlalchemy.engine.base.Engine] COMMIT
INFO  [alembic.runtime.migration] Running upgrade  -> 80def0e180bc, Rename cats to dogs
INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.base.Engine] alter table cats rename to dogs
INFO  [sqlalchemy.engine.base.Engine] ()
INFO  [sqlalchemy.engine.base.Engine] INSERT INTO alembic_version (version_num) VALUES ('80def0e180bc')
INFO  [sqlalchemy.engine.base.Engine] ()
INFO  [sqlalchemy.engine.base.Engine] ROLLBACK

As you can see, my omission of db.commit() caused alembic's version upgrade to be rolled back.  However, the table has really been renamed from "cats" to "dogs".  Therefore, a second run of alembic upgrade head will fail in this case.

[Mike Bayer]

On 04/18/2016 12:04 PM, bert...@cryptosense.com wrote:
> Sorry for the confusion, I meant "alembic upgrade head" indeed. As you
> confirmed the expected behavior, I did a bit of debugging to find out
> where I might have done a mistake, and I found the problem.
>
> I wrote a test revision that looked like the following:
>
> |
> defupgrade():
> bind =op.get_bind()
> db =Session(bind)
> db.execute('alter table cats rename to dogs')
> |
>
> With level = DEBUG for [logger_sqlalchemy], I get:
>
> |
>>alembic upgrade head
> INFO [sqlalchemy.engine.base.Engine]SELECT CAST('test plain returns'AS

> VARCHAR(60))AS anon_1

> INFO [sqlalchemy.engine.base.Engine]()
> INFO [sqlalchemy.engine.base.Engine]SELECT CAST('test unicode returns'AS

> VARCHAR(60))AS anon_1

> INFO [sqlalchemy.engine.base.Engine]()
> INFO [alembic.runtime.migration]Contextimpl SQLiteImpl.

> INFO [alembic.runtime.migration]Willassume non-transactional DDL.

> INFO [sqlalchemy.engine.base.Engine]PRAGMA table_info("alembic_version")
> INFO [sqlalchemy.engine.base.Engine]()
> INFO [sqlalchemy.engine.base.Engine]PRAGMA table_info("alembic_version")
> INFO [sqlalchemy.engine.base.Engine]()
> INFO [sqlalchemy.engine.base.Engine]
> CREATE TABLE alembic_version (

> version_num VARCHAR(32)NOT NULL

> )
>
>
> INFO [sqlalchemy.engine.base.Engine]()
> INFO [sqlalchemy.engine.base.Engine]COMMIT
> INFO [alembic.runtime.migration]Runningupgrade ->80def0e180bc,Renamecats
> to dogs
> INFO [sqlalchemy.engine.base.Engine]BEGIN(implicit)
> INFO [sqlalchemy.engine.base.Engine]alter table cats rename to dogs
> INFO [sqlalchemy.engine.base.Engine]()
> INFO [sqlalchemy.engine.base.Engine]INSERT INTO alembic_version

> (version_num)VALUES ('80def0e180bc')

> INFO [sqlalchemy.engine.base.Engine]()
> INFO [sqlalchemy.engine.base.Engine]ROLLBACK
> |
>
> As you can see, my omission of db.commit() caused alembic's version
> upgrade to be rolled back. However, the table has really been renamed
> from "cats" to "dogs". Therefore, a second run of alembic upgrade head
> will fail in this case.

in keeping with the tradition of "long-lived bugs that remain invisible
for years and all the sudden get reported twice in two days", this has
just been added yesterday in
https://bitbucket.org/zzzeek/alembic/issues/369/orm-session-creates-a-subtransaction-on#comment-27063967
.