yahoo-eng-team team mailing list archive

[OpenStack Infra <1988297@xxxxxxxxxxxxxxxxxx>]

Reviewed:  https://review.opendev.org/c/openstack/keystone/+/885463
Committed: https://opendev.org/openstack/keystone/commit/90dcff07c03ee60227b01f47d67fe9e5b1629593
Submitter: "Zuul (22348)"
Branch:    master

commit 90dcff07c03ee60227b01f47d67fe9e5b1629593
Author: Christian Rohmann <christian.rohmann@xxxxxxxxx>
Date:   Wed Jun 7 14:49:35 2023 +0200

    sql: Fixup for invalid unique constraint on external_id in access_rule table
    
    There was a big drop of invalid constraints with [1]. One of them was on
    `external_id` in the access_rule table.
    
    While the change made it into a Alembic revision with [2], it still exists in
    the schema causing an a new Alembic autogeneration to actually add it again as
    a revision.
    
    [1] https://review.opendev.org/c/openstack/keystone/+/851845
    [2] https://opendev.org/openstack/keystone/commit/7d169870fe418b9aa5765dc2f413ecdf9c8f1d48#diff-26484e3f6683ce7557e17b67220003784ff84fbe
    
    Closes-Bug: #1988297
    Change-Id: I66626ba8771ef2aa8b3580fd3f5d15fd4b58ab48


** Changed in: keystone
       Status: In Progress => Fix Released

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to OpenStack Identity (keystone).
https://bugs.launchpad.net/bugs/1988297

Title:
  Unique constraint on external_id still created for table access_rule

Status in OpenStack Identity (keystone):
  Fix Released

Bug description:
  Currently the primary key and an additional unique index are configured on the same column.
  This is why sqlalchemy logs a warning on a database migration displaying following information:

  ```
  ​/usr/lib/python3/dist-packages/pymysql/cursors.py:170: Warning: (1831, 'Duplicate index `uniq_instances0uuid`. This is deprecated and will be disallowed in a future release')
  result = self._query(query)
  ```
  (​This example is actually taken from the nova output, but looks just the same for Keystone.
  There actually is the same issue within Nova schemas, see bug https://bugs.launchpad.net/nova/+bug/1641185)

  From my understanding of the documentation of mysql (see [1] [2]) and
  postgres (see [3] [4]) a unique constraint, which is created in the
  first place, automatically creates an index for the column(s). So
  there should be no need to create an additional index for the same
  column:

  ```
  Table: access_rule (https://opendev.org/openstack/keystone/src/commit/7c2d0f589c8daf5c65a80ed20d1e7fbfcc282312/keystone/common/sql/migrations/versions/27e647c0fad4_initial_version.py#L120)

  Column: external_id
  Indexes:
      Unique Constraint: access_rule_external_id_key
      Index: external_id
  ```


  [1] https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-unique
  [2] https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html
  [3] https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS
  [4] https://www.postgresql.org/docs/current/indexes-types.html

To manage notifications about this bug go to:
https://bugs.launchpad.net/keystone/+bug/1988297/+subscriptions