← Back to team overview

openstack team mailing list archive

Problems with sqlalchemy migrate and foreign key constraints

 

Hi. I've spent ages on this and can't get myself unstuck. I have a DB
schema update like this:

def upgrade(migrate_engine):
    meta = MetaData()
    meta.bind = migrate_engine
    instances = Table('instances', meta, autoload=True)
    instance_info_caches = Table('instance_info_caches', meta,
                                 autoload=True)

    # We need to remove the foreign key constraint or the column rename
    # will fail
    fkeys = list(instance_info_caches.c.instance_id.foreign_keys)
    try:
        fkey_name = fkeys[0].constraint.name
        ForeignKeyConstraint(
            columns=[instance_info_caches.c.instance_id],
            refcolumns=[instances.c.uuid],
            name=fkey_name).drop()
    except Exception:
        LOG.error(_("foreign key constraint couldn't be removed"))
        raise

    instance_info_caches.c.instance_id.alter(name='instance_uuid')

    # Add the foreign key constraint back
    try:
        ForeignKeyConstraint(
            columns=[instance_info_caches.c.instance_uuid],
            refcolumns=[instances.c.uuid]).create()
    except Exception:
        LOG.error(_("foreign key constraint couldn't be created"))
        raise

This gives me this error:

[snip]
2012-06-22 11:18:33 TRACE nova   File
"/opt/stack/nova/nova/db/sqlalchemy/migrate_repo/versions/105_instance_info_caches_uses_uuid.py",
line 53, in upgrade
2012-06-22 11:18:33 TRACE nova
columns=[instance_info_caches.c['instance_uuid']],
2012-06-22 11:18:33 TRACE nova   File
"/usr/lib/python2.7/dist-packages/sqlalchemy/util.py", line 797, in
__getitem__
2012-06-22 11:18:33 TRACE nova     return self._data[key]
2012-06-22 11:18:33 TRACE nova KeyError: 'instance_uuid'
2012-06-22 11:18:33 TRACE nova

It looks to me like the old column name is being left in
instance_info_caches.c, although trying to use the old name doesn't work
as well. I suspect this is because the SQL for the alter hasn't actually
run yet.

Is there some way to do this that I can't think of, or should I put the
new foreign key constraint in a separate DB migration? The problem with
that path is I'm not sure how to do the downgrade() step, as it would
have the same problem in reverse.

Thanks,
Mikal