Open Stack

Well, if you create that index , it will fail on the next one and you'll still be in this state.

I tried manually deleting the index, foreign key and everything but I always got a 150 or 152 error where it can't rename the table after deleting the index  or foreign key (correct me if I'm wrong on this part) ...

Dave

-----Original Message-----
From: Blair Zajac [mailto:blair at orcaware.com] 
Sent: October-27-13 7:48 PM
To: David Hill; Michael Still
Cc: openstack at lists.openstack.org
Subject: Re: [Openstack] Grizzly -> Havana nova upgrade failure: Cannot drop index 'instance_uuid'

I haven't touched the schema since the 'db sync' failed, so I can send any 
information anyone needs.

Blair

On 10/27/13 4:46 PM, David Hill wrote:
> All parts involving indexes and foreign keys in revision 185 failed.
>
> I had to create them by hand and  in some cases I wasn't able to drop foreign keys or indexes... gave error 150 and 152.
>
> I could (maybe) restore a backup of that mysql database tomorrow and try the db sync again but I'm not sure I have a backup since it's the lab of the lab I'm using.
>
> Dave
>
>
> -----Original Message-----
> From: mikalstill at gmail.com [mailto:mikalstill at gmail.com] On Behalf Of Michael Still
> Sent: October-27-13 7:39 PM
> To: David Hill
> Cc: Blair Zajac; openstack at lists.openstack.org
> Subject: Re: [Openstack] Grizzly -> Havana nova upgrade failure: Cannot drop index 'instance_uuid'
>
> These sound like bugs worth filing...
>
> Is it too late to get a list of tables / indexes you had problems with?
>
> Michael
>
> On Mon, Oct 28, 2013 at 10:34 AM, David Hill <david.hill at ubisoft.com> wrote:
>> Hi guys,
>>
>>          I experienced the same issue while upgrading from Grizzly to Havana ...  I had to create manually some indexes before it could drop them! :(
>>
>> Dave
>>
>>
>> -----Original Message-----
>> From: Michael Still [mailto:mikal at stillhq.com]
>> Sent: October-27-13 6:06 PM
>> To: Blair Zajac
>> Cc: openstack at lists.openstack.org
>> Subject: Re: [Openstack] Grizzly -> Havana nova upgrade failure: Cannot drop index 'instance_uuid'
>>
>> Can you run this against your nova mysql database and let us know the result?
>>
>> show create table instance_info_caches \G
>>
>> Thanks,
>> Michael
>>
>> On Sun, Oct 27, 2013 at 7:58 PM, Blair Zajac <blair at orcaware.com> wrote:
>>> Hello,
>>>
>>> I was running Ubuntu 13.04 and upgraded tonight to 13.10.  I was running the
>>> Ubuntu Precise 1:2013.1.3-0ubuntu1.1 on 13.04 and am now on 13.10's provided
>>> 1:2013.2~rc2-0ubuntu1.
>>>
>>> After getting the box up, migrating the nova database failed with the below
>>> error.  I am using MySQL.
>>>
>>> Suggestions on how to fix this?
>>>
>>> Thanks,
>>> Blair
>>>
>>>
>>> # nova-manage -v db sync
>>> 2013-10-27 01:47:03.615 24457 INFO migrate.versioning.api [-] 161 -> 162...
>>> 2013-10-27 01:47:03.673 24457 INFO migrate.versioning.api [-] done
>>> ...
>>> ...
>>> 2013-10-27 01:47:16.373 24457 INFO migrate.versioning.api [-] 184 -> 185...
>>> Command failed, please check log for more info
>>> 2013-10-27 01:47:17.835 24457 CRITICAL nova [-] (OperationalError) (1553,
>>> "Cannot drop index 'instance_uuid': needed in a foreign key constraint")
>>> 'ALTER TABLE instance_info_caches DROP INDEX instance_uuid' ()
>>> 2013-10-27 01:47:17.835 24457 TRACE nova Traceback (most recent call last):
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File "/usr/bin/nova-manage", line
>>> 10, in <module>
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     sys.exit(main())
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/nova/cmd/manage.py", line 1377, in main
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     ret = fn(*fn_args, **fn_kwargs)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/nova/cmd/manage.py", line 885, in sync
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     return
>>> migration.db_sync(version)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/nova/db/migration.py", line 33, in db_sync
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     return
>>> IMPL.db_sync(version=version)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migration.py", line 75,
>>> in db_sync
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     return
>>> versioning_api.upgrade(get_engine(), repository, version)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/migrate/versioning/api.py", line 186, in
>>> upgrade
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     return _migrate(url,
>>> repository, version, upgrade=True, err=err, **opts)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File "<string>", line 2, in
>>> _migrate
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migration.py", line 40,
>>> in patched_with_engine
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     return f(*a, **kw)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/migrate/versioning/api.py", line 366, in
>>> _migrate
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     schema.runchange(ver, change,
>>> changeset.step)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/migrate/versioning/schema.py", line 91, in
>>> runchange
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     change.run(self.engine, step)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/migrate/versioning/script/py.py", line
>>> 145, in run
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     script_func(engine)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migrate_repo/versions/185_rename_unique_constraints.py",
>>> line 129, in upgrade
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     return
>>> _uc_rename(migrate_engine, upgrade=True)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migrate_repo/versions/185_rename_unique_constraints.py",
>>> line 112, in _uc_rename
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     old_name, *(columns))
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/utils.py", line 197, in
>>> drop_unique_constraint
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     uc.drop()
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/migrate/changeset/constraint.py", line 59,
>>> in drop
>>> 2013-10-27 01:47:17.835 24457 TRACE nova
>>> self.__do_imports('constraintdropper', *a, **kw)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/migrate/changeset/constraint.py", line 32,
>>> in __do_imports
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     run_single_visitor(engine,
>>> visitorcallable, self, *a, **kw)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/migrate/changeset/databases/visitor.py",
>>> line 75, in run_single_visitor
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     fn(element, **kwargs)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/migrate/changeset/ansisql.py", line 272,
>>> in visit_migrate_unique_constraint
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     self._visit_constraint(*p, **k)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/migrate/changeset/ansisql.py", line 284,
>>> in _visit_constraint
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     self.execute()
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/migrate/changeset/ansisql.py", line 42, in
>>> execute
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     return
>>> self.connection.execute(self.buffer.getvalue())
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in
>>> execute
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     params)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 805, in
>>> _execute_text
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     statement, parameters
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in
>>> _execute_context
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     context)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in
>>> _handle_dbapi_exception
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     exc_info
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 195, in
>>> raise_from_cause
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     reraise(type(exception),
>>> exception, tb=exc_tb)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 867, in
>>> _execute_context
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     context)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324,
>>> in do_execute
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     cursor.execute(statement,
>>> parameters)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     self.errorhandler(self, exc,
>>> value)
>>> 2013-10-27 01:47:17.835 24457 TRACE nova   File
>>> "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in
>>> defaulterrorhandler
>>> 2013-10-27 01:47:17.835 24457 TRACE nova     raise errorclass, errorvalue
>>> 2013-10-27 01:47:17.835 24457 TRACE nova OperationalError:
>>> (OperationalError) (1553, "Cannot drop index 'instance_uuid': needed in a
>>> foreign key constraint") 'ALTER TABLE instance_info_caches DROP INDEX
>>> instance_uuid' ()
>>> 2013-10-27 01:47:17.835 24457 TRACE nova