Database migration v064 fails against MySQL 5.5 + InnoDB

[Adam Gandelman]

After Bug #907878 was resolved, migrations are failing against MySQL + InnoDB databases on precise:

2012-01-16 00:33:12,574 INFO migrate.versioning.api [-] 63 -> 64...
2012-01-16 00:33:12,643 DEBUG migrate.versioning.util [-] Disposing SQLAlchemy engine Engine(mysql://nova:<email address hidden>/nova) from (pid=4828) with_engine /usr/lib/python2.7/dist-packages/migrate/versioning/util/__init__.py:162
Command failed, please check log for more info
2012-01-16 00:33:12,645 CRITICAL nova [-] (OperationalError) (1025, "Error on rename of './nova/#sql-1115_36' to './nova/instance_actions' (errno: 150)") '\nALTER TABLE instance_actions DROP COLUMN instance_id' ()
(nova): TRACE: Traceback (most recent call last):
(nova): TRACE: File "/usr/bin/nova-manage", line 2382, in <module>
(nova): TRACE: main()
(nova): TRACE: File "/usr/bin/nova-manage", line 2370, in main
(nova): TRACE: fn(*fn_args, **fn_kwargs)
(nova): TRACE: File "/usr/bin/nova-manage", line 1207, in sync
(nova): TRACE: return migration.db_sync(version)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/nova/db/migration.py", line 35, in db_sync
(nova): TRACE: return IMPL.db_sync(version=version)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migration.py", line 51, in db_sync
(nova): TRACE: return versioning_api.upgrade(FLAGS.sql_connection, repo_path, version)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/versioning/api.py", line 186, in upgrade
(nova): TRACE: return _migrate(url, repository, version, upgrade=True, err=err, **opts)
(nova): TRACE: File "<string>", line 2, in _migrate
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/versioning/util/__init__.py", line 159, in with_engine
(nova): TRACE: return f(*a, **kw)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/versioning/api.py", line 366, in _migrate
(nova): TRACE: schema.runchange(ver, change, changeset.step)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/versioning/schema.py", line 91, in runchange
(nova): TRACE: change.run(self.engine, step)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/versioning/script/py.py", line 145, in run
(nova): TRACE: script_func(engine)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migrate_repo/versions/064_change_instance_id_to_uuid_in_instance_actions.py", line 52, in upgrade
(nova): TRACE: instance_actions.c.instance_id.drop()
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/changeset/schema.py", line 549, in drop
(nova): TRACE: engine._run_visitor(visitorcallable, self, connection, **kwargs)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2234, in _run_visitor
(nova): TRACE: conn._run_visitor(visitorcallable, element, **kwargs)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1904, in _run_visitor
(nova): TRACE: **kwargs).traverse_single(element)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/changeset/ansisql.py", line 53, in traverse_single
(nova): TRACE: ret = super(AlterTableVisitor, self).traverse_single(elem)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 86, in traverse_single
(nova): TRACE: return meth(obj, **kw)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/changeset/ansisql.py", line 138, in visit_column
(nova): TRACE: self.execute()
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/changeset/ansisql.py", line 42, in execute
(nova): TRACE: return self.connection.execute(self.buffer.getvalue())
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1405, in execute
(nova): TRACE: params)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1582, in _execute_text
(nova): TRACE: statement, parameters
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1646, in _execute_context
(nova): TRACE: context)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1639, in _execute_context
(nova): TRACE: context)
(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-1115_36' to './nova/instance_actions' (errno: 150)") '\nALTER TABLE instance_actions DROP COLUMN instance_id' ()
(nova): TRACE:

MySQL 5.5 on Precise has switched from using MyISAM as the default storage engine to InnoDB. When InnoDB is configured as the default, migrations need to make sure FK constraints are removed. This seems to only be an issue on systems that use InnoDB by default first. See Bug #816236 and Bug #904888 .

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/3110
Committed: http://github.com/openstack/nova/commit/35df99cf1dca5d9b0ca1cb09039845aa8bd3a49b
Submitter: Jenkins
Branch: master

commit 35df99cf1dca5d9b0ca1cb09039845aa8bd3a49b
Author: Chuck Short <email address hidden>
Date: Tue Jan 17 14:24:09 2012 -0500

    Drop FK constraint if it exists in migration 064

    Fixes LP bug 916992

    A workaround was originally applied to fix a FK constraint problem in migration
    064. The original bug only affected MySQL+InnoDB databases and was masked by
    using MySQL+MyISAM. Commit d503d6b1079f9eafe0430754214fc5b6d4e32c09 attempted
    to improve the workaround, though it caused issues on databases not using
    InnoDB, so it was reverted enitrely and we are back with the original bug.

    This change will attempt to drop the FK constraint only if it exists. This
    should now support MySQL installations that are configured both
    default_storage_engine=MyISAM (oneiric) and default_storage_engine=InnoDB
    (precise)

    Change-Id: Ie2cfa32d8f52b163f513679649da52a73fc501df
    Signed-off-by: Adam Gandleman <email address hidden>
    Signed-off-by: Chuck Short <email address hidden>

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.