Credential table in the backed still has foreign key reference to id column in tenant table (which is dropped).

[Nachiappan]

Credential table in the backed still has foreign key reference to id column in tenant table

sqlite> .schema credential
CREATE TABLE credential (
 id VARCHAR(64) NOT NULL,
 user_id VARCHAR(64) NOT NULL,
 project_id VARCHAR(64),
 blob TEXT NOT NULL,
 type VARCHAR(255) NOT NULL,
 extra TEXT,
 PRIMARY KEY (id),
 FOREIGN KEY(user_id) REFERENCES user (id),
 FOREIGN KEY(project_id) REFERENCES tenant (id)
);

There is a bug in the sql migration code where we drop the foreign key constraint.
https://github.com/openstack/keystone/blob/master/keystone/common/sql/migrate_repo/versions/023_drop_credential_constraints.py

[Nachiappan (nachiappan)]

From what i understood, is when we try to load the credential table,

https://github.com/openstack/keystone/blob/master/keystone/common/sql/migrate_repo/versions/023_drop_credential_constraints.py#L27

since it has foreign key reference to tenant table which has been dropped in the previous migration, the credential table does not get loaded and the sql migration would have failed. So that is the reason we still observe the foreign key constraint.

[OpenStack Infra (hudson-openstack)]

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

[Brant Knudson (blk-u)]

What problem does this cause?

[Nachiappan (nachiappan)]

Brant Knudson: If sqlite is used as database, the problem was not able to load the credential table in the new migration script using sqlalchemy. I am working on the blueprint for migrating the data from ec2_credential table to credential table https://blueprints.launchpad.net/keystone/+spec/migrate-ec2-credentials.

When i try to load the credential table using sqlachemy, i get the following error message.

  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 260, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py", line 412, in reflecttable
    raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError: tenant

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/38219
Committed: http://github.com/openstack/keystone/commit/0a401529947c98ab761256e233b157071a57ab70
Submitter: Jenkins
Branch: master

commit 0a401529947c98ab761256e233b157071a57ab70
Author: Nachiappan VR N <email address hidden>
Date: Mon Jul 22 15:50:33 2013 -0700

    Fixing broken credential schema in sqlite.

    Credential table has foreign key constraint
    referring to tenant table which is dropped.
    Since sqlite does not support alter table
    drop constraint, the foreign key constraint
    was not dropped. When we try to load credential
    table using sqlite backend it fails because tenant
    table does not exist. Fix is provided such that
    the credential table is recreated without foreign
    key constraint and the data is moved from old
    credential table to the new credential table.

    Fixes Bug #1190383

    Change-Id: I3afb04254f33e12fccb7da84c8674feba36622c8