Duplicate indexes in table ports of neutron database

[Christian Rohmann]

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

[Brian Haley (brian-haley)]

These two items were added to the DB code for different reasons - one for correctness (constraint), the other for performance (index). They were done some time ago (8 years, Kilo-ish cycle) so I'm a little weary at changing things without having someone take a closer look.

Do you have a log of the Warning being emitted for this table? I didn't see anything in a new devstack install.

[Christian Rohmann (christian-rohmann)]

Hey Brian,

well the warning originates from MySQL/MariaDB, see https://mariadb.com/docs/reference/mdb/error-codes/ER_DUP_INDEX/ or "1831" on https://mariadb.com/kb/en/mariadb-error-codes/. It just happen during DDL SQL statements modifing the schemas, but it's actually just MariaDB complaining.

I tried to manually reproduce the warning by creating a regular and a unique index on the same columns, but MariaDB 10.6.8 would only complain if both were either regular indexes or a both unique indexes.

In any case a unique index is more specific and should be all you need. In the referenced case of Nova (https://bugs.launchpad.net/nova/+bug/1641185) there it's actually two unique indexes being created on the exact same column "uuid" (no pun intended).

If you look at their code at https://opendev.org/openstack/nova/src/commit/c53ec4e48884235566962bc934cbf292ad5b67b8/nova/db/main/models.py#L269 they simply marked the column as unique in the first place and did also add a unique index.

Maybe it's cleaner code to just mark a column unique and don't bother with an additional constraint?

Would you consider dropping the additional index in the neutron case then?

[Launchpad Janitor (janitor)]

[Expired for neutron because there has been no activity for 60 days.]

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/neutron/+/885456

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 23.0.0.0b3 development milestone.