Failed migration 90 -> 91 Can't DROP 'ixu_user_name_domain_id'

[Sam Morrison]

Get the following error running DB migration when upgrading from kilo -> mitaka

2016-04-20 09:31:37.560 10471 INFO migrate.versioning.api [-] 90 -> 91...
2016-04-20 09:31:37.822 10471 CRITICAL keystone [-] OperationalError: (_mysql_exceptions.OperationalError) (1091, "Can't DROP 'ixu_user_name_domain_id'; check that column/key exists") [SQL: u'ALTER TABLE user DROP INDEX ixu_user_name_domain_id']
2016-04-20 09:31:37.822 10471 ERROR keystone Traceback (most recent call last):
2016-04-20 09:31:37.822 10471 ERROR keystone File "/usr/bin/keystone-manage", line 10, in <module>
2016-04-20 09:31:37.822 10471 ERROR keystone sys.exit(main())
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/keystone/keystone/cmd/manage.py", line 47, in main
2016-04-20 09:31:37.822 10471 ERROR keystone cli.main(argv=sys.argv, config_files=config_files)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/keystone/keystone/cmd/cli.py", line 992, in main
2016-04-20 09:31:37.822 10471 ERROR keystone CONF.command.cmd_class.main()
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/keystone/keystone/cmd/cli.py", line 371, in main
2016-04-20 09:31:37.822 10471 ERROR keystone migration_helpers.sync_database_to_version(extension, version)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/keystone/keystone/common/sql/migration_helpers.py", line 210, in sync_database_to_version
2016-04-20 09:31:37.822 10471 ERROR keystone _sync_common_repo(version)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/keystone/keystone/common/sql/migration_helpers.py", line 136, in _sync_common_repo
2016-04-20 09:31:37.822 10471 ERROR keystone init_version=init_version, sanity_check=False)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/oslo_db/sqlalchemy/migration.py", line 79, in db_sync
2016-04-20 09:31:37.822 10471 ERROR keystone migration = versioning_api.upgrade(engine, repository, version)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/versioning/api.py", line 186, in upgrade
2016-04-20 09:31:37.822 10471 ERROR keystone return _migrate(url, repository, version, upgrade=True, err=err, **opts)
2016-04-20 09:31:37.822 10471 ERROR keystone File "<decorator-gen-15>", line 2, in _migrate
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 160, in with_engine
2016-04-20 09:31:37.822 10471 ERROR keystone return f(*a, **kw)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/versioning/api.py", line 366, in _migrate
2016-04-20 09:31:37.822 10471 ERROR keystone schema.runchange(ver, change, changeset.step)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/versioning/schema.py", line 93, in runchange
2016-04-20 09:31:37.822 10471 ERROR keystone change.run(self.engine, step)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/versioning/script/py.py", line 148, in run
2016-04-20 09:31:37.822 10471 ERROR keystone script_func(engine)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/keystone/keystone/common/sql/migrate_repo/versions/091_migrate_data_to_local_user_and_password_tables.py", line 61, in upgrade
2016-04-20 09:31:37.822 10471 ERROR keystone name='ixu_user_name_domain_id').drop()
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/changeset/constraint.py", line 59, in drop
2016-04-20 09:31:37.822 10471 ERROR keystone self.__do_imports('constraintdropper', *a, **kw)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/changeset/constraint.py", line 32, in __do_imports
2016-04-20 09:31:37.822 10471 ERROR keystone run_single_visitor(engine, visitorcallable, self, *a, **kw)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/changeset/databases/visitor.py", line 85, in run_single_visitor
2016-04-20 09:31:37.822 10471 ERROR keystone fn(element, **kwargs)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/changeset/ansisql.py", line 294, in visit_migrate_unique_constraint
2016-04-20 09:31:37.822 10471 ERROR keystone self._visit_constraint(*p, **k)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/changeset/ansisql.py", line 306, in _visit_constraint
2016-04-20 09:31:37.822 10471 ERROR keystone self.execute()
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/migrate/changeset/ansisql.py", line 44, in execute
2016-04-20 09:31:37.822 10471 ERROR keystone return self.connection.execute(self.buffer.getvalue())
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 906, in execute
2016-04-20 09:31:37.822 10471 ERROR keystone return self._execute_text(object, multiparams, params)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1054, in _execute_text
2016-04-20 09:31:37.822 10471 ERROR keystone statement, parameters
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
2016-04-20 09:31:37.822 10471 ERROR keystone context)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
2016-04-20 09:31:37.822 10471 ERROR keystone util.raise_from_cause(newraise, exc_info)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
2016-04-20 09:31:37.822 10471 ERROR keystone reraise(type(exception), exception, tb=exc_tb, cause=cause)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2016-04-20 09:31:37.822 10471 ERROR keystone context)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2016-04-20 09:31:37.822 10471 ERROR keystone cursor.execute(statement, parameters)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
2016-04-20 09:31:37.822 10471 ERROR keystone self.errorhandler(self, exc, value)
2016-04-20 09:31:37.822 10471 ERROR keystone File "/opt/mitaka/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2016-04-20 09:31:37.822 10471 ERROR keystone raise errorclass, errorvalue
2016-04-20 09:31:37.822 10471 ERROR keystone OperationalError: (_mysql_exceptions.OperationalError) (1091, "Can't DROP 'ixu_user_name_domain_id'; check that column/key exists") [SQL: u'ALTER TABLE user DROP INDEX ixu_user_name_domain_id']

