OpenStack Compute (nova)

failed nova db migration upgrading from grizzly to havana

Bug #1239484 reported by Paul Collins on 2013-10-14

This bug affects 4 people

Affects		Status	Importance	Assigned to	Milestone
	OpenStack Compute (nova)	Won't Fix	Wishlist	Tiantian Gao
	Ubuntu Cloud Archive	Won't Fix	Undecided	Unassigned

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.

See original description

Tags:

Paul Collins (pjdc) on 2013-10-14

tags:	added: canonistack
description:	updated

Thierry Carrez (ttx) on 2013-10-14

tags:

added: havana-rc-potential

Roman Podoliaka (rpodolyaka) on 2013-10-14

Changed in nova:
assignee:	nobody → Roman Podolyaka (rpodolyaka)
status:	New → Confirmed

Roman Podoliaka (rpodolyaka) on 2013-10-15

Changed in nova:
status:	Confirmed → Incomplete

Revision history for this message

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

Download full text (3.2 KiB)

Ok, so this is odd, but known behavior of MySQL.

Let's consider the following example:

mysql> create table testtbl (a integer primary key);
Query OK, 0 rows affected (0.09 sec)

mysql> create table testtbl2 (a integer primary key, b varchar(36), c integer, foreign key test_fk (c) references testtbl (a));
Query OK, 0 rows affected (0.09 sec)

mysql> alter table testtbl2 add constraint unique test_unique (c, b);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table testtbl2;
| testtbl2 | CREATE TABLE `testtbl2` (
  `a` int(11) NOT NULL,
  `b` varchar(36) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `test_idx` (`c`),
  CONSTRAINT `testtbl2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `testtbl` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

mysql> alter table testtbl2 drop index test_unique;
ERROR 1553 (HY000): Cannot drop index 'test_unique': needed in a foreign key constraint

so if we want to drop the UC, we have to drop the FK first, then drop the UC, and finally recreate the FK.

But:

mysql> create index test_idx on testtbl2 (c);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table testtbl2 drop index test_unique;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

Returning to our problem. The reporter shown his definition of 'instance_type_projects' table with migration 184 applied:

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 |

At the same time applying of all migrations from 133 (initial one) to 184 gives me the following definition:

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`),
  KEY `instance_type_id` (`instance_type_id`),
  CONSTRAINT `instance_type_projects_ibfk_1` FOREIGN KEY (`instance_type_id`) REFERENCES `instance_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

So, for some reason the reporter doesn't have index on column 'instance_type_id' ( KEY `instance_type_id` (`instance_type_id`)). This explains the fact, that our migrations tests pass. That index is created in the initial migration (133_folsom.py) and doesn't see m to be modified anywhere.

Could you please give us more information on your setup? Have you modified you DB schema manually (created...

Ok, so this is odd, but known behavior of MySQL.

Let's consider the following example:

mysql> create table testtbl (a integer primary key);
Query OK, 0 rows affected (0.09 sec)

mysql> create table testtbl2 (a integer primary key, b varchar(36), c integer, foreign key test_fk (c) references testtbl (a));
Query OK, 0 rows affected (0.09 sec)

mysql> alter table testtbl2 add constraint unique test_unique (c, b);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table testtbl2 drop index test_unique;
ERROR 1553 (HY000): Cannot drop index 'test_unique': needed in a foreign key constraint

so if we want to drop the UC, we have to drop the FK first, then drop the UC, and finally recreate the FK.

But:

mysql> create index test_idx on testtbl2 (c);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  alter table testtbl2 drop index test_unique;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Returning to our problem. The reporter shown his definition of 'instance_type_projects' table with migration 184 applied:

At the same time applying of all migrations from 133 (initial one) to 184 gives me the following definition:

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`),
  KEY `instance_type_id` (`instance_type_id`),
  CONSTRAINT `instance_type_projects_ibfk_1` FOREIGN KEY (`instance_type_id`) REFERENCES `instance_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Could you please give us more information on your setup? Have you modified you DB schema manually (created/deleted indexes/constraints)?

Thierry Carrez (ttx) on 2013-10-17

tags:

added: havana-backport-potential
removed: havana-rc-potential

Tiantian Gao (gtt116) on 2013-11-19

Changed in nova:
status:	Incomplete → Confirmed

Revision history for this message

Tiantian Gao (gtt116) wrote on 2013-11-19:

I can reproduct it.

When I upgrade from folsom to grizzly, then to havana, I meet the issue. But, in a clean environment, install a grizzly, then upgrade to havana, every thing is fine.

Revision history for this message

Tiantian Gao (gtt116) wrote on 2013-11-19:

Before running nova-manage db sync, execute below sql command will quick fix it:

create index `instance_type_id` on instance_type_projects (`instance_type_id`);

Revision history for this message

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

@Tiantian

Is there any chance you can provide us with a dump of the DB schema just before you run folsom -> grizzly update? (only DB schema, not data rows, of course)

Revision history for this message

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

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

Revision history for this message

Tiantian Gao (gtt116) wrote on 2013-11-19:

nova.folsom.schema Edit (46.7 KiB, text/plain)

@Roman, Sure, the schema is in attachment.

But what confuse me is when I setup database with sqldump file, upgrading is successful.
While using folsom version code, running nova-manage db sync to setup database, this bug will happen.

Tom Fifield (fifieldt) on 2013-12-06

tags:	added: db
Changed in nova:
importance:	Undecided → High

Tiantian Gao (gtt116) on 2014-01-12

Changed in nova:
assignee:	Roman Podoliaka (rpodolyaka) → Tiantian Gao (gtt116)

Revision history for this message

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

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

Matt Riedemann (mriedem) on 2014-03-22

Changed in nova:
status:	Confirmed → In Progress

Revision history for this message

Qiu Yu (unicell) wrote on 2014-04-01:

I just hit the exact same issue here, upgrading from folsom to havana. The ‘instance_type_id’ index somehow lost in 173->174 migration.

And the same with Tiantian, it happens with clean sync of folsom db model, then upgrade. However, with mysql dump and restore of Folsom database schema, then the issue is gone.

Revision history for this message

Tracy Jones (tjones-i) wrote on 2014-07-03:

Is this still in progress? There are no updates in 3 months.

Revision history for this message

Sean Dague (sdague) wrote on 2014-09-03:

#10

Honestly, upgrade up from Folsom is pretty out of scope now, as the folsom and grizzly branches have been eoled, and havana is eol in a couple of weeks.