yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1349515] [NEW] Commit 0e98f5a breaks db migrations with DB2

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: Matt Riedemann <mriedem@xxxxxxxxxx>
Date: Mon, 28 Jul 2014 17:38:00 -0000
Reply-to: Bug 1349515 <1349515@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Public bug reported:

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.

** Affects: nova
     Importance: Undecided
     Assignee: Matt Riedemann (mriedem)
         Status: Triaged


** Tags: db db2

** Changed in: nova
       Status: New => Triaged

** Changed in: nova
     Assignee: (unassigned) => Matt Riedemann (mriedem)

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to OpenStack Compute (nova).
https://bugs.launchpad.net/bugs/1349515

Title:
  Commit 0e98f5a breaks db migrations with DB2

Status in OpenStack Compute (Nova):
  Triaged

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.

To manage notifications about this bug go to:
https://bugs.launchpad.net/nova/+bug/1349515/+subscriptions
Follow ups

[Bug 1349515] Re: Commit 0e98f5a breaks db migrations with DB2
From: Matt Riedemann, 2015-03-30
[Bug 1349515] [NEW] Commit 0e98f5a breaks db migrations with DB2
From: Matt Riedemann, 2014-07-28
References

[Bug 1349515] [NEW] Commit 0e98f5a breaks db migrations with DB2
From: Matt Riedemann, 2014-07-28