[Sam Morrison (sorrison)]

To fix this I put a try catch around the drop call and all else worked

[Steve Martinelli (stevemar)]

what kind of database are you using? postgres? mysql?

we had a similar bug reported during the RC period (https://bugs.launchpad.net/keystone/+bug/1562965) but it was a result of running the migration multiple times before bug 1562934 was fixed (https://bugs.launchpad.net/keystone/+bug/1562934).

[Steve Martinelli (stevemar)]

AFAICT this constraint has been around for a while: https://github.com/openstack/keystone/blob/stable/kilo/keystone/common/sql/migrate_repo/versions/044_icehouse.py#L219-L221

have you had the same long running deployment for a while now?

[Sam Morrison (sorrison)]

Yeah this is been running for a while. We're using mysql. This install I tested has been around since havana I think

[Ala Rezmerita (arezmerita)]

We have the same problem: we started our deployment with icehouse, so in our database the constraint name is actually "domain_id".

mysql> show create table user;

| user | CREATE TABLE `user` (
  `id` varchar(64) NOT NULL,
  `name` varchar(255) NOT NULL,
  `extra` text,
  `password` varchar(128) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT NULL,
  `domain_id` varchar(64) NOT NULL,
  `default_project_id` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain_id` (`domain_id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When I replace the constraint name "ixu_user_name_domain_id" by "domain_id" the DB migration (kilo -> mitaka) successively finishes.

[Morgan Fainberg (mdrnstm)]

@steve this is the realization of the same bug we fixed in 88 but we determined it wasn't an issue with 91. I'll spin up a similar fix with backports this week.

[Liam Young (gnuoy)]

As arezmerita noted in earlier releases the constraint went by a different name:

Icehouse:
UNIQUE KEY `domain_id` (`domain_id`,`name`)

Liberty:
UNIQUE KEY `ixu_user_name_domain_id` (`domain_id`,`name`)

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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

commit 8c2412adecc08e7bcb07a3c679fcb45fdc06aeb7
Author: Liam Young <email address hidden>
Date: Wed Jun 15 10:01:43 2016 +0000

    Correct domain_id and name constraint dropping

    The 'domain_id' and 'name' unique constraint was not properly dropped
    in some cases because the unique constraint was not consistently
    named. In all cases we must search for the constraint expected,
    not assume the name of the constraint will be consistent
    (especially from older installs that have been moved forward in
    releases). This fix is modeled on the fix for a similair issue
    authored by Morgan Fainberg & Matthew Thode for Bug #1562934

    Migration 091:
        Fix to broken migration to prevent failed migrations when database is
        upgraded from Kilo (or below) to Mitaka
    Migration 097:
        Ensure that when Mitaka point release is applied the constraint and tables
        have been dropped if migration 91 was previously worked around.
    Migration 104:
        Ensure that when upgrading to Newton the constraint and tables
        have been dropped if migration 91 was previously worked around.

    Migration 91 drops 3 columns from the user table after the code to disable
    the constraint. I have included code in migrations 97 and 104 to also drop
    those columns if they are still present in case they were missed when working
    around Bug #1572341. This may be over kill.

    Change-Id: I076d7139b388e30be8826d0a4550256b5617d992
    Closes-bug: #1572341

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/mitaka
Review: https://review.openstack.org/332994

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/332994
Committed: https://git.openstack.org/cgit/openstack/keystone/commit/?id=a4be3399eec5eac6bbcd5540b4b057e8a9f562d2
Submitter: Jenkins
Branch: stable/mitaka

commit a4be3399eec5eac6bbcd5540b4b057e8a9f562d2
Author: Liam Young <email address hidden>
Date: Wed Jun 15 10:01:43 2016 +0000

    Correct domain_id and name constraint dropping

    The 'domain_id' and 'name' unique constraint was not properly dropped
    in some cases because the unique constraint was not consistently
    named. In all cases we must search for the constraint expected,
    not assume the name of the constraint will be consistent
    (especially from older installs that have been moved forward in
    releases). This fix is modeled on the fix for a similair issue
    authored by Morgan Fainberg & Matthew Thode for Bug #1562934

    Migration 091:
        Fix to broken migration to prevent failed migrations when database is
        upgraded from Kilo (or below) to Mitaka
    Migration 097:
        Ensure that when Mitaka point release is applied the constraint and tables
        have been dropped if migration 91 was previously worked around.

    Migration 91 drops 3 columns from the user table after the code to disable
    the constraint. I have included code in migrations 97 to also drop
    those columns if they are still present in case they were missed when working
    around Bug #1572341. This may be over kill.

    The following file conflicted since Opportunistic DB testing was included
    in the Newton release.

      keystone/tests/unit/test_sql_upgrade.py

    Note that migration 104 was removed since it does not exist in the Mitaka
    release. The unit tests were also modified accordingly.

    Change-Id: I076d7139b388e30be8826d0a4550256b5617d992
    Closes-bug: #1572341

[Doug Hellmann (doug-hellmann)]

This issue was fixed in the openstack/keystone 9.1.0 release.

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

This issue was fixed in the openstack/keystone 10.0.0.0b2 development milestone.