OpenStack Compute (nova)

Grizzly -> Havana nova upgrade failure: Cannot drop index 'instance_uuid'

Bug #1245502 reported by Blair Zajac on 2013-10-28

This bug affects 3 people

Affects		Status	Importance	Assigned to	Milestone
	OpenStack Compute (nova)	Fix Released	Undecided	Joshua Hesketh	OpenStack Compute (nova) 2014.1 "icehouse"
	Havana	Fix Released	Undecided	Unassigned	OpenStack Compute (nova) 2013.2.2

Bug Description

I was running Ubuntu 13.04 and upgraded 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.

# 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

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_ibfk_1` FOREIGN KEY (`instance_uuid`) REFERENCES `instances` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Discussed on the OpenStack mailing list and confirmed here:

http://lists.openstack.org/pipermail/openstack/2013-October/002414.html

Tags:

Roman Podoliaka (rpodolyaka) on 2013-10-28

Changed in nova:
status:	New → Incomplete
status:	Incomplete → Confirmed

Revision history for this message

Roman Podoliaka (rpodolyaka) wrote on 2013-10-28:

This is odd, but known behavior of MySQL: it doesn't allow one to drop a unique constraint if one or more columns of the latter are used a FK. So the solution is to drop the FK first, drop the unique and then recreate the FK. And this is what migration 185 does.

The problem with this particular migration is that your FK name doesn't correspond the one we have in the original Folsom migration (133_folsom.py): instance_info_caches_ibfk_1 vs instance_info_caches_instance_uuid_fkey. The name instance_info_caches_ibfk_1 comes from the Essex initial migration (082_essex.py).

Did you upgrade this installation from Folsom to Grizzly before?

From what I see, we have a bug in migration 133_folsom.py, which accidentally changed the name of the FK constraint when all pre-folsom migrations were compacted.

Revision history for this message

Blair Zajac (blair) wrote on 2013-10-28:

Yes, this install started with 2012.2.1 at a minimum and I've been upgrading it through releases.

So would renaming instance_info_caches_ibfk_1 to instance_info_caches_instance_uuid_fkey and then rerunning 'db sync' work?

Revision history for this message

David Hill (david-hill-ubisoft) wrote on 2013-10-28:

*Takes a deep breath* I have the same issue and we update from cactus to disablo to essex to folsom to grizzly to havana...

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2013-10-29: Fix proposed to nova (master)

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

Joshua Hesketh (joshua.hesketh) on 2013-10-29

Changed in nova:
assignee:	nobody → Joshua Hesketh (joshua.hesketh)

Revision history for this message

Roman Podoliaka (rpodolyaka) wrote on 2013-10-29:

@Blair

Yes, renaming of the FK constraint and rerunning 'db sync' should help.

Revision history for this message

Roman Podoliaka (rpodolyaka) wrote on 2013-10-29:

I asked Svetlana Shturm to take a look at this one. She's been working hard on writing a test for finding differences between two DB schemas. I hope, using this test we can fix all the variances the compacted migrations brought, so pre-Grizzly -> Havana upgrade could be done.

OpenStack Infra (hudson-openstack) on 2013-10-30

Changed in nova:
status:	Confirmed → In Progress

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2013-11-13: Fix merged to nova (master)

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

commit c620cafb700ca195db0bd0ef9d62a0c9459bdc38
Author: Joshua Hesketh <email address hidden>
Date: Tue Oct 29 09:40:41 2013 +1100

Fix migration 185 to work with old fkey names

    Migration 133_folsom.py accidentally changed the name of the FK
    constraint on instance_info_caches from instance_info_caches_ibfk_1
    to instance_info_caches_instance_uuid_fkey and on virtual_interfaces
    from virtual_interfaces_ibfk_1 to
    virtual_interfaces_instance_uuid_fkey meaning databases who have
    upgraded from before folsom have the old fkey.

    This patch adds a check for those with the old fkey name to make sure
    it is dropped before changing unique constraints (as is required by
    MySQL). It also fixes the fkey name to be as defined in 133 if it
    wasn't before (which will persist on downgrade being consistent with
    those who have come in after folsom).

Closes-Bug: #1245502

Change-Id: Ib0dcd04fcb9ed776c76d40561181abad2e14f76f

Changed in nova:
status:	In Progress → Fix Committed

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2013-11-13: Fix proposed to nova (stable/havana)

Fix proposed to branch: stable/havana
Review: https://review.openstack.org/56149

Revision history for this message

Roman Podoliaka (rpodolyaka) wrote on 2013-11-13:

Hey, I'm very sorry for such a late response, but it's better late than never...

Ok, so Svetlana did the deep investigation of this bug. Despite I was sure, we had a problem with initial folsom migration (133_folsom.py), it looks like the actual problem is more subtle. It turns out that during Essex release cycle we had a migration that created the FKs mentioned above WITHOUT specifying names for them. In this case it's up to RDBMS to choose a name for a constraint. It seems that at that time we had a different release of MySQL which had a different algorithms for choosing names for FKs. So Dan's tests for comparing migrations are actually OK and the compacted migration is OK.

So here is problem. Depending on the concrete version of MySQL users had when they upgraded from Essex to Folsom, they ended up with different names for the same FK constraints... This means that we don't have a consistent state of our DB schema when we run migrations scripts.

In order to fix this, we must fix every migration, that touches such FKs/etc. Joshua has already fixed one. Thank you for undertaking this task!

So, affected users, you can actually help by providing us with the dumps of your DB SCHEMAS (without data, of course) before upgades to Grizzly or Havana. In this case, we could compare your DB schemas with the ones we expect to see, and fix all the differences by patching existing migrations.

Russell Bryant (russellb) on 2013-12-03

Changed in nova:
milestone:	none → icehouse-1

Thierry Carrez (ttx) on 2013-12-04

Changed in nova:
status:	Fix Committed → Fix Released

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2014-01-01: Fix merged to nova (stable/havana)

#10

Reviewed: https://review.openstack.org/56149
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=bd724fbb78f37c19d2cd265f19194684cc265575
Submitter: Jenkins
Branch: stable/havana

commit bd724fbb78f37c19d2cd265f19194684cc265575
Author: Joshua Hesketh <email address hidden>
Date: Tue Oct 29 09:40:41 2013 +1100

Fix migration 185 to work with old fkey names

    Due to different versions of MySQL it is possible to end up with
    different FK's then what is expected. For example the FK constraint
    on instance_info_caches changed from instance_info_caches_ibfk_1
    to instance_info_caches_instance_uuid_fkey and on virtual_interfaces
    from virtual_interfaces_ibfk_1 to
    virtual_interfaces_instance_uuid_fkey meaning databases who have
    upgraded from before folsom may have the old fkey.

Closes-Bug: #1245502

Change-Id: Ib0dcd04fcb9ed776c76d40561181abad2e14f76f
(cherry picked from commit c620cafb700ca195db0bd0ef9d62a0c9459bdc38)

tags:

added: in-stable-havana

Revision history for this message

Meghal Gosalia (meghalgosalia) wrote on 2014-03-12:

#11

I see error for instance_type_projects while upgrading from grizzly->havana. Also, this is seen only in cases where db existed since folsom.

Here is the error -
Cannot drop index 'uniq_instance_type_id_x_project_id_x_deleted': needed in a
foreign key constraint") 'ALTER TABLE instance_type_projects DROP INDEX
uniq_instance_type_id_x_project_id_x_deleted' ()

Here is how the table looks:
| instance_type_projects | CREATE TABLE `instance_type_projects` (
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `instance_type_id` int(11) NOT NULL,
  `project_id` varchar(255) DEFAULT NULL,
  `deleted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_instance_type_id_x_project_id_x_deleted` (`instance_type_id`,`project_id`,`deleted`),
  CONSTRAINT `instance_type_projects_ibfk_1` FOREIGN KEY (`instance_type_id`) REFERENCES `instance_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

In order to get past this, I had to run following steps:
mysql> ALTER TABLE instance_type_projects DROP FOREIGN KEY instance_type_projects_ibfk_1;

and then run nova-manage db sync 185.

This is the new state after the migration:
| instance_type_projects | CREATE TABLE `instance_type_projects` (
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `instance_type_id` int(11) NOT NULL,
  `project_id` varchar(255) DEFAULT NULL,
  `deleted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_instance_type_projects0instance_type_id0project_id0deleted` (`instance_type_id`,`project_id`,`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Is this correct?

Revision history for this message

Meghal Gosalia (meghalgosalia) wrote on 2014-03-12:

#12

Should not the script create the foreign key?

Thierry Carrez (ttx) on 2014-04-17

Changed in nova:
milestone:	icehouse-1 → 2014.1

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.