[SRU] schema changes using sqlalchemy's sqlite dialect can fail when using reflection

[Adam Gandelman]

[IMPACT]

Sqlalchemy allows models to be generated from existing tables automatically using reflection. The fact that sqlite does not support FK constraints is not properly accounted for in this process and certain schema modifications result in the exception logged in this bug. The most likely way of triggering this fault is through the use of python-migrate and running database migrations against the sqlite dialect.

[TESTCASE]

As of 07/23/2012 (git hash 601882a23dd8a6573f0e59bb26e13233e2dce736), migration '111_general_aggregates.py' attempts to drop a table /w a FK constraint and triggers this bug when run against sqlite. It can be reproduced easily by:

ubuntu@server-468:~$ sudo apt-get -y install git && sudo apt-get -y build-dep nova-common
ubuntu@server-468:~$ git clone https://github.com/openstack/nova.git
ubuntu@server-468:~$ (cd nova/ && ./run_tests.sh -N -P)

This will run through the migrations as part of the test suite, and result in the traceback that ending in: AttributeError: 'int' object has no attribute 'lower'

[Development Fix]

This has been resolved upstream in version 0.7.5 and fixed in Ubuntu 12.10 as of the 0.7.8 Debian sync. http://hg.sqlalchemy.org/sqlalchemy/rev/2aed4e56676a

[Regression Potential]

Minimal. The minimal fix is a 2 line change that properly sets constraint_name to a None value. The bulk of the patch included in my branch is updates to the sqlalchemy test suite, which is run during our package build.

>> Original bug report <<

Seems to only trigger when running on Ubuntu 12.04 with distro packaged dependencies (that is, not installed from via pip). Exception when attempting to drop the aggregate_hosts.host column during migration of sqlite databases. First hit this running the test suite during automated package builds, but it is reproducable via 'nova-mange db sync', too ( though less verbose)

ubuntu@server-459:~/nova$ pkgs="openssh-client openssl python-setuptools python-setuptools-git python-sphinx python-distutils-extra python-gflags python-mox python-carrot python-boto python-amqplib openssh-client python-sqlalchemy python-eventlet python-routes python-webob python-cheetah python-nose python-paste python-pastedeploy python-tempita python-migrate python-netaddr python-glance python-paramiko python-novaclient python-lockfile python-simplejson python-lxml python-unittest2 python-daemon python-suds python-xattr python-feedparser python-crypto python-iso8601 python-kombu python-quantumclient pep8"
ubuntu@server-459:~/nova$ sudo apt-get -y install $pkgs
ubuntu@server-459:~/nova$ ./run_tests.sh -N -P

ERROR

