yahoo-eng-team team mailing list archive
-
yahoo-eng-team team
-
Mailing list archive
-
Message #15929
[Bug 1239484] Re: failed nova db migration upgrading from grizzly to havana
** Also affects: nova/icehouse
Importance: Undecided
Status: New
--
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):
In Progress
Status in OpenStack Compute (nova) icehouse series:
New
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
