Migrations fail going from juno -> kilo

[Sam Morrison]

Trying to upgrade from Juno -> Kilo

keystone-manage db_version
55

keystone-manage db_sync
2015-06-26 16:52:47.494 6169 CRITICAL keystone [-] ProgrammingError: (ProgrammingError) (1146, "Table 'keystone_k.identity_provider' doesn't exist") 'ALTER TABLE identity_provider Engine=InnoDB' ()
2015-06-26 16:52:47.494 6169 TRACE keystone Traceback (most recent call last):
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/bin/keystone-manage", line 10, in <module>
2015-06-26 16:52:47.494 6169 TRACE keystone execfile(__file__)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/keystone/bin/keystone-manage", line 44, in <module>
2015-06-26 16:52:47.494 6169 TRACE keystone cli.main(argv=sys.argv, config_files=config_files)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/keystone/keystone/cli.py", line 585, in main
2015-06-26 16:52:47.494 6169 TRACE keystone CONF.command.cmd_class.main()
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/keystone/keystone/cli.py", line 76, in main
2015-06-26 16:52:47.494 6169 TRACE keystone migration_helpers.sync_database_to_version(extension, version)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/keystone/keystone/common/sql/migration_helpers.py", line 247, in sync_database_to_version
2015-06-26 16:52:47.494 6169 TRACE keystone _sync_extension_repo(default_extension, version)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/keystone/keystone/common/sql/migration_helpers.py", line 232, in _sync_extension_repo
2015-06-26 16:52:47.494 6169 TRACE keystone _fix_federation_tables(engine)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/keystone/keystone/common/sql/migration_helpers.py", line 167, in _fix_federation_tables
2015-06-26 16:52:47.494 6169 TRACE keystone engine.execute("ALTER TABLE identity_provider Engine=InnoDB")
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1863, in execute
2015-06-26 16:52:47.494 6169 TRACE keystone return connection.execute(statement, *multiparams, **params)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 833, in execute
2015-06-26 16:52:47.494 6169 TRACE keystone return self._execute_text(object, multiparams, params)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 982, in _execute_text
2015-06-26 16:52:47.494 6169 TRACE keystone statement, parameters
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1070, in _execute_context
2015-06-26 16:52:47.494 6169 TRACE keystone context)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/oslo_db/sqlalchemy/compat/handle_error.py", line 261, in _handle_dbapi_exception
2015-06-26 16:52:47.494 6169 TRACE keystone e, statement, parameters, cursor, context)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1267, in _handle_dbapi_exception
2015-06-26 16:52:47.494 6169 TRACE keystone util.raise_from_cause(newraise, exc_info)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
2015-06-26 16:52:47.494 6169 TRACE keystone reraise(type(exception), exception, tb=exc_tb)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1063, in _execute_context
2015-06-26 16:52:47.494 6169 TRACE keystone context)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 442, in do_execute
2015-06-26 16:52:47.494 6169 TRACE keystone cursor.execute(statement, parameters)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
2015-06-26 16:52:47.494 6169 TRACE keystone self.errorhandler(self, exc, value)
2015-06-26 16:52:47.494 6169 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2015-06-26 16:52:47.494 6169 TRACE keystone raise errorclass, errorvalue
2015-06-26 16:52:47.494 6169 TRACE keystone ProgrammingError: (ProgrammingError) (1146, "Table 'keystone_k.identity_provider' doesn't exist") 'ALTER TABLE identity_provider Engine=InnoDB' ()
2015-06-26 16:52:47.494 6169 TRACE keystone

If I run the command again I get:

keystone-manage db_version
67

keystone-manage db_sync
2015-06-26 16:53:25.489 6186 CRITICAL keystone [-] ValueError: Tables "endpoint_group,project_endpoint,project_endpoint_group" have non utf8 collation, please make sure all tables are CHARSET=utf8
2015-06-26 16:53:25.489 6186 TRACE keystone Traceback (most recent call last):
2015-06-26 16:53:25.489 6186 TRACE keystone File "/opt/kilo/bin/keystone-manage", line 10, in <module>
2015-06-26 16:53:25.489 6186 TRACE keystone execfile(__file__)
2015-06-26 16:53:25.489 6186 TRACE keystone File "/opt/keystone/bin/keystone-manage", line 44, in <module>
2015-06-26 16:53:25.489 6186 TRACE keystone cli.main(argv=sys.argv, config_files=config_files)
2015-06-26 16:53:25.489 6186 TRACE keystone File "/opt/keystone/keystone/cli.py", line 585, in main
2015-06-26 16:53:25.489 6186 TRACE keystone CONF.command.cmd_class.main()
2015-06-26 16:53:25.489 6186 TRACE keystone File "/opt/keystone/keystone/cli.py", line 76, in main
2015-06-26 16:53:25.489 6186 TRACE keystone migration_helpers.sync_database_to_version(extension, version)
2015-06-26 16:53:25.489 6186 TRACE keystone File "/opt/keystone/keystone/common/sql/migration_helpers.py", line 242, in sync_database_to_version
2015-06-26 16:53:25.489 6186 TRACE keystone _sync_common_repo(version)
2015-06-26 16:53:25.489 6186 TRACE keystone File "/opt/keystone/keystone/common/sql/migration_helpers.py", line 148, in _sync_common_repo
2015-06-26 16:53:25.489 6186 TRACE keystone init_version=init_version)
2015-06-26 16:53:25.489 6186 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/oslo_db/sqlalchemy/migration.py", line 77, in db_sync
2015-06-26 16:53:25.489 6186 TRACE keystone _db_schema_sanity_check(engine)
2015-06-26 16:53:25.489 6186 TRACE keystone File "/opt/kilo/local/lib/python2.7/site-packages/oslo_db/sqlalchemy/migration.py", line 110, in _db_schema_sanity_check
2015-06-26 16:53:25.489 6186 TRACE keystone ) % ','.join(table_names))
2015-06-26 16:53:25.489 6186 TRACE keystone ValueError: Tables "endpoint_group,project_endpoint,project_endpoint_group" have non utf8 collation, please make sure all tables are CHARSET=utf8
2015-06-26 16:53:25.489 6186 TRACE keystone

