Database migrations failing on

Bug #931602 reported by Adam Gandelman on 2012-02-13
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Compute (nova)
Adam Gandelman

Bug Description

On Ubuntu Precise, mysql 5.5.17-4ubuntu6 x86_64, 'nova-manage db sync' results in error:

2012-02-13 13:26:02,905 CRITICAL nova [-] (OperationalError) (1025, "Error on rename of './nova/#sql-1cbe_79' to './nova/projects' (errno: 150)") 'ALTER TABLE projects CONVERT TO CHARACTER SET utf8' ()
(nova): TRACE: Traceback (most recent call last):
(nova): TRACE: File "/usr/bin/nova-manage", line 2372, in <module>
(nova): TRACE: main()
(nova): TRACE: File "/usr/bin/nova-manage", line 2359, in main
(nova): TRACE: fn(*fn_args, **fn_kwargs)
(nova): TRACE: File "/usr/bin/nova-manage", line 1193, in sync
(nova): TRACE: return migration.db_sync(version)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/nova/db/", line 30, in db_sync
(nova): TRACE: return IMPL.db_sync(version=version)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/", 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/", 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/", line 159, in with_engine
(nova): TRACE: return f(*a, **kw)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/versioning/", line 366, in _migrate
(nova): TRACE: schema.runchange(ver, change, changeset.step)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/versioning/", line 91, in runchange
(nova): TRACE:, step)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/migrate/versioning/script/", line 145, in run
(nova): TRACE: script_func(engine)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/nova/db/sqlalchemy/migrate_repo/versions/", line 53, in upgrade
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/", line 2297, in execute
(nova): TRACE: return connection.execute(statement, *multiparams, **params)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/", line 1405, in execute
(nova): TRACE: params)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/", line 1582, in _execute_text
(nova): TRACE: statement, parameters
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/", line 1646, in _execute_context
(nova): TRACE: context)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/", line 1639, in _execute_context
(nova): TRACE: context)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/", line 330, in do_execute
(nova): TRACE: cursor.execute(statement, parameters)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/MySQLdb/", line 174, in execute
(nova): TRACE: self.errorhandler(self, exc, value)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/MySQLdb/", line 36, in defaulterrorhandler
(nova): TRACE: raise errorclass, errorvalue
(nova): TRACE: OperationalError: (OperationalError) (1025, "Error on rename of './nova/#sql-1cbe_79' to './nova/projects' (errno: 150)") 'ALTER TABLE projects CONVERT TO CHARACTER SET utf8' ()
(nova): TRACE:

There have been similar bugs in the past with migrations against mysql servers configured with 'default_storage_engine = InnoDB' before initial nova migrations. Changing this to MyISAM and recreating the database is a workaround, but migrations should handle both configurations.

For reference, see: Bug #816236 Bug #916992 Bug #917205

Dan Prince (dan-prince) wrote :

Adam. I think Hu already filed a bug on this... I fixed it in :,3891

We should however pull that fix out into a smaller patch and get this fixed today.

Dan Prince (dan-prince) wrote :

NM. I misread. This may be a separate issue.

Adam Gandelman (gandelman-a) wrote :

Hey Dan,

Finally got a chance to look at this. Yeah, that doesn't address whats really going on. It seems the schema ends up being created much differently wrt foreign key constraints depending on how the MySQL server is configured prior to running any database. Migrating the database to v76 (just prior to this failing migration) against two MySQL configurations, one MyISAM and the other InnoDB, leads to two *very different* schemas in terms of foreign keys. I've captured the differences using some visualization software:

The following patch fixes the issue for me by addressing all other FK parents early on with the instances table: This seems a bit clunky but works. I'd like to look into the issue a bit more and see how this might also affect other database backends.

Fix proposed to branch: master

Changed in nova:
assignee: nobody → Adam Gandelman (gandelman-a)
status: New → In Progress

Fix proposed to branch: master

Adam Gandelman (gandelman-a) wrote :

MyISAM does not support FK constraints. If mysql is set to use MyISAM as default, any FK created prior to ends up being missing later on. Converting tables to InnoDB in 022 does not create the FKs, and sqlalchemy does nothing to ensure they are created. We should add migration somewhere that ensures missing FKs exist and ensure schemas are consistent across storage engines so that errors like this turn up in gating, as this seems to happen often. I'll file a bug and submit something for that.

Hengqing Hu (hudayou) wrote :

Hi Dan, better to do it in one place in Adam's newest change for this 77 problem.

I abandoned mine and will left a comment in my tr to refer to here.

Submitter: Jenkins
Branch: master

commit 516582b62d785d25044b0f852eb7e42a2b22558e
Author: Adam Gandelman <email address hidden>
Date: Tue Feb 14 10:40:29 2012 -0800

    077_convert_to_utf8: Convert *all* FK tables early

    The original migration only takes care of migrating the instances
    table before all others. It turns out, when using MySQL with InnoDB
    by default, there are many other tables with FKs that need to be converted
    early on as well (see bug description for schema diagram).

    Fixes bug 931602

    Update: Convert all tables in a predefined order from within the same

    Update: Avoid hard-coding database, fixes bug 930863

    Update: Fix pep8 spacing

    Change-Id: Ideb619eecef2ad067bda5c26612f761674dee924

Changed in nova:
status: In Progress → Fix Committed
Thierry Carrez (ttx) on 2012-02-29
Changed in nova:
milestone: none → essex-4
status: Fix Committed → Fix Released
Thierry Carrez (ttx) on 2012-04-05
Changed in nova:
milestone: essex-4 → 2012.1
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers