(Sending this message again, as apparently the formatting was messed up
the first time).


Hello OpenStack-Discuss,



I recently saw lots of warnings the like of:

"​/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 originated from MySQL error 1831 (see [1]).



I then raised a few bugs and also did some changes in regards to the
duplicate indices I noticed:

  * Placement https://storyboard.openstack.org/#!/story/2010251 /
https://review.opendev.org/c/openstack/placement/+/856770
  * Keystone - https://bugs.launchpad.net/keystone/+bug/1988297 /
https://review.opendev.org/c/openstack/keystone/+/885463
  * Neutron -  https://bugs.launchpad.net/neutron/+bug/1988421 /
https://review.opendev.org/c/openstack/neutron/+/885456
  * Nova - https://review.opendev.org/c/openstack/nova/+/856757




But running Percona's pt-duplicate-key-checker ([2]) there are quite a
few more redundant indices reported:

 > ALTER TABLE `cinder`.`driver_initiator_data` DROP INDEX
`ix_driver_initiator_data_initiator`;
 > ALTER TABLE `cinder`.`quota_usages` DROP INDEX
`ix_quota_usages_project_id`;
 > ALTER TABLE `cinder`.`quota_usages` DROP INDEX
`quota_usage_project_resource_idx`;
 > ALTER TABLE `designate`.`records` DROP FOREIGN KEY `records_ibfk_2`;
 > ALTER TABLE `designate`.`recordsets` DROP INDEX `rrset_zoneid`;
 > ALTER TABLE `designate`.`recordsets` DROP INDEX `rrset_type`;
 > ALTER TABLE `glance`.`image_members` DROP INDEX
`ix_image_members_image_id`;
 > ALTER TABLE `glance`.`image_members` DROP INDEX
`ix_image_members_image_id_member`;
 > ALTER TABLE `glance`.`image_properties` DROP INDEX
`ix_image_properties_image_id`;
 > ALTER TABLE `glance`.`image_tags` DROP INDEX `ix_image_tags_image_id`;
 > ALTER TABLE `keystone`.`access_rule` DROP INDEX `external_id`;
 > ALTER TABLE `keystone`.`access_rule` DROP INDEX `user_id`;
 > ALTER TABLE `keystone`.`project_tag` DROP INDEX `project_id`;
 > ALTER TABLE `keystone`.`token` DROP INDEX `ix_token_expires`;
 > ALTER TABLE `mysql`.`transaction_registry` DROP INDEX
`commit_timestamp`, ADD INDEX `commit_timestamp` (`commit_timestamp`);
 > ALTER TABLE `neutron`.`addressgrouprbacs` DROP INDEX
`ix_addressgrouprbacs_target_project`;
 > ALTER TABLE `neutron`.`addressscoperbacs` DROP INDEX
`ix_addressscoperbacs_target_project`;
 > ALTER TABLE `neutron`.`floatingipdnses` DROP INDEX
`ix_floatingipdnses_floatingip_id`;
 > ALTER TABLE `neutron`.`networkdnsdomains` DROP INDEX
`ix_networkdnsdomains_network_id`;
 > ALTER TABLE `neutron`.`networkrbacs` DROP INDEX
`ix_networkrbacs_target_project`;
 > ALTER TABLE `neutron`.`ovn_hash_ring` DROP INDEX
`ix_ovn_hash_ring_node_uuid`;
 > ALTER TABLE `neutron`.`ovn_revision_numbers` DROP INDEX
`ix_ovn_revision_numbers_resource_uuid`;
 > ALTER TABLE `neutron`.`portdataplanestatuses` DROP INDEX
`ix_portdataplanestatuses_port_id`;
 > ALTER TABLE `neutron`.`portdnses` DROP INDEX `ix_portdnses_port_id`;
 > ALTER TABLE `neutron`.`ports` DROP INDEX
`ix_ports_network_id_mac_address`;
 > ALTER TABLE `neutron`.`portuplinkstatuspropagation` DROP INDEX
`ix_portuplinkstatuspropagation_port_id`;
 > ALTER TABLE `neutron`.`qos_minimum_bandwidth_rules` DROP INDEX
`ix_qos_minimum_bandwidth_rules_qos_policy_id`;
 > ALTER TABLE `neutron`.`qos_minimum_packet_rate_rules` DROP INDEX
`ix_qos_minimum_packet_rate_rules_qos_policy_id`;
 > ALTER TABLE `neutron`.`qos_packet_rate_limit_rules` DROP INDEX
`ix_qos_packet_rate_limit_rules_qos_policy_id`;
 > ALTER TABLE `neutron`.`qos_policies_default` DROP INDEX
`ix_qos_policies_default_project_id`;
 > ALTER TABLE `neutron`.`qospolicyrbacs` DROP INDEX
`ix_qospolicyrbacs_target_project`;
 > ALTER TABLE `neutron`.`quotas` DROP INDEX `ix_quotas_project_id`;
 > ALTER TABLE `neutron`.`quotausages` DROP INDEX
`ix_quotausages_project_id`;
 > ALTER TABLE `neutron`.`securitygrouprbacs` DROP INDEX
`ix_securitygrouprbacs_target_project`;
 > ALTER TABLE `neutron`.`segmenthostmappings` DROP INDEX
`ix_segmenthostmappings_segment_id`;
 > ALTER TABLE `neutron`.`subnet_dns_publish_fixed_ips` DROP INDEX
`ix_subnet_dns_publish_fixed_ips_subnet_id`;
 > ALTER TABLE `neutron`.`subnetpoolrbacs` DROP INDEX
`ix_subnetpoolrbacs_target_project`;
 > ALTER TABLE `nova`.`agent_builds` DROP INDEX
`agent_builds_hypervisor_os_arch_idx`;
 > ALTER TABLE `nova`.`block_device_mapping` DROP INDEX
`block_device_mapping_instance_uuid_idx`;
 > ALTER TABLE `nova`.`console_auth_tokens` DROP INDEX
`console_auth_tokens_token_hash_idx`;
 > ALTER TABLE `nova`.`fixed_ips` DROP INDEX `address`;
 > ALTER TABLE `nova`.`fixed_ips` DROP INDEX `network_id`;
 > ALTER TABLE `nova`.`instance_actions` DROP INDEX `instance_uuid_idx`;
 > ALTER TABLE `nova`.`instance_type_extra_specs` DROP INDEX
`instance_type_extra_specs_instance_type_id_key_idx`;
 > ALTER TABLE `nova`.`instance_type_projects` DROP INDEX
`instance_type_id`;
 > ALTER TABLE `nova`.`instances` DROP INDEX `uniq_instances0uuid`;
 > ALTER TABLE `nova`.`instances` DROP INDEX `instances_project_id_idx`;
 > ALTER TABLE `nova`.`inventories` DROP INDEX
`inventories_resource_provider_id_idx`;
 > ALTER TABLE `nova`.`inventories` DROP INDEX
`inventories_resource_provider_resource_class_idx`;
 > ALTER TABLE `nova`.`networks` DROP INDEX `networks_vlan_deleted_idx`;
 > ALTER TABLE `nova`.`resource_providers` DROP INDEX
`resource_providers_name_idx`;
 > ALTER TABLE `nova`.`resource_providers` DROP INDEX
`resource_providers_uuid_idx`;
 > ALTER TABLE `nova_api`.`build_requests` DROP INDEX
`build_requests_instance_uuid_idx`;
 > ALTER TABLE `nova_api`.`cell_mappings` DROP INDEX `uuid_idx`;
 > ALTER TABLE `nova_api`.`flavor_extra_specs` DROP INDEX
`flavor_extra_specs_flavor_id_key_idx`;
 > ALTER TABLE `nova_api`.`host_mappings` DROP INDEX `host_idx`;
 > ALTER TABLE `nova_api`.`instance_mappings` DROP INDEX
