Migrate fails to downgrade database

[Simon Chang]

trove-manage db_downgrade command fail to downgrade trove database to before v30. Here are the steps to replicate the issue, error details and workaround.

trove-manage db_sync

trove-manage db_downgrade 29
Migration script: 030_add_master_slave.py

Error:
TRACE root OperationalError: (OperationalError) (1025, "Error on rename of './trove/#sql-4300_76' to './trove/instances' (errno: 150)") '\nALTER TABLE instances DROP COLUMN slave_of_id' ()

Workaround: Delete the foreign key associated with the slave_of_id column in the instances table.

********

trove-manage db_downgrade 28
Migration script: 029_add_backup_datastore.py

Error:
TRACE root OperationalError: (OperationalError) (1025, "Error on rename of './trove/#sql-4300_84' to './trove/backups' (errno: 150)") '\nALTER TABLE backups DROP COLUMN datastore_version_id' ()

Workaround: Delete the foreign key associated with the datastore_version_id column in the backups table.

********

trove-manage db_downgrade 26
Migration script: 027_add_datastore_capabilities.py

Error:
TRACE root IntegrityError: (IntegrityError) (1217, 'Cannot delete or update a parent row: a foreign key constraint fails') '\nDROP TABLE capabilities' ()

Workaround: Reverse the drop table sequence in the downgrade method of 027_add_datastore_capabilities.py ...
i.e. instead of drop_tables([capabilities, capability_overrides]), do this: drop_tables([capability_overrides, capabilities])

********

trove-manage db_downgrade 15
Migration script: 016_add_datastore_type.py

Error:
TRACE root IntegrityError: (IntegrityError) (1217, 'Cannot delete or update a parent row: a foreign key constraint fails') '\nDROP TABLE datastores' ()

Workaround: Delete the foreign key associated with the datastore_id column in the datastore_versions table

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/113582
Committed: https://git.openstack.org/cgit/openstack/trove/commit/?id=06e0aa25a066cb889fb8cb72708dd504cf84a4bd
Submitter: Jenkins
Branch: master

commit 06e0aa25a066cb889fb8cb72708dd504cf84a4bd
Author: Simon Chang <email address hidden>
Date: Tue Aug 12 12:24:54 2014 -0400

    Fixed database migration script issues

    The db downgrade scripts are currently not dropping foreign key
    constraints, causing errors when the script tries to drop related
    tables on downgrade.

    This commit address issues in the migration scripts, and also
    introduces a new test script to test the migration scripts, so that
    issues can be prevented in the future. The new test script is based
    on the existing migration test script implementated in Nova.

    Change-Id: I240d81afc3e43fd3711de8c156cfb43fd14850bf
    Closes-Bug: #1347114