keystone-manage db_sync with DB2 fails

[Brant Knudson]

I set up my system with DB2 according to https://wiki.openstack.org/wiki/DB2Enablement

Ran keystone-manage db_sync, and got this error:

...
 File "/opt/stack/keystone/keystone/common/sql/migrate_repo/versions/011_endpoints_v3.py", line 26, in upgrade
    legacy_table.rename('endpoint_v2')
...
sqlalchemy.exc.ProgrammingError: (ProgrammingError)
ibm_db_dbi::ProgrammingError: Statement Execute Failed:
[IBM][CLI Driver][DB2/LINUXX8664] SQL0750N
The statement failed because the table or column cannot be renamed.
SQLSTATE=42986 SQLCODE=-750 'RENAME TABLE endpoint TO endpoint_v2' ()

The problem is that DB2 will not allow you to rename a table if it's got a constraint on it (a unique or foreign key constraint). See
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Ft0020130.html

The suggested fix for this is to drop the unique or FK constraints before renaming and then restoring the FK constraints.

The FK constraint problem affects migrations 11 and 13. The unique constraint problem affects migration 16.

The next problem is that the upgrade process deadlocks in migration 16. This is because with DB2 a lock is held in upgrade_user_table_with_col_create and it's not released with a commit(), then later upgrade_project_table_with_col_create tries to get the same lock and now we're deadlocked. The suggested fix is to commit the transaction in upgrade_user_table_with_col_create.

Also ran into a similar problem as mysql on migration 23, but ayoung has a fix for this.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/32207
Committed: http://github.com/openstack/keystone/commit/67157406ea2e15bea3322b64c67680db184554ef
Submitter: Jenkins
Branch: master

commit 67157406ea2e15bea3322b64c67680db184554ef
Author: Brant Knudson <email address hidden>
Date: Fri Jun 7 17:23:04 2013 -0500

    Commit transaction in migration

    Some databases (e.g., DB2) would deadlock on migration 16 because a
    lock was held on the domain table for an uncommitted alter and then
    SQLAlchemy queried the domain table info in a separate transaction.

    The fix is to commit the ALTER statements to release the lock so
    that the domain table schema query will not block.

    I've run this with sqlite, mysql, and postgresql and they all worked.

    Part of fix for bug 1188785

    Change-Id: Ic540a6cb09a0c525df7aaea55b64af96f0dd87c7

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/32191
Committed: http://github.com/openstack/keystone/commit/052e7d525146c235f3c4ddc729ee6e1ac18a1337
Submitter: Jenkins
Branch: master

commit 052e7d525146c235f3c4ddc729ee6e1ac18a1337
Author: Brant Knudson <email address hidden>
Date: Fri Jun 7 14:47:49 2013 -0500

    DB2 migration support

    DB2 will not allow you to rename a table if it's got a
    constraint on it (a unique or foreign key constraint).

    This fix changes the migrations so that the unique or FK
    constraints are dropped from tables before renaming and then
    restoring the unique FK constraints. This works for DB2 and
    other DBMSs that support FK constraints such as MySQL with
    InnoDB and PostgreSQL.

    Also, for DB2, give a name to the unique constraints so that
    they can be manipulated.

    Fixes bug 1188785

    Change-Id: I7cf6ab42084e43d827ed827c64025e61e72a4672