Migration tests fail with sqlalchemy 0.8

[Chuck Short]

 File "/��BUILDDIR��/nova-2013.2+git201308071233~saucy/nova/db/sqlalchemy/migrate_repo/versions/206_add_instance_cleaned.py", line 47, in downgrade
    instances.columns.cleaned.drop()
  File "/usr/lib/python2.7/dist-packages/migrate/changeset/schema.py", line 549, in drop
    engine._run_visitor(visitorcallable, self, connection, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1479, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1122, in _run_visitor
    **kwargs).traverse_single(element)
  File "/usr/lib/python2.7/dist-packages/migrate/changeset/ansisql.py", line 53, in traverse_single
    ret = super(AlterTableVisitor, self).traverse_single(elem)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 111, in traverse_single
    return meth(obj, **kw)
  File "/usr/lib/python2.7/dist-packages/migrate/changeset/databases/sqlite.py", line 90, in visit_column
    super(SQLiteColumnDropper,self).visit_column(column)
  File "/usr/lib/python2.7/dist-packages/migrate/changeset/databases/sqlite.py", line 53, in visit_column
    self.recreate_table(table,column,delta)
  File "/usr/lib/python2.7/dist-packages/migrate/changeset/databases/sqlite.py", line 40, in recreate_table
    table.create(bind=self.connection)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 614, in create
    checkfirst=checkfirst)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1122, in _run_visitor
    **kwargs).traverse_single(element)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 111, in traverse_single
    return meth(obj, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 93, in visit_table
    self.traverse_single(index)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 111, in traverse_single
    return meth(obj, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 105, in visit_index
    self.connection.execute(schema.CreateIndex(index))
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 720, in _execute_ddl
    compiled
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 195, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
OperationalError: (OperationalError) table instances has no column named cleaned u'CREATE INDEX instances_host_deleted_cleaned_idx ON instances (host, deleted, cleaned)' ()
======================================================================
FAIL: process-returncode
tags: worker-0
----------------------------------------------------------------------

[Matt Riedemann (mriedem)]

I'm not sure how this is valid since nova's requirements.txt pins sqlalchemy < 0.7.99:

https://github.com/openstack/nova/blob/master/requirements.txt#L3

[David Ripton (dripton)]

Like Matt said, this is invalid for now since we pin sqlalchemy to 0.7.x. But it will matter in the near future when we go for 0.8 compatibility, so I'll leave it here as a low priority.

[Michael Still (mikal)]

Is this bug repeatable? The cleaned column not existing in the downgrade means that it wasn't created in the upgrade. I don't see how that could be happening unless something really unexpected is wrong.

[Matt Riedemann (mriedem)]

Marking this as invalid since it must be working, we're now supporting sqlalchemy 0.8.

[Thomas Goirand (thomas-goirand)]

Hi,

I'd like to point out that once again, the requirements.txt do not decide what kind of package we hold in the distribution, and therefore, this type of argumentation is completely invalid. I'm having issues with unit tests in Sid, which used to run SQLA 0.8, and which now has 0.9. In both cases, it fails. I really would like to fix these.

As you see, Chuck is having the same issue that I have in Debian in Ubuntu.

[Matt Riedemann (mriedem)]

In talking with Thomas it sounds like there is a problem with using latest sqlalchemy-migrate and sqlalchemy > 0.7.99 with nova, I do remember Dan Prince needing to fix something in nova when we tried updating to migrate 0.8.2 for sqla 0.8 support, we'd have to go back and find that change and backport it to stable/havana. It was pretty trivial from what I remember, something about the migration test code putting a cap on migrate/sqlalchemy for a now-invalid reason.

[Matt Riedemann (mriedem)]

Bingo: https://review.openstack.org/56667

[David Ripton (dripton)]

Matt, that review bumps migrate to 0.8.2, but does not unpin SQLAlchemy from <= 0.7.99. I think we'd also need to backport https://review.openstack.org/#/c/68246/ to allow using SQLAlchemy 0.8.x.

[Andrew Laski (alaski)]

Now that SQLAlchemy is set to >=0.7.8,<=0.9.99 is this still valid?

[Matt Riedemann (mriedem)]

Given stable/icehouse supports newer sqlalchemy:

https://github.com/openstack/nova/blob/stable/icehouse/requirements.txt#L2

I think we can close this, there would be nothing to backport in community since havana is end of life.