`instance_uuid_idx`;
 > ALTER TABLE `nova_api`.`inventories` DROP INDEX
`inventories_resource_provider_id_idx`;
 > ALTER TABLE `nova_api`.`inventories` DROP INDEX
`inventories_resource_provider_resource_class_idx`;
 > ALTER TABLE `nova_api`.`placement_aggregates` DROP INDEX
`ix_placement_aggregates_uuid`;
 > ALTER TABLE `nova_api`.`project_user_quotas` DROP INDEX
`project_user_quotas_user_id_idx`;
 > ALTER TABLE `nova_api`.`request_specs` DROP INDEX
`request_spec_instance_uuid_idx`;
 > ALTER TABLE `nova_api`.`resource_providers` DROP INDEX
`resource_providers_name_idx`;
 > ALTER TABLE `nova_api`.`resource_providers` DROP INDEX
`resource_providers_uuid_idx`;
 > ALTER TABLE `nova_cell0`.`agent_builds` DROP INDEX
`agent_builds_hypervisor_os_arch_idx`;
 > ALTER TABLE `nova_cell0`.`block_device_mapping` DROP INDEX
`block_device_mapping_instance_uuid_idx`;
 > ALTER TABLE `nova_cell0`.`console_auth_tokens` DROP INDEX
`console_auth_tokens_token_hash_idx`;
 > ALTER TABLE `nova_cell0`.`fixed_ips` DROP INDEX `address`;
 > ALTER TABLE `nova_cell0`.`fixed_ips` DROP INDEX `network_id`;
 > ALTER TABLE `nova_cell0`.`instance_actions` DROP INDEX
`instance_uuid_idx`;
 > ALTER TABLE `nova_cell0`.`instance_type_extra_specs` DROP INDEX
`instance_type_extra_specs_instance_type_id_key_idx`;
 > ALTER TABLE `nova_cell0`.`instance_type_projects` DROP INDEX
`instance_type_id`;
 > ALTER TABLE `nova_cell0`.`instances` DROP INDEX `uniq_instances0uuid`;
 > ALTER TABLE `nova_cell0`.`instances` DROP INDEX
`instances_project_id_idx`;
 > ALTER TABLE `nova_cell0`.`inventories` DROP INDEX
`inventories_resource_provider_id_idx`;
 > ALTER TABLE `nova_cell0`.`inventories` DROP INDEX
`inventories_resource_provider_resource_class_idx`;
 > ALTER TABLE `nova_cell0`.`networks` DROP INDEX
`networks_vlan_deleted_idx`;
 > ALTER TABLE `nova_cell0`.`resource_providers` DROP INDEX
`resource_providers_name_idx`;
 > ALTER TABLE `nova_cell0`.`resource_providers` DROP INDEX
`resource_providers_uuid_idx`;
 > ALTER TABLE `placement`.`inventories` DROP INDEX
`inventories_resource_provider_id_idx`;
 > ALTER TABLE `placement`.`inventories` DROP INDEX
`inventories_resource_provider_resource_class_idx`;
 > ALTER TABLE `placement`.`placement_aggregates` DROP INDEX
`ix_placement_aggregates_uuid`;
 > ALTER TABLE `placement`.`resource_providers` DROP INDEX
`resource_providers_name_idx`;
 > ALTER TABLE `placement`.`resource_providers` DROP INDEX
`resource_providers_uuid_idx`;
 >
 >
 >
 > #
########################################################################
 > # Summary of indexes
 > #
########################################################################
 >
 > # Size Duplicate Indexes   35810
 > # Total Duplicate Indexes  84
 > # Total Indexes            1792
 >
 >

(When adding more services (Telemetry, ...) there are even more indices
reported.)




Does it make sense to address this more broadly maybe?
Especially with all the changes happening for SQLAlchemy 2.x currently?




Regards


Christian


[1] https://mariadb.com/kb/en/mariadb-error-codes/
[2] https://docs.percona.com/percona-toolkit/pt-duplicate-key-checker.html