migration 86 fails when database table dns_domains is already InnoDB

[Russell Sim]

Hi, from my investigation it seems that altering a table from innodb causes problems if the table has registered foreign keys. There is further information about the problem on this random blog [1].

I came across the bug while working with a fresh devstack (master branch) install on a precise vm.

1. http://www.xaprb.com/blog/2006/08/22/mysqls-error-1025-explained/

Command failed, please check log for more info
2012-04-30 14:34:40 CRITICAL nova [-] (OperationalError) (1025, "Error on rename of './nova/#sql-67f4_40' to './nova/dns_domains' (errno: 150)") 'ALTER TABLE dns_domains Engine=InnoDB' ()
2012-04-30 14:34:40 TRACE nova Traceback (most recent call last):
2012-04-30 14:34:40 TRACE nova File "/usr/local/bin/nova-manage", line 7, in <module>
2012-04-30 14:34:40 TRACE nova execfile(__file__)
2012-04-30 14:34:40 TRACE nova File "/opt/stack/nova/bin/nova-manage", line 1754, in <module>
2012-04-30 14:34:40 TRACE nova main()
2012-04-30 14:34:40 TRACE nova File "/opt/stack/nova/bin/nova-manage", line 1741, in main
2012-04-30 14:34:40 TRACE nova fn(*fn_args, **fn_kwargs)
2012-04-30 14:34:40 TRACE nova File "/opt/stack/nova/bin/nova-manage", line 1106, in sync
2012-04-30 14:34:40 TRACE nova return migration.db_sync(version)
2012-04-30 14:34:40 TRACE nova File "/opt/stack/nova/nova/db/migration.py", line 30, in db_sync
2012-04-30 14:34:40 TRACE nova return IMPL.db_sync(version=version)
2012-04-30 14:34:40 TRACE nova File "/opt/stack/nova/nova/db/sqlalchemy/migration.py", line 83, in db_sync
2012-04-30 14:34:40 TRACE nova return versioning_api.upgrade(get_engine(), repository, version)
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/migrate/versioning/api.py", line 186, in upgrade
2012-04-30 14:34:40 TRACE nova return _migrate(url, repository, version, upgrade=True, err=err, **opts)
2012-04-30 14:34:40 TRACE nova File "<string>", line 2, in _migrate
2012-04-30 14:34:40 TRACE nova File "/opt/stack/nova/nova/db/sqlalchemy/migration.py", line 39, in patched_with_engine
2012-04-30 14:34:40 TRACE nova return f(*a, **kw)
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/migrate/versioning/api.py", line 366, in _migrate
2012-04-30 14:34:40 TRACE nova schema.runchange(ver, change, changeset.step)
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/migrate/versioning/schema.py", line 91, in runchange
2012-04-30 14:34:40 TRACE nova change.run(self.engine, step)
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/migrate/versioning/script/py.py", line 145, in run
2012-04-30 14:34:40 TRACE nova script_func(engine)
2012-04-30 14:34:40 TRACE nova File "/opt/stack/nova/nova/db/sqlalchemy/migrate_repo/versions/086_set_engine_mysql_innodb.py", line 33, in upgrade
2012-04-30 14:34:40 TRACE nova migrate_engine.execute("ALTER TABLE dns_domains Engine=InnoDB")
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2297, in execute
2012-04-30 14:34:40 TRACE nova return connection.execute(statement, *multiparams, **params)
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1405, in execute
2012-04-30 14:34:40 TRACE nova params)
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1582, in _execute_text
2012-04-30 14:34:40 TRACE nova statement, parameters
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1646, in _execute_context
2012-04-30 14:34:40 TRACE nova context)
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1639, in _execute_context
2012-04-30 14:34:40 TRACE nova context)
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 330, in do_execute
2012-04-30 14:34:40 TRACE nova cursor.execute(statement, parameters)
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
2012-04-30 14:34:40 TRACE nova self.errorhandler(self, exc, value)
2012-04-30 14:34:40 TRACE nova File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2012-04-30 14:34:40 TRACE nova raise errorclass, errorvalue
2012-04-30 14:34:40 TRACE nova OperationalError: (OperationalError) (1025, "Error on rename of './nova/#sql-67f4_40' to './nova/dns_domains' (errno: 150)") 'ALTER TABLE dns_domains Engine=InnoDB' ()
2012-04-30 14:34:40 TRACE nova

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/6913
Committed: http://github.com/openstack/nova/commit/5f2eab36345b2e2ac5ca72843c7dc69d35c106da
Submitter: Jenkins
Branch: master

commit 5f2eab36345b2e2ac5ca72843c7dc69d35c106da
Author: Russell Sim <email address hidden>
Date: Mon Apr 30 14:48:15 2012 +1000

    Fix InnoDB migration bug in migrate script 86.

    Fixes bug 991672

    Only change tables that aren't already InnoDB. Attempting to migrate
    an InnoDB table to InnoDB will causes an obscure "Error on rename"
    error if the table has foreign key relationships.

    Change-Id: I7bd5c964da851a8dd9cc0a6af3f93c07786b6781

[Khairul Aizat Kamarudzzaman (fenris)]

is it will release in folsom or already been release in essex seem like im still getting the error as per today.

[Khairul Aizat Kamarudzzaman (fenris)]

$ sudo nova-manage db sync
2012-07-25 00:43:30 DEBUG nova.utils [-] backend <module 'nova.db.sqlalchemy.migration' from '/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migration.pyc'> from (pid=13097) __get_backend /usr/lib/python2.7/dist-packages/nova/utils.py:658
2012-07-25 00:43:30 WARNING nova.utils [-] /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py:639: SADeprecationWarning: The 'listeners' argument to Pool (and create_engine()) is deprecated. Use event.listen().
  Pool.__init__(self, creator, **kw)

2012-07-25 00:43:30 WARNING nova.utils [-] /usr/lib/python2.7/dist-packages/sqlalchemy/pool.py:145: SADeprecationWarning: Pool.add_listener is deprecated. Use event.listen()
  self.add_listener(l)

[Fawad Khaliq (fawadkhaliq)]

I am also getting the same error as Khairul Aizat.

[shah kalpan (shahkalpan09)]

I have also same problem and i could not find any solution..