OpenStack Compute (nova)

Database migrations failing on 077_convert_to_utf8.py

Bug #931602 reported by Adam Gandelman
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Compute (nova)
Fix Released
Undecided
Adam Gandelman
OpenStack Compute (nova) 2012.1 "essex"

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/migration.py", 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/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/077_convert_to_utf8.py", line 53, in upgrade
(nova): TRACE: "ALTER TABLE %s CONVERT TO CHARACTER SET utf8" % table)
(nova): TRACE: File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2297, in execute
(nova): TRACE: return connection.execute(statement, *multiparams, **params)
(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-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

Revision history for this message
Dan Prince (dan-prince) wrote :

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

https://review.openstack.org/#change,3891

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

Revision history for this message
Dan Prince (dan-prince) wrote :

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

Revision history for this message
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:

http://people.canonical.com/~agandelman/schema/innodb/relationships.html
http://people.canonical.com/~agandelman/schema/myisam/relationships.html

The following patch fixes the issue for me by addressing all other FK parents early on with the instances table: http://paste.ubuntu.com/841173/ 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.

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to nova (master)

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

Changed in nova:
assignee: nobody → Adam Gandelman (gandelman-a)
status: New → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote :

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

Revision history for this message
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 022_set_engine_mysql_innodb.py 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.

Revision history for this message
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.

Revision history for this message
Hengqing Hu (hudayou) wrote :

https://review.openstack.org/#change,4149

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to nova (master)

Reviewed: https://review.openstack.org/4149
Committed: http://github.com/openstack/nova/commit/516582b62d785d25044b0f852eb7e42a2b22558e
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
    execution.

    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)
Changed in nova:
milestone: none → essex-4
status: Fix Committed → Fix Released
Thierry Carrez (ttx)
Changed in nova:
milestone: essex-4 → 2012.1
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.