DB migration 209 can fail when deleting from instance_actions

[Johannes Erdfelt]

Migration 209 adds foreign key constraints that were defined in the model but never applied as part of a migration.

Since the foreign key didn't exist previously, there may be rows that need to be deleted before the foreign key constraint is added.

It does this by selecting the list of rows that reference a nonexistant row in the parent table. It first dumps the rows into a dump table and then deletes the rows.

However, the instance_actions table is a parent in a foreign key relationship with the instance_actions_events table. As a result, if rows need to be deleted from the instance_actions table, this exception similar to this will be raised:

   File "nova/db/sqlalchemy/migrate_repo/versions/209_add_missing_foreign_keys.py", line 67, in upgrade
     migrate_engine.execute(sql_del)
   File "site-packages/sqlalchemy/engine/base.py", line 2446, in execute
     return connection.execute(statement, *multiparams, **params)
   File "site-packages/sqlalchemy/engine/base.py", line 1449, in execute
     params)
   File "site-packages/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
     compiled_sql, distilled_params
   File "site-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
     context)
   File "site-packages/sqlalchemy/engine/base.py", line 1850, in _handle_dbapi_exception
     None, sys.exc_info()[2]
 IntegrityError: (IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`nova`.`instance_actions_events`, CONSTRAINT `instance_actions_events_ibfk_1` FOREIGN KEY (`action_id`) REFERENCES `instance_actions` (`id`))') 'DELETE FROM instance_actions WHERE instance_actions.instance_uuid NOT IN (SELECT instances.uuid \nFROM instances)' ()

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/58384
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=7ea026e8d07720a1c368bee175a3a0df632a3fbe
Submitter: Jenkins
Branch: master

commit 7ea026e8d07720a1c368bee175a3a0df632a3fbe
Author: Johannes Erdfelt <email address hidden>
Date: Mon Nov 25 18:34:00 2013 +0000

    DB migration 209: Clean up child rows as well

    Fixes bug 1252891

    In order to add missing foreign key constraints, rows that reference a
    nonexistant key need to be purged. Unfortunately, DB migration 209 does
    not clean up any rows in child tables that themselves have a foreign key
    constraint on the tables with the missing foreign keys. This can result
    in an integrity violation causing the migration to fail.

    When purging the rows, make sure to purge rows in any child tables first.

    Change-Id: I25bd899383c72cb0670819b030759630ac89acaa