sqlalchemy-migrate>0.9.1 and <0.9.5 causes glance test failures

[Nikhil Komawar]

The error is seen as

OperationalError: (OperationalError) cannot start a transaction within a transaction u'/*\n * This is necessary because SQLite does not support\n * RENAME INDEX or ALTER TABLE CHANGE COLUMN.\n */\nBEGIN TRANSACTION;' ()

More info at:
http://paste.openstack.org/show/157276/

[Ian Cordasco (icordasc)]

I can reproduce this with glance's tests. If you install sqlalchemy-migrate 0.9.1, all tests pass. If you install 0.9.4 a subset of the tests fail. It's unclear what is actually causing this.

[Ian Cordasco (icordasc)]

 Davanum (dims) helped me narrow it down to https://github.com/stackforge/sqlalchemy-migrate/commit/938757e7aa581646a6a501426a5dae84ebe5631f in #openstack-oslo since there weren't many changes between 0.9.2 and 0.9.4. This could also be a problem introduced in 0.9.2 and not found after we blacklisted its installation.

[Nikhil Komawar (nikhil-komawar)]

The versions breaking the tests are 0.9.2, 0.9.3(possibly removed) and 0.9.4 as of now.

[Nikhil Komawar (nikhil-komawar)]

https://review.openstack.org/#/c/146975/

[Matt Riedemann (mriedem)]

Ihar has a change in glance here: https://review.openstack.org/#/c/121033/

That would get around this for now, but wouldn't fix anyone busted with the other versions of sqlalchemy-migrate.

[Matt Riedemann (mriedem)]

http://logstash.openstack.org/#eyJzZWFyY2giOiJtZXNzYWdlOlwiT3BlcmF0aW9uYWxFcnJvcjogKE9wZXJhdGlvbmFsRXJyb3IpIGNhbm5vdCBzdGFydCBhIHRyYW5zYWN0aW9uIHdpdGhpbiBhIHRyYW5zYWN0aW9uXCIgQU5EIG1lc3NhZ2U6XCJUaGlzIGlzIG5lY2Vzc2FyeSBiZWNhdXNlIFNRTGl0ZSBkb2VzIG5vdCBzdXBwb3J0XCIgQU5EIHRhZ3M6XCJjb25zb2xlXCIgQU5EIGJ1aWxkX25hbWU6XCJnYXRlLWdsYW5jZS1weXRob24yN1wiIiwiZmllbGRzIjpbXSwib2Zmc2V0IjowLCJ0aW1lZnJhbWUiOiIxNzI4MDAiLCJncmFwaG1vZGUiOiJjb3VudCIsInRpbWUiOnsidXNlcl9pbnRlcnZhbCI6MH0sInN0YW1wIjoxNDIxMTgyNjMzNzU2LCJtb2RlIjoiIiwiYW5hbHl6ZV9maWVsZCI6IiJ9

1792 hits in the last 48 hours, right now it's on showing up on one change in the check queue but I'm sure it's hitting more.

http://logs.openstack.org/53/146953/1/check/gate-glance-python27/b87b2b8/console.html#_2015-01-13_19_49_37_989

And it's using sqlalchemy-migrate 0.9.4:

http://logs.openstack.org/53/146953/1/check/gate-glance-python27/b87b2b8/console.html#_2015-01-13_19_51_00_691

[Matt Riedemann (mriedem)]

Also:

message:"found in SQL script; ignoring" AND tags:"console" AND build_name:"gate-glance-python27"

http://logstash.openstack.org/#eyJzZWFyY2giOiJtZXNzYWdlOlwiZm91bmQgaW4gU1FMIHNjcmlwdDsgaWdub3JpbmdcIiBBTkQgdGFnczpcImNvbnNvbGVcIiBBTkQgYnVpbGRfbmFtZTpcImdhdGUtZ2xhbmNlLXB5dGhvbjI3XCIiLCJmaWVsZHMiOltdLCJvZmZzZXQiOjAsInRpbWVmcmFtZSI6IjE3MjgwMCIsImdyYXBobW9kZSI6ImNvdW50IiwidGltZSI6eyJ1c2VyX2ludGVydmFsIjowfSwic3RhbXAiOjE0MjExODM5NDM3MTQsIm1vZGUiOiIiLCJhbmFseXplX2ZpZWxkIjoiIn0=

1074 hits.

That warning was added here:

https://review.openstack.org/#/c/121517/8/migrate/versioning/script/sql.py

[Matt Riedemann (mriedem)]

sdague updated https://review.openstack.org/#/c/121033/ to fix the comments in the sql scripts to be using the correct format (-- rather than /* */), so the comments were wrong and that was causing a parsing error.

We can fix the migrations on master and pin sqlalchemy-migrate in the stable branches.

[Ian Cordasco (icordasc)]

For openstack-ansible, the solution is to pin sqlalchemy-migrate to version 0.9.1

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/121033
Committed: https://git.openstack.org/cgit/openstack/glance/commit/?id=92a7111e9e5a9e3494ba1ce074f673bc11328c63
Submitter: Jenkins
Branch: master

commit 92a7111e9e5a9e3494ba1ce074f673bc11328c63
Author: Ihar Hrachyshka <email address hidden>
Date: Fri Sep 12 11:02:31 2014 +0200

    SQL scripts should not manage transactions

    Glance SQL scripts assume too much about the environment and the way in
    which they are executed by sqlalchemy-migrate. Particularly, they manage
    transactions on their own even though sqlalchemy-migrate do it itself,
    plus it assumes that scripts are executed in one go (even though
    standard Python DB-API 2.0 does not mention multi-statement SQL commands
    to be executed on DB cursors.

    Also fix comments to be actual SQL comments and not C comments

    https://blueprints.launchpad.net/oslo.db/+spec/enable-mysql-connector

    Change-Id: I10c58b3af75d3ab9153a8bbd2a539bf1577de328
    Closes-Bug: 1368391
    Closes-Bug: 1410494

[OpenStack Infra (hudson-openstack)]

Change abandoned by Ihar Hrachyshka (<email address hidden>) on branch: stable/juno
Review: https://review.openstack.org/147217
Reason: We're going to cap sqlalchemy-migrate version instead.

[OpenStack Infra (hudson-openstack)]

Change abandoned by Ihar Hrachyshka (<email address hidden>) on branch: stable/icehouse
Review: https://review.openstack.org/147218
Reason: We're going to cap sqlalchemy-migrate version instead.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/147013
Committed: https://git.openstack.org/cgit/stackforge/sqlalchemy-migrate/commit/?id=b9caaae4fccb1d6f8dea575ff318975fe197edde
Submitter: Jenkins
Branch: master

commit b9caaae4fccb1d6f8dea575ff318975fe197edde
Author: Ihar Hrachyshka <email address hidden>
Date: Wed Jan 14 00:09:32 2015 +0100

    script: strip comments in SQL statements

    Regular expression does not match correctly against statements that contain
    comments at their start. So strip those comments first (and whitespaces, while
    we are at it).

    Change-Id: Iad9b544bf995374d76cab1e125658aae2f8511f4
    Closes-Bug: #1410494