[Sam Morrison (sorrison)]

Should note I'm using stable/kilo branch

Also using mysql with ?charset=utf8

[Davanum Srinivas (DIMS) (dims-v)]

We should not throw a hard error i think (in _db_schema_sanity_check)

[Morgan Fainberg (mdrnstm)]

This is an issue with the table previous to the migration you're at. The solution is to alter the table directly to be utf8 (manually) as it is the only way out of this situation until we either remove the utf8 check from oslo.db or add a custom handler to correct the DB for you in line.

The source of this is that your db is not defaulted to using utf8 charset everywhere and a previous migration created the table with latin1 or other similarly incorrect (according to this check) charset.

I'm working with the oslo team to remove this check, but the quickest resolution for you is just the alter.

[Davanum Srinivas (DIMS) (dims-v)]

Here's some context from IRC:

morganfainberg dimsum__: we need to remove the utf8 check in oslo.db
morganfainberg dimsum__: it's surfaced *again* as a bug in keystone. it really does nothing but cause us to wedge our operators
dimsum__ morganfainberg: review? bug?
morganfainberg dimsum__: we need to instead write logic that fixes the utf8 scenario.
morganfainberg dimsum__: https://bugs.launchpad.net/keystone/+bug/1469029
morganfainberg this is like the 4th time i've had it wedge a operator
morganfainberg and we've been 1-off dropping in out-of-band custom fixes, but it's really starting to show this check just doesn't do anything useful
morganfainberg it doesn't catch anything early since we do utf8 default
morganfainberg in gate
morganfainberg and it just wedges our deployers
morganfainberg https://github.com/openstack/oslo.db/blob/master/oslo_db/sqlalchemy/migration.py#L97-L100
morganfainberg is the check
morganfainberg and basically once you're wedged you can never recover with a db_sync w/o a custom exception handler because the check is run before migrations
morganfainberg we should just drop the check imo (and backport to /stables)

[Sam Morrison (sorrison)]

I should make it clear that before I run any db_sync all my tables are utf8 and my connection string in keystone has ?charset=utf8

The tables it's complaining about not being utf8 are the ones that were created in the migration.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/kilo
Review: https://review.openstack.org/196335

[Roman Podoliaka (rpodolyaka)]

The last Sam's comment means there is a problem with Keystone migration scripts, or I should really say, they simply do not override the horrible MySQL default for collation and new tables created are not in utf8.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/196329
Committed: https://git.openstack.org/cgit/openstack/keystone/commit/?id=daecabcda752004b760c5be5a4a3a4d5471554db
Submitter: Jenkins
Branch: master

commit daecabcda752004b760c5be5a4a3a4d5471554db
Author: Morgan Fainberg <email address hidden>
Date: Fri Jun 26 19:19:44 2015 -0700

    Disable migration sanity check

    Disable the migration sanity check, it has served to wedge deployers
    more often than anything and should (instead) issue a warnin that
    the deployer should fix their table. Until the real fix is in place
    just disable it for any keystone migration.

    Change-Id: I210dcc77d188ae3b505dccdac1534184b8443aa9
    closes-bug: #1469029

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/196335
Committed: https://git.openstack.org/cgit/openstack/keystone/commit/?id=2376ed516119a5dba6cf1e4990d8744423bace1d
Submitter: Jenkins
Branch: stable/kilo

commit 2376ed516119a5dba6cf1e4990d8744423bace1d
Author: Morgan Fainberg <email address hidden>
Date: Fri Jun 26 19:19:44 2015 -0700

    Disable migration sanity check

    Disable the migration sanity check, it has served to wedge deployers
    more often than anything and should (instead) issue a warning that
    the deployer should fix their table. Until the real fix is in place
    just disable it for any keystone migration.

    Change-Id: I210dcc77d188ae3b505dccdac1534184b8443aa9
    closes-bug: #1469029
    (cherry picked from commit daecabcda752004b760c5be5a4a3a4d5471554db)