Migration 185 does not downgrade on MySQL

[Joshua Hesketh]

When downgrading migration 185 I get the following error:

OperationalError: (OperationalError) (1061, "Duplicate key name 'virtual_interfaces_instance_uuid_fkey'") 'ALTER TABLE virtual_interfaces ADD CONSTRAINT virtual_interfaces_instance_uuid_fkey UNIQUE (address)' ()

The virtual_interfaces table has a virtual_interfaces_instance_uuid_fkey KEY before the upgrade. That is, before the upgrade, virtual_interfaces looks like so:

CREATE TABLE `virtual_interfaces` (
 `created_at` datetime DEFAULT NULL,
 `updated_at` datetime DEFAULT NULL,
 `deleted_at` datetime DEFAULT NULL,
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `address` varchar(255) DEFAULT NULL,
 `network_id` int(11) DEFAULT NULL,
 `uuid` varchar(36) DEFAULT NULL,
 `instance_uuid` varchar(36) DEFAULT NULL,
 `deleted` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `address` (`address`),
 KEY `network_id` (`network_id`),
 KEY `virtual_interfaces_instance_uuid_fkey` (`instance_uuid`),
 CONSTRAINT `virtual_interfaces_instance_uuid_fkey` FOREIGN KEY (`instance_uuid`) REFERENCES `instances` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

After the 185 upgrade the key still exists. I believe this is because it is not a UNIQUE key and utils.drop_unique_constraint fails silently. At version 185 virtual_interfaces looks like so:

CREATE TABLE `virtual_interfaces` (
 `created_at` datetime DEFAULT NULL,
 `updated_at` datetime DEFAULT NULL,
 `deleted_at` datetime DEFAULT NULL,
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `address` varchar(255) DEFAULT NULL,
 `network_id` int(11) DEFAULT NULL,
 `uuid` varchar(36) DEFAULT NULL,
 `instance_uuid` varchar(36) DEFAULT NULL,
 `deleted` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `address` (`address`),
 UNIQUE KEY `uniq_virtual_interfaces0address` (`address`),
 KEY `network_id` (`network_id`),
 KEY `virtual_interfaces_instance_uuid_fkey` (`instance_uuid`),
 CONSTRAINT `virtual_interfaces_instance_uuid_fkey` FOREIGN KEY (`instance_uuid`) REFERENCES `instances` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

So when downgrading 185 the virtual_interfaces_instance_uuid_fkey KEY is created again (albeit this time UNIQUE) and fails as a duplicate.

[OpenStack Infra (hudson-openstack)]

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

[Joshua Hesketh (joshua.hesketh)]

The goal here is to get migration 185 to leave the database in the same state as previously. Because the key `virtual_interfaces_instance_uuid_fkey` was never dropped (or never unique in the first place - the reason it wasn't dropped) we just don't try to recreate it on adding back in.

See https://review.openstack.org/39709

[Viktor Serhieiev (vsergeyev)]

Hello Joshua.

This bug caused by wrong unique name in unique constraints data. See line 66:

            (("address",), "virtual_interfaces_instance_uuid_fkey"),

This line should be:

            (("address",), "address"),

So this migrations drops wrong key and creates again the `virtual_interfaces_instance_uuid_fkey` KEY on downgrade.
Your patch fix it.

This migration also have one more side effect - we have two uniques on `address` column:

 UNIQUE KEY `address` (`address`),
 UNIQUE KEY `uniq_virtual_interfaces0address` (`address`),

It' would be nice to add one more migration to fix it.

Thanks.

[Joshua Hesketh (joshua.hesketh)]

Hi Victor,

Thanks for that. The column is fixed in migration 192.

Cheers,
Josh

[Shane Wang (shane-wang)]

Hi, Joshua,

Is this bug fixed? I saw your patch https://review.openstack.org/39709 was not merged yet. And I also saw the similar thing happened when I test migrations with mysql.

BR.
--
Shane

[Joshua Hesketh (joshua.hesketh)]

Hi Shane,

Yes my patch fixes this but is waiting a second core to approve.

Cheers,
Josh

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/39709
Committed: http://github.com/openstack/nova/commit/dc4e9976c8baf20cd988f80e2a476aae0e4d3813
Submitter: Jenkins
Branch: master

commit dc4e9976c8baf20cd988f80e2a476aae0e4d3813
Author: Joshua Hesketh <email address hidden>
Date: Thu Aug 1 23:55:45 2013 +1000

    Downgrade MySQL to the same state it used to be

    virtual_interfaces_instance_uuid_fkey exists as a KEY, not a UNIQUE KEY in
    MySQL which causes trouble when downgrading.

    Don't re-create the virtual_interfaces_instance_uuid_fkey on downgrade as it
    wasn't upgraded properly and therefore still exists. This forces the downgrade
    to look exactly like it was before the upgrade.

    Fixes bug 1207344

    Change-Id: Id1700e1cbba669df2dd8e5c5a842b859577aa32c