======================================================================
ERROR: test suite for <module 'nova.tests' from '/home/ubuntu/nova/nova/tests/__init__.py'>
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/suite.py", line 208, in run
    self.setUp()
  File "/usr/lib/python2.7/dist-packages/nose/suite.py", line 291, in setUp
    self.setupContext(ancestor)
  File "/usr/lib/python2.7/dist-packages/nose/suite.py", line 314, in setupContext
    try_run(context, names)
  File "/usr/lib/python2.7/dist-packages/nose/util.py", line 478, in try_run
    return func()
  File "/home/ubuntu/nova/nova/tests/__init__.py", line 86, in setup
    migration.db_sync()
  File "/home/ubuntu/nova/nova/db/migration.py", line 32, in db_sync
    return IMPL.db_sync(version=version)
  File "/home/ubuntu/nova/nova/db/sqlalchemy/migration.py", line 79, in db_sync
    return versioning_api.upgrade(get_engine(), repository, version)
  File "/usr/lib/python2.7/dist-packages/migrate/versioning/api.py", line 186, in upgrade
    return _migrate(url, repository, version, upgrade=True, err=err, **opts)
  File "<string>", line 2, in _migrate
  File "/home/ubuntu/nova/nova/db/sqlalchemy/migration.py", line 44, in patched_with_engine
    return f(*a, **kw)
  File "/usr/lib/python2.7/dist-packages/migrate/versioning/api.py", line 366, in _migrate
    schema.runchange(ver, change, changeset.step)
  File "/usr/lib/python2.7/dist-packages/migrate/versioning/schema.py", line 91, in runchange
    change.run(self.engine, step)
  File "/usr/lib/python2.7/dist-packages/migrate/versioning/script/py.py", line 145, in run
    script_func(engine)
  File "/home/ubuntu/nova/nova/db/sqlalchemy/migrate_repo/versions/111_general_aggregates.py", line 46, in upgrade
    aggregate_hosts.drop_column('host')
  File "/usr/lib/python2.7/dist-packages/migrate/changeset/schema.py", line 445, in drop_column
    column.drop(table=self, *p, **kw)
  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 2234, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1904, 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 86, 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 564, in create
    checkfirst=checkfirst)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1904, in _run_visitor
    **kwargs).traverse_single(element)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 86, in traverse_single
    return meth(obj, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 86, in visit_table
    self.connection.execute(schema.CreateTable(table))
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1405, in execute
    params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1490, in _execute_ddl
    compiled = ddl.compile(dialect=dialect)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py", line 1722, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 2852, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 699, in __init__
    self.string = self.process(self.statement)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 718, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 59, in _compiler_dispatch
    return getter(visitor)(self, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1386, in visit_create_table
    const = self.create_table_constraints(table)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1406, in create_table_constraints
    for constraint in constraints
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1404, in <genexpr>
    return ", \n\t".join(p for p in
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1412, in <genexpr>
    not getattr(constraint, 'use_alter', False)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 718, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 59, in _compiler_dispatch
    return getter(visitor)(self, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/dialects/sqlite/base.py", line 382, in visit_foreign_key_constraint
    return super(SQLiteDDLCompiler, self).visit_foreign_key_constraint(constraint)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1540, in visit_foreign_key_constraint
    preparer.format_constraint(constraint)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1833, in format_constraint
    return self.quote(constraint.name, constraint.quote)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1805, in quote
    if self._requires_quotes(ident):
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 1786, in _requires_quotes
    lc_value = value.lower()
AttributeError: 'int' object has no attribute 'lower'

Might be of interest, since some are older than what devstack installs:

 python-sqlalchemy 0.7.4-1
 python-migrate 0.7.2-1ubuntu1
 libsqlite3-0 3.7.9-2ubuntu1

[Adam Gandelman (gandelman-a)]

This is migration is apparently hitting a bug that was fixed in sqlalchemy 0.7.5:

http://hg.sqlalchemy.org/sqlalchemy/rev/2aed4e56676a

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Thierry Carrez (ttx)]

On Nova's side, we should be either requiring 0.7.5 or working around that specific issue

[Marc Deslauriers (mdeslaur)]

Looks good, ACK.

I've uploaded it to -proposed (with a slight version change) for processing by the SRU team. Thanks!

[Adam Gandelman (gandelman-a)]

Is there an ETA on when this will hit precise-proposed?

[Adam Conrad (adconrad)]

Hello Adam, or anyone else affected,

Accepted sqlalchemy into precise-proposed. The package will build now and be available at http://launchpad.net/ubuntu/+source/sqlalchemy/0.7.4-1ubuntu0.1 in a few hours, and then in the -proposed repository.

Please help us by testing this new package. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Your feedback will aid us getting this update out to other Ubuntu users.

If this package fixes the bug for you, please change the bug tag from verification-needed to verification-done. If it does not, change the tag to verification-failed. In either case, details of your testing will help us make a better decision.

Further information regarding the verification process can be found at https://wiki.ubuntu.com/QATeam/PerformingSRUVerification . Thank you in advance!

[Chuck Short (zulcss)]

Tried to reproduce the error:

1. Install nova-common
2. Installed failed
3. Removed nova-common
3. installed sqlachemy from proposed
4. Installed nova-common
5. Didnt get the error.

chuck

[Adam Conrad (adconrad)]

The verification of this Stable Release Update has completed successfully and the package has now been released to -updates. Subsequently, the Ubuntu Stable Release Updates Team is being unsubscribed and will not receive messages about this bug report. In the event that you encounter a regression using the package from -updates please report a new bug using ubuntu-bug and tag the bug report regression-update so we can easily find any regresssions.

[Launchpad Janitor (janitor)]

This bug was fixed in the package sqlalchemy - 0.7.4-1ubuntu0.1

---------------
sqlalchemy (0.7.4-1ubuntu0.1) precise-proposed; urgency=low

  * debian/patches/lp1025544-fix-sqlite-reflection.patch: Fix sqlite database
    reflection bug that can result in FK constraint name type issues.
    (LP: #1025544)
 -- Adam Gandelman <email address hidden> Tue, 17 Jul 2012 17:57:24 -0700

[Sean Dague (sdague)]

fixed by bumping min SQA to 0.7.8