[Openstack] Problems with sqlalchemy migrate and foreign key constraints
Michael Still
michael.still at canonical.com
Fri Jun 22 01:27:01 UTC 2012
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
More information about the Openstack
mailing list