← Back to team overview

yahoo-eng-team team mailing list archive

[Bug 1239484] Re: failed nova db migration upgrading from grizzly to havana

 

** No longer affects: nova/icehouse

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to OpenStack Compute (nova).
https://bugs.launchpad.net/bugs/1239484

Title:
  failed nova db migration upgrading from grizzly to havana

Status in Ubuntu Cloud Archive:
  New
Status in OpenStack Compute (Nova):
  Won't Fix

Bug description:
  I recently upgraded a Nova cluster from grizzly to havana. We're using
  the Ubuntu Cloud Archive and so in terms of package versions the
  upgrade was from 1:2013.1.3-0ubuntu1~cloud0 to
  1:2013.2~rc2-0ubuntu1~cloud0.  We're using mysql-server-5.5
  5.5.32-0ubuntu0.12.04.1 from Ubuntu 12.04 LTS.

  After the upgrade, "nova-manage db sync" failed as follows:

  # nova-manage db sync
  2013-10-13 21:08:54.132 26592 INFO migrate.versioning.api [-] 161 -> 162...
  2013-10-13 21:08:54.138 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.140 26592 INFO migrate.versioning.api [-] 162 -> 163...
  2013-10-13 21:08:54.145 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.146 26592 INFO migrate.versioning.api [-] 163 -> 164...
  2013-10-13 21:08:54.154 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.154 26592 INFO migrate.versioning.api [-] 164 -> 165...
  2013-10-13 21:08:54.162 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.162 26592 INFO migrate.versioning.api [-] 165 -> 166...
  2013-10-13 21:08:54.167 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.170 26592 INFO migrate.versioning.api [-] 166 -> 167...
  2013-10-13 21:08:54.175 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.176 26592 INFO migrate.versioning.api [-] 167 -> 168...
  2013-10-13 21:08:54.184 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.184 26592 INFO migrate.versioning.api [-] 168 -> 169...
  2013-10-13 21:08:54.189 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.189 26592 INFO migrate.versioning.api [-] 169 -> 170...
  2013-10-13 21:08:54.199 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.199 26592 INFO migrate.versioning.api [-] 170 -> 171...
  2013-10-13 21:08:54.204 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.205 26592 INFO migrate.versioning.api [-] 171 -> 172...
  2013-10-13 21:08:54.841 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.842 26592 INFO migrate.versioning.api [-] 172 -> 173...
  2013-10-13 21:08:54.883 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 409 from table: key_pairs
  2013-10-13 21:08:54.888 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 257 from table: key_pairs
  2013-10-13 21:08:54.889 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 383 from table: key_pairs
  2013-10-13 21:08:54.897 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 22 from table: key_pairs
  2013-10-13 21:08:54.905 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 65 from table: key_pairs
  2013-10-13 21:08:54.911 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 106 from table: key_pairs
  2013-10-13 21:08:54.911 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 389 from table: key_pairs
  2013-10-13 21:08:54.923 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 205 from table: key_pairs
  2013-10-13 21:08:54.928 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 259 from table: key_pairs
  2013-10-13 21:08:54.934 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 127 from table: key_pairs
  2013-10-13 21:08:54.946 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 337 from table: key_pairs
  2013-10-13 21:08:54.951 26592 INFO nova.db.sqlalchemy.utils [-] Deleted duplicated row with id: 251 from table: key_pairs
  2013-10-13 21:08:54.991 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:54.991 26592 INFO migrate.versioning.api [-] 173 -> 174...
  2013-10-13 21:08:55.052 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:55.053 26592 INFO migrate.versioning.api [-] 174 -> 175...
  2013-10-13 21:08:55.146 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:55.147 26592 INFO migrate.versioning.api [-] 175 -> 176...
  2013-10-13 21:08:55.171 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:55.172 26592 INFO migrate.versioning.api [-] 176 -> 177...
  2013-10-13 21:08:55.236 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:55.237 26592 INFO migrate.versioning.api [-] 177 -> 178...
  2013-10-13 21:08:55.635 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:55.636 26592 INFO migrate.versioning.api [-] 178 -> 179...
  2013-10-13 21:08:55.692 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:55.692 26592 INFO migrate.versioning.api [-] 179 -> 180...
  2013-10-13 21:08:55.734 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:55.734 26592 INFO migrate.versioning.api [-] 180 -> 181...
  2013-10-13 21:08:55.786 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:55.786 26592 INFO migrate.versioning.api [-] 181 -> 182...
  2013-10-13 21:08:55.829 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:55.830 26592 INFO migrate.versioning.api [-] 182 -> 183...
  2013-10-13 21:08:55.872 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:55.874 26592 INFO migrate.versioning.api [-] 183 -> 184...
  2013-10-13 21:08:56.092 26592 INFO migrate.versioning.api [-] done
  2013-10-13 21:08:56.093 26592 INFO migrate.versioning.api [-] 184 -> 185...
  Command failed, please check log for more info
  2013-10-13 21:09:00.315 26592 CRITICAL nova [-] (OperationalError) (1553, "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' ()
  2013-10-13 21:09:00.315 26592 TRACE nova Traceback (most recent call last):
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/bin/nova-manage", line 10, in <module>
  2013-10-13 21:09:00.315 26592 TRACE nova     sys.exit(main())
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/nova/cmd/manage.py", line 1377, in main
  2013-10-13 21:09:00.315 26592 TRACE nova     ret = fn(*fn_args, **fn_kwargs)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/nova/cmd/manage.py", line 885, in sync
  2013-10-13 21:09:00.315 26592 TRACE nova     return migration.db_sync(version)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/nova/db/migration.py", line 33, in db_sync
  2013-10-13 21:09:00.315 26592 TRACE nova     return IMPL.db_sync(version=version)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migration.py", line 75, in db_sync
  2013-10-13 21:09:00.315 26592 TRACE nova     return versioning_api.upgrade(get_engine(), repository, version)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/migrate/versioning/api.py", line 186, in upgrade
  2013-10-13 21:09:00.315 26592 TRACE nova     return _migrate(url, repository, version, upgrade=True, err=err, **opts)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "<string>", line 2, in _migrate
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migration.py", line 40, in patched_with_engine
  2013-10-13 21:09:00.315 26592 TRACE nova     return f(*a, **kw)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/migrate/versioning/api.py", line 366, in _migrate
  2013-10-13 21:09:00.315 26592 TRACE nova     schema.runchange(ver, change, changeset.step)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/migrate/versioning/schema.py", line 91, in runchange
  2013-10-13 21:09:00.315 26592 TRACE nova     change.run(self.engine, step)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/migrate/versioning/script/py.py", line 145, in run
  2013-10-13 21:09:00.315 26592 TRACE nova     script_func(engine)
  2013-10-13 21:09:00.315 26592 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-13 21:09:00.315 26592 TRACE nova     return _uc_rename(migrate_engine, upgrade=True)
  2013-10-13 21:09:00.315 26592 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-13 21:09:00.315 26592 TRACE nova     old_name, *(columns))
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/utils.py", line 197, in drop_unique_constraint
  2013-10-13 21:09:00.315 26592 TRACE nova     uc.drop()
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/migrate/changeset/constraint.py", line 59, in drop
  2013-10-13 21:09:00.315 26592 TRACE nova     self.__do_imports('constraintdropper', *a, **kw)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/migrate/changeset/constraint.py", line 32, in __do_imports
  2013-10-13 21:09:00.315 26592 TRACE nova     run_single_visitor(engine, visitorcallable, self, *a, **kw)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/migrate/changeset/databases/visitor.py", line 75, in run_single_visitor
  2013-10-13 21:09:00.315 26592 TRACE nova     fn(element, **kwargs)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/migrate/changeset/ansisql.py", line 272, in visit_migrate_unique_constraint
  2013-10-13 21:09:00.315 26592 TRACE nova     self._visit_constraint(*p, **k)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/migrate/changeset/ansisql.py", line 284, in _visit_constraint
  2013-10-13 21:09:00.315 26592 TRACE nova     self.execute()
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/migrate/changeset/ansisql.py", line 42, in execute
  2013-10-13 21:09:00.315 26592 TRACE nova     return self.connection.execute(self.buffer.getvalue())
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute
  2013-10-13 21:09:00.315 26592 TRACE nova     params)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 805, in _execute_text
  2013-10-13 21:09:00.315 26592 TRACE nova     statement, parameters
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
  2013-10-13 21:09:00.315 26592 TRACE nova     context)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
  2013-10-13 21:09:00.315 26592 TRACE nova     exc_info
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 195, in raise_from_cause
  2013-10-13 21:09:00.315 26592 TRACE nova     reraise(type(exception), exception, tb=exc_tb)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
  2013-10-13 21:09:00.315 26592 TRACE nova     context)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324, in do_execute
  2013-10-13 21:09:00.315 26592 TRACE nova     cursor.execute(statement, parameters)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
  2013-10-13 21:09:00.315 26592 TRACE nova     self.errorhandler(self, exc, value)
  2013-10-13 21:09:00.315 26592 TRACE nova   File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
  2013-10-13 21:09:00.315 26592 TRACE nova     raise errorclass, errorvalue
  2013-10-13 21:09:00.315 26592 TRACE nova OperationalError: (OperationalError) (1553, "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' ()
  2013-10-13 21:09:00.315 26592 TRACE nova

  After some investigation I decided to fix it as follows:

  mysql> show create table instance_type_projects;
  +------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table                  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
  +------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | 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 |
  +------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)

  mysql> alter table instance_type_projects drop foreign key instance_type_projects_ibfk_1;
  Query OK, 0 rows affected (0.01 sec)
  Records: 0  Duplicates: 0  Warnings: 0

  mysql> alter table instance_type_projects drop index uniq_instance_type_id_x_project_id_x_deleted;
  Query OK, 0 rows affected (0.00 sec)
  Records: 0  Duplicates: 0  Warnings: 0

  mysql> alter table instance_type_projects add UNIQUE KEY `uniq_instance_type_projects0instance_type_id0project_id0deleted` (`instance_type_id`,`project_id`,`deleted`);
  Query OK, 0 rows affected (0.02 sec)
  Records: 0  Duplicates: 0  Warnings: 0

  mysql> alter table instance_type_projects add constraint `instance_type_projects_ibfk_1` foreign key (`instance_type_id`) references `instance_types` (`id`);
  Query OK, 0 rows affected (0.00 sec)
  Records: 0  Duplicates: 0  Warnings: 0

  mysql> show create table instance_type_projects;
  +------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table                  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
  +------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | 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`),
    CONSTRAINT `instance_type_projects_ibfk_1` FOREIGN KEY (`instance_type_id`) REFERENCES `instance_types` (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  +------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)

  I checked that the rest of 185 was applied, bumped the version in the
  DB to 185, and a second run of "nova-manage db sync" completed without
  incident.

To manage notifications about this bug go to:
https://bugs.launchpad.net/cloud-archive/+bug/1239484/+subscriptions