Initial 'nova db sync' migration fails on mysql (if InnoDB was used since the beginning)

[Adam Gandelman]

On Ubuntu natty with nova 2011.3~d3~20110726.1320-0ubuntu0ppa1~natty1, the initial 'nova db sync' fails:

(nova): TRACE: OperationalError: (OperationalError) (1025, "Error on rename of './nova/#sql-9a3_10f' to './nova/migrations' (errno: 150)") '\nALTER TABLE migrations DROP COLUMN instance_id' ()

...because migrations.instance_id is part of a foreign key reference that must first be deleted:

mysql> show create table migrations;
<..snip..>
  KEY `instance_id` (`instance_id`),
  CONSTRAINT `migrations_ibfk_1` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Modifying db/sqlalchemy/migrate_repo/versions/034_change_instance_id_in_migrations.py to delete this foreign key before dropping table allows migrations to complete successfully.

[Thierry Carrez (ttx)]

Can't reproduce...

[Thierry Carrez (ttx)]

This only happens if the database was originally created as InnoDB (and not migrated to InnoDB at 022_set_engine_mysql_innodb.py). To reproduce, add 'default_storage_engine = InnoDB' to the [mysqld] section of /etc/mysql/my.cnf before creating the nova database.