glance db sync failed when using DB2

[Jun Hong Li]

The DB2 CI on nova project was blocked by glance db sync.

Software versions:

SQLAlchemy (0.9.8)
sqlalchemy-migrate (0.9.5)
ibm-db (2.0.4.1)
ibm-db-alembic (0.1.0) with patch https://code.google.com/p/ibm-db/source/detail?r=74221d0d47a9aad3b1a28c9a5a5de41880136560&repo=ibm-db-alembic
ibm-db-sa (0.3.2) with patch https://code.google.com/p/ibm-db/source/detail?r=f560ba1e0d8210c1498c3db35489eb471001749a&repo=ibm-db-sa

2015-03-19 09:06:19.182 | 2015-03-19 17:06:19.182 13546 INFO migrate.versioning.api [-] 38 -> 39...
2015-03-19 09:06:19.335 | 2015-03-19 17:06:19.332 13546 ERROR oslo_db.sqlalchemy.exc_filters [-] DBAPIError exception wrapped from (ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0669N A system required index cannot be dropped explicitly. SQLSTATE=42917 SQLCODE=-669 '\nDROP INDEX ix_namespaces_namespace' ()
2015-03-19 09:06:19.335 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2015-03-19 09:06:19.335 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
2015-03-19 09:06:19.335 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters context)
2015-03-19 09:06:19.335 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/ibm_db_sa-0.3.2-py2.7.egg/ibm_db_sa/ibm_db.py", line 106, in do_execute
2015-03-19 09:06:19.335 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2015-03-19 09:06:19.335 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/ibm_db_dbi.py", line 1332, in execute
2015-03-19 09:06:19.336 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters self._execute_helper(parameters)
2015-03-19 09:06:19.336 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/ibm_db_dbi.py", line 1244, in _execute_helper
2015-03-19 09:06:19.336 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters raise self.messages[len(self.messages) - 1]
2015-03-19 09:06:19.336 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters ProgrammingError: ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0669N A system required index cannot be dropped explicitly. SQLSTATE=42917 SQLCODE=-669
2015-03-19 09:06:19.336 | 2015-03-19 17:06:19.332 13546 TRACE oslo_db.sqlalchemy.exc_filters
2015-03-19 09:06:19.458 | 2015-03-19 17:06:19.342 13546 CRITICAL glance [-] DBError: (ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0669N A system required index cannot be dropped explicitly. SQLSTATE=42917 SQLCODE=-669 '\nDROP INDEX ix_namespaces_namespace' ()
2015-03-19 09:06:19.459 | 2015-03-19 17:06:19.342 13546 TRACE glance Traceback (most recent call last):
2015-03-19 09:06:19.459 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/bin/glance-manage", line 10, in <module>
2015-03-19 09:06:19.460 | 2015-03-19 17:06:19.342 13546 TRACE glance sys.exit(main())
2015-03-19 09:06:19.460 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/opt/stack/new/glance/glance/cmd/manage.py", line 303, in main
2015-03-19 09:06:19.460 | 2015-03-19 17:06:19.342 13546 TRACE glance return CONF.command.action_fn()
2015-03-19 09:06:19.460 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/opt/stack/new/glance/glance/cmd/manage.py", line 171, in sync
2015-03-19 09:06:19.460 | 2015-03-19 17:06:19.342 13546 TRACE glance CONF.command.current_version)
2015-03-19 09:06:19.461 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/opt/stack/new/glance/glance/cmd/manage.py", line 116, in sync
2015-03-19 09:06:19.461 | 2015-03-19 17:06:19.342 13546 TRACE glance version)
2015-03-19 09:06:19.461 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/oslo_db/sqlalchemy/migration.py", line 79, in db_sync
2015-03-19 09:06:19.461 | 2015-03-19 17:06:19.342 13546 TRACE glance return versioning_api.upgrade(engine, repository, version)
2015-03-19 09:06:19.461 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/migrate/versioning/api.py", line 186, in upgrade
2015-03-19 09:06:19.462 | 2015-03-19 17:06:19.342 13546 TRACE glance return _migrate(url, repository, version, upgrade=True, err=err, **opts)
2015-03-19 09:06:19.462 | 2015-03-19 17:06:19.342 13546 TRACE glance File "<string>", line 2, in _migrate
2015-03-19 09:06:19.462 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/migrate/versioning/util/__init__.py", line 160, in with_engine
2015-03-19 09:06:19.462 | 2015-03-19 17:06:19.342 13546 TRACE glance return f(*a, **kw)
2015-03-19 09:06:19.462 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/migrate/versioning/api.py", line 366, in _migrate
2015-03-19 09:06:19.462 | 2015-03-19 17:06:19.342 13546 TRACE glance schema.runchange(ver, change, changeset.step)
2015-03-19 09:06:19.462 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/migrate/versioning/schema.py", line 93, in runchange
2015-03-19 09:06:19.462 | 2015-03-19 17:06:19.342 13546 TRACE glance change.run(self.engine, step)
2015-03-19 09:06:19.462 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/migrate/versioning/script/py.py", line 148, in run
2015-03-19 09:06:19.462 | 2015-03-19 17:06:19.342 13546 TRACE glance script_func(engine)
2015-03-19 09:06:19.463 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/opt/stack/new/glance/glance/db/sqlalchemy/migrate_repo/versions/039_add_changes_to_satisfy_models_metadef.py", line 34, in upgrade
2015-03-19 09:06:19.463 | 2015-03-19 17:06:19.342 13546 TRACE glance Index('ix_namespaces_namespace', metadef_namespaces.c.namespace).drop()
2015-03-19 09:06:19.463 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/schema.py", line 2975, in drop
2015-03-19 09:06:19.463 | 2015-03-19 17:06:19.342 13546 TRACE glance bind._run_visitor(ddl.SchemaDropper, self)
2015-03-19 09:06:19.463 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1616, in _run_visitor
2015-03-19 09:06:19.463 | 2015-03-19 17:06:19.342 13546 TRACE glance conn._run_visitor(visitorcallable, element, **kwargs)
2015-03-19 09:06:19.463 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1245, in _run_visitor
2015-03-19 09:06:19.463 | 2015-03-19 17:06:19.342 13546 TRACE glance **kwargs).traverse_single(element)
2015-03-19 09:06:19.463 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 120, in traverse_single
2015-03-19 09:06:19.463 | 2015-03-19 17:06:19.342 13546 TRACE glance return meth(obj, **kw)
2015-03-19 09:06:19.464 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 813, in visit_index
2015-03-19 09:06:19.464 | 2015-03-19 17:06:19.342 13546 TRACE glance self.connection.execute(DropIndex(index))
2015-03-19 09:06:19.464 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 729, in execute
2015-03-19 09:06:19.464 | 2015-03-19 17:06:19.342 13546 TRACE glance return meth(self, multiparams, params)
2015-03-19 09:06:19.464 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 69, in _execute_on_connection
2015-03-19 09:06:19.464 | 2015-03-19 17:06:19.342 13546 TRACE glance return connection._execute_ddl(self, multiparams, params)
2015-03-19 09:06:19.464 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 783, in _execute_ddl
2015-03-19 09:06:19.464 | 2015-03-19 17:06:19.342 13546 TRACE glance compiled
2015-03-19 09:06:19.464 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
2015-03-19 09:06:19.464 | 2015-03-19 17:06:19.342 13546 TRACE glance context)
2015-03-19 09:06:19.465 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/oslo_db/sqlalchemy/compat/handle_error.py", line 261, in _handle_dbapi_exception
2015-03-19 09:06:19.465 | 2015-03-19 17:06:19.342 13546 TRACE glance e, statement, parameters, cursor, context)
2015-03-19 09:06:19.465 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1155, in _handle_dbapi_exception
2015-03-19 09:06:19.465 | 2015-03-19 17:06:19.342 13546 TRACE glance util.raise_from_cause(newraise, exc_info)
2015-03-19 09:06:19.465 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
2015-03-19 09:06:19.465 | 2015-03-19 17:06:19.342 13546 TRACE glance reraise(type(exception), exception, tb=exc_tb)
2015-03-19 09:06:19.465 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
2015-03-19 09:06:19.465 | 2015-03-19 17:06:19.342 13546 TRACE glance context)
2015-03-19 09:06:19.465 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/ibm_db_sa-0.3.2-py2.7.egg/ibm_db_sa/ibm_db.py", line 106, in do_execute
2015-03-19 09:06:19.465 | 2015-03-19 17:06:19.342 13546 TRACE glance cursor.execute(statement, parameters)
2015-03-19 09:06:19.466 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/ibm_db_dbi.py", line 1332, in execute
2015-03-19 09:06:19.466 | 2015-03-19 17:06:19.342 13546 TRACE glance self._execute_helper(parameters)
2015-03-19 09:06:19.466 | 2015-03-19 17:06:19.342 13546 TRACE glance File "/usr/local/lib/python2.7/dist-packages/ibm_db_dbi.py", line 1244, in _execute_helper
2015-03-19 09:06:19.466 | 2015-03-19 17:06:19.342 13546 TRACE glance raise self.messages[len(self.messages) - 1]
2015-03-19 09:06:19.466 | 2015-03-19 17:06:19.342 13546 TRACE glance DBError: (ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0669N A system required index cannot be dropped explicitly. SQLSTATE=42917 SQLCODE=-669 '\nDROP INDEX ix_namespaces_namespace' ()
2015-03-19 09:06:19.466 | 2015-03-19 17:06:19.342 13546 TRACE glance

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/167101

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/167101
Committed: https://git.openstack.org/cgit/openstack/glance/commit/?id=6264117879b12d44df6c9241bd2ff89b9cb5f761
Submitter: Jenkins
Branch: master

commit 6264117879b12d44df6c9241bd2ff89b9cb5f761
Author: Jun Hong Li <email address hidden>
Date: Fri Mar 20 18:01:48 2015 +0800

    Fix the db_sync problem in 039 for db2

    When using db2 as the database backend, it encounters problem
    when running db_sync in step 039 due to "SQL0669N A system
    required index cannot be dropped explicitly", which need to
    drop the constraint first then recreate the index. And this
    patch also fixes the problem of dropping some indexes which
    only are created in non-db2 databases, meanwhile, it will not
    break the logic for all other database backends.

    Closes-Bug: #1434440

    Change-Id: I628bfa9ab1e4e07b71b49fc0e5a6087679b642a6