yahoo-eng-team team mailing list archive

[Christian Rohmann <1988421@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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: ports (https://opendev.org/openstack/neutron/src/commit/732c1dcbc2fe95bc3d8b6a61b124d59595958b4f/neutron/db/models_v2.py#L128)

Columns: network_id, mac_address
Indexes:
    Unique Constraint: uniq_ports0network_id0mac_address
    Index: ix_ports_network_id_mac_address
```

[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

** Affects: neutron
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to neutron.
https://bugs.launchpad.net/bugs/1988421

Title:
   Duplicate indexes in table ports of neutron database

Status in neutron:
  New

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: ports (https://opendev.org/openstack/neutron/src/commit/732c1dcbc2fe95bc3d8b6a61b124d59595958b4f/neutron/db/models_v2.py#L128)

  Columns: network_id, mac_address
  Indexes:
      Unique Constraint: uniq_ports0network_id0mac_address
      Index: ix_ports_network_id_mac_address
  ```

  [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/neutron/+bug/1988421/+subscriptions