nova db sync failure on precise

[Scott Moser]

On recent precise, we're seeing a failure of devstack install in 'nova db sync'

2011-12-15 18:39:14,483 INFO migrate.versioning.api [-] 63 -> 64...
2011-12-15 18:39:14,504 DEBUG migrate.versioning.util [-] Disposing SQLAlchemy engine Engine(mysql://root:golfing@localhost/nova) from (pid=8177) with_engine /usr/lib/python2.7/dist-packages/migrate/versioning/util/__init__.py:162
2011-12-15 18:39:14,506 CRITICAL nova [-] (OperationalError) (1025, "Error on rename of './nova/#sql-19b6_2b' to './nova/instance_actions' (errno: 150)") '\nALTER TABLE instance_actions DROP COLUMN instance_id' ()
(nova): TRACE: Traceback (most recent call last):
(nova): TRACE: File "/opt/stack/nova/bin/nova-manage", line 2337, in <module>
(nova): TRACE: main()
(nova): TRACE: File "/opt/stack/nova/bin/nova-manage", line 2325, in main
(nova): TRACE: fn(*fn_args, **fn_kwargs)
(nova): TRACE: File "/opt/stack/nova/bin/nova-manage", line 1162, in sync
(nova): TRACE: return migration.db_sync(version)
(nova): TRACE: File "/opt/stack/nova/nova/db/migration.py", line 35, in db_sync
...
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 330, in do_execute
(nova): TRACE: cursor.execute(statement, parameters)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
(nova): TRACE: self.errorhandler(self, exc, value)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
(nova): TRACE: raise errorclass, errorvalue
(nova): TRACE: OperationalError: (OperationalError) (1025, "Error on rename of './nova/#sql-19b6_2b' to './nova/instance_actions' (errno: 150)") '\nALTER TABLE instance_actions DROP COLUMN instance_id' ()
(nova): TRACE:
Command failed, please check log for more info

This is seen most recently on nova tip of:
commit 8eeb132f80acdf3f05edd5594bce54dde5fb789c
Merge: 22040a5 6090313
Author: Jenkins <email address hidden>
Date: Thu Dec 15 18:05:56 2011 +0000

    Merge "Updates OVS rules applied to IPv4 VIFs"

[Adam Gandelman (gandelman-a)]

This is very similar to Bug #816236 , which was triggered by configuring MySQL to use InnoDB before the initial migrations. This hasn't shown up with Devstack on Oneiric because MySQL 5.2 uses MyISAM as the default. MySQL 5.5/Precise now uses InnoDB out of the box, so the same bug is turning up in migrations like 064_change_instance_id_to_uuid_in_instance_actions.py. The same fix from bug #816236 can be applied here but I'm not certain it is a fix or a workaround to a problem with earlier migrations (specifically 022_set_engine_mysql_innodb.py) or python-migrate/sqlalchemy.

[Adam Gandelman (gandelman-a)]

Gerrit proposal @ https://review.openstack.org/#change,2385

[Openstack Gerrit (openstack-gerrit)]

Reviewed: https://review.openstack.org/2389
Committed: http://github.com/openstack/nova/commit/8b52505df5d6f62e3a31a3457832288fd9e450d0
Submitter: Jenkins
Branch: master

 status fixcommitted
 done

commit 8b52505df5d6f62e3a31a3457832288fd9e450d0
Author: Thierry Carrez <email address hidden>
Date: Thu Dec 15 21:54:07 2011 +0100

    Ensure fkey is dropped before removing instance_id

    This fixes Bug #904888

    On MySQL databases that use InnoDB by default (ie, at the point when
    database is being first migrated), extra care needs to be taken to
    ensure FKs are dropped before columns because they are not automatically.

    Patch from Adam Gandelman and Scott Moser.

    Change-Id: I32919a46bb76f524e064098738ad98248b2f1c0e

[Openstack Gerrit (openstack-gerrit)]

Reviewed: https://review.openstack.org/2404
Committed: http://github.com/openstack/nova/commit/5a28a07b10c4f7fef2046c4e84929706c8351e19
Submitter: Jenkins
Branch: milestone-proposed

 tag in-milestone-proposed
 done

commit 5a28a07b10c4f7fef2046c4e84929706c8351e19
Author: Thierry Carrez <email address hidden>
Date: Thu Dec 15 21:54:07 2011 +0100

    Ensure fkey is dropped before removing instance_id

    This fixes Bug #904888

    On MySQL databases that use InnoDB by default (ie, at the point when
    database is being first migrated), extra care needs to be taken to
    ensure FKs are dropped before columns because they are not automatically.

    Patch from Adam Gandelman and Scott Moser.

    Change-Id: I32919a46bb76f524e064098738ad98248b2f1c0e