Commit 0e98f5a breaks db migrations with DB2

Bug #1349515 reported by Matt Riedemann on 2014-07-28
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Compute (nova)
Low
Matt Riedemann

Bug Description

This change:

https://github.com/openstack/nova/commit/0e98f5a522c08b17c98ed108459a179d14eacd4a

Sets pci_devices.deleted.nullable=True which breaks with DB2 because that column is in a UniqueConstraint:

http://git.openstack.org/cgit/openstack/nova/tree/nova/db/sqlalchemy/models.py#n1374

And DB2 requires that columns in a UniqueConstraint are not nullable.

We get a trace like this:

2014-07-28 03:40:33.511 7720 INFO migrate.versioning.api [-] 246 -> 247...
2014-07-28 03:40:37.073 7720 CRITICAL nova [-] ProgrammingError: (ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0542N The column named "DELETED" cannot be a column of a primary key or unique key constraint because it can contain null values. SQLSTATE=42831 SQLCODE=-542 '\nALTER TABLE pci_devices ALTER COLUMN deleted DROP NOT NULL' ()
2014-07-28 03:40:37.073 7720 TRACE nova Traceback (most recent call last):
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/bin/nova-manage", line 10, in <module>
2014-07-28 03:40:37.073 7720 TRACE nova sys.exit(main())
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/nova/cmd/manage.py", line 1401, in main
2014-07-28 03:40:37.073 7720 TRACE nova ret = fn(*fn_args, **fn_kwargs)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/nova/cmd/manage.py", line 902, in sync
2014-07-28 03:40:37.073 7720 TRACE nova return migration.db_sync(version)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/nova/db/migration.py", line 29, in db_sync
2014-07-28 03:40:37.073 7720 TRACE nova return IMPL.db_sync(version=version)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/nova/db/sqlalchemy/migration.py", line 44, in db_sync
2014-07-28 03:40:37.073 7720 TRACE nova return versioning_api.upgrade(get_engine(), repository, version)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/versioning/api.py", line 186, in upgrade
2014-07-28 03:40:37.073 7720 TRACE nova return _migrate(url, repository, version, upgrade=True, err=err, **opts)
2014-07-28 03:40:37.073 7720 TRACE nova File "<string>", line 2, in _migrate
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/versioning/util/__init__.py", line 160, in with_engine
2014-07-28 03:40:37.073 7720 TRACE nova return f(*a, **kw)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/versioning/api.py", line 366, in _migrate
2014-07-28 03:40:37.073 7720 TRACE nova schema.runchange(ver, change, changeset.step)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/versioning/schema.py", line 93, in runchange
2014-07-28 03:40:37.073 7720 TRACE nova change.run(self.engine, step)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/versioning/script/py.py", line 148, in run
2014-07-28 03:40:37.073 7720 TRACE nova script_func(engine)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/nova/db/sqlalchemy/migrate_repo/versions/247_nullable_mismatch.py", line 27, in upgrade
2014-07-28 03:40:37.073 7720 TRACE nova pci_devices.c.deleted.alter(nullable=True)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/changeset/schema.py", line 534, in alter
2014-07-28 03:40:37.073 7720 TRACE nova return alter_column(self, *p, **k)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/changeset/schema.py", line 141, in alter_column
2014-07-28 03:40:37.073 7720 TRACE nova engine._run_visitor(visitorcallable, delta)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1479, in _run_visitor
2014-07-28 03:40:37.073 7720 TRACE nova conn._run_visitor(visitorcallable, element, **kwargs)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1122, in _run_visitor
2014-07-28 03:40:37.073 7720 TRACE nova **kwargs).traverse_single(element)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/changeset/ansisql.py", line 56, in traverse_single
2014-07-28 03:40:37.073 7720 TRACE nova ret = super(AlterTableVisitor, self).traverse_single(elem)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/sqlalchemy/sql/visitors.py", line 122, in traverse_single
2014-07-28 03:40:37.073 7720 TRACE nova return meth(obj, **kw)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/changeset/databases/ibmdb2.py", line 161, in visit_column
2014-07-28 03:40:37.073 7720 TRACE nova self._run_subvisit(delta, self._visit_column_nullable)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/changeset/databases/ibmdb2.py", line 136, in _run_subvisit
2014-07-28 03:40:37.073 7720 TRACE nova self.execute()
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/migrate/changeset/ansisql.py", line 44, in execute
2014-07-28 03:40:37.073 7720 TRACE nova return self.connection.execute(self.buffer.getvalue())
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 662, in execute
2014-07-28 03:40:37.073 7720 TRACE nova params)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 805, in _execute_text
2014-07-28 03:40:37.073 7720 TRACE nova statement, parameters
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
2014-07-28 03:40:37.073 7720 TRACE nova context)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
2014-07-28 03:40:37.073 7720 TRACE nova exc_info
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
2014-07-28 03:40:37.073 7720 TRACE nova reraise(type(exception), exception, tb=exc_tb)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
2014-07-28 03:40:37.073 7720 TRACE nova context)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib/python2.6/site-packages/ibm_db_sa/ibm_db.py", line 104, in do_execute
2014-07-28 03:40:37.073 7720 TRACE nova cursor.execute(statement, parameters)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/ibm_db_dbi.py", line 1335, in execute
2014-07-28 03:40:37.073 7720 TRACE nova self._execute_helper(parameters)
2014-07-28 03:40:37.073 7720 TRACE nova File "/usr/lib64/python2.6/site-packages/ibm_db_dbi.py", line 1247, in _execute_helper
2014-07-28 03:40:37.073 7720 TRACE nova raise self.messages[len(self.messages) - 1]
2014-07-28 03:40:37.073 7720 TRACE nova ProgrammingError: (ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0542N The column named "DELETED" cannot be a column of a primary key or unique key constraint because it can contain null values. SQLSTATE=42831 SQLCODE=-542 '\nALTER TABLE pci_devices ALTER COLUMN deleted DROP NOT NULL' ()
2014-07-28 03:40:37.073 7720 TRACE nova

This will need to be worked into blueprint db2-database.

Matt Riedemann (mriedem) on 2014-07-28
Changed in nova:
status: New → Triaged
assignee: nobody → Matt Riedemann (mriedem)
Sean Dague (sdague) on 2014-09-15
Changed in nova:
importance: Undecided → Low
Matt Riedemann (mriedem) wrote :

This is being handled with a backend-specific condition in the 247 migration:

https://review.openstack.org/#/c/69047/45/nova/db/sqlalchemy/migrate_repo/versions/247_nullable_mismatch.py

Changed in nova:
status: Triaged → Invalid
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers