Open Stack

Hi Blair,

Thanks for the clarifications, that helps.

At the moment I'm trying to determine how you ended up with both fkeys 
so I can ensure the problem is properly fixed. What version did you 
first start deploying openstack from? Essex? and have you been just 
upgrading with releases or RC's or upstream etc.

Thanks,
Josh

Rackspace Australia

On 10/30/13 10:10 AM, Blair Zajac wrote:
> On 10/29/2013 04:04 PM, Joshua Hesketh wrote:
>> Hi Blair,
>>
>> So from your dump a few weeks ago you have both fkeys
>> 'instance_info_caches_ibfk_1' and
>> 'instance_info_caches_instance_uuid_fkey'?
>
> Hi Joshua,
>
> Yes, that's correct.
>
> Yet your original bug report
>> only lists the former key. Was the original bug report after attempting
>> to run migration 185 and failing (ie, no rollback)? If so I suspect
>> migration 185 dropped the 'instance_info_caches_instance_uuid_fkey' fkey
>> before failing. Perhaps the patch needs to check if either or exist.
>
> Yes, my first email showing the table schema was after 185 and failing 
> with no rollback.
>
> Yes, the original bug report was generated by showing the table 
> creation after 185 failed.
>
>> Similar question, was the dump you have just shown now after 185 failed
>> with my patch? That would also be why you can't see the old fkey.
>
> Yes, that's correct.
>
> And the dump of my db from a week ago shows both FKs:
>
> -- 
> -- Table structure for table `instance_info_caches`
> -- 
>
> DROP TABLE IF EXISTS `instance_info_caches`;
> /*!40101 SET @saved_cs_client     = @@character_set_client */;
> /*!40101 SET character_set_client = utf8 */;
> CREATE TABLE `instance_info_caches` (
>   `created_at` datetime DEFAULT NULL,
>   `updated_at` datetime DEFAULT NULL,
>   `deleted_at` datetime DEFAULT NULL,
>   `id` int(11) NOT NULL AUTO_INCREMENT,
>   `network_info` mediumtext,
>   `instance_uuid` varchar(36) NOT NULL,
>   `deleted` int(11) DEFAULT NULL,
>   PRIMARY KEY (`id`),
>   UNIQUE KEY `instance_uuid` (`instance_uuid`),
>   CONSTRAINT `instance_info_caches_ibfk_1` FOREIGN KEY 
> (`instance_uuid`) REFERENCES `instances` (`uuid`),
>   CONSTRAINT `instance_info_caches_instance_uuid_fkey` FOREIGN KEY 
> (`instance_uuid`) REFERENCES `instances` (`uuid`)
> ) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8;
> /*!40101 SET character_set_client = @saved_cs_client */;
>
> -- 
> -- Dumping data for table `instance_info_caches`
> -- 
>
>
> I didn't go looking to see what was going on until the patch 185 
> migration also failed.
>
> Regards,
> Blair
>
>>
>> Thanks,
>> Josh
>>
>> Rackspace Australia
>>
>> On 10/29/13 3:50 PM, Blair Zajac wrote:
>>> On 10/28/13 6:17 PM, Joshua Hesketh wrote:
>>>> Hi guys,
>>>>
>>>> I have a patch in the works against this that will hopefully fix your
>>>> problems:
>>>> https://review.openstack.org/#/c/54212/
>>>>
>>>> One of the gotchas though will be if you have already ran migration
>>>> 185 you
>>>> can't run it again (even if it failed because it'll try and do
>>>> operations that
>>>> it got part way through before).
>>>>
>>>> Once the patch is merged you may have to restore your database to a
>>>> previous and
>>>> run the upgrade again.
>>>
>>> Hi Josh,
>>>
>>> Thanks for the patch.  I went back to a mysqldump from a week ago and
>>> loaded it into MySQL and dropped the new
>>> shadow_security_group_default_rules table. However, I'm getting the
>>> same failure, even though that constraint is gone:
>>>
>>> mysql> show create table instance_info_caches \G
>>> *************************** 1. row ***************************
>>>        Table: instance_info_caches
>>> Create Table: CREATE TABLE `instance_info_caches` (
>>>   `created_at` datetime DEFAULT NULL,
>>>   `updated_at` datetime DEFAULT NULL,
>>>   `deleted_at` datetime DEFAULT NULL,
>>>   `id` int(11) NOT NULL AUTO_INCREMENT,
>>>   `network_info` mediumtext,
>>>   `instance_uuid` varchar(36) NOT NULL,
>>>   `deleted` int(11) DEFAULT NULL,
>>>   PRIMARY KEY (`id`),
>>>   UNIQUE KEY `instance_uuid` (`instance_uuid`),
>>>   CONSTRAINT `instance_info_caches_instance_uuid_fkey` FOREIGN KEY
>>> (`instance_uuid`) REFERENCES `instances` (`uuid`)
>>> ) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8
>>>
>>>
>>> mysql> alter table instance_info_caches drop index instance_uuid;
>>> ERROR 1553 (HY000): Cannot drop index 'instance_uuid': needed in a
>>> foreign key constraint
>>>
>>>
>>> Looking at my dump from a week ago, it appears there's two identical
>>> constraints with different names:
>>>
>>>
>>>   CONSTRAINT `instance_info_caches_ibfk_1` FOREIGN KEY
>>> (`instance_uuid`) REFERENCES `instances` (`uuid`),
>>>   CONSTRAINT `instance_info_caches_instance_uuid_fkey` FOREIGN KEY
>>> (`instance_uuid`) REFERENCES `instances` (`uuid`)
>>>
>>>
>>> Blair
>>>
>>
>