Helolo 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://storyboard.openstack.org/#!/story/2010251> / https://review.opendev.org/c/openstack/placement/+/856770 <https://review.opendev.org/c/openstack/placement/+/856770> * Keystone - https://bugs.launchpad.net/keystone/+bug/1988297 <https://bugs.launchpad.net/keystone/+bug/1988297> / https://review.opendev.org/c/openstack/keystone/+/885463 <https://review.opendev.org/c/openstack/keystone/+/885463> * Neutron - https://bugs.launchpad.net/neutron/+bug/1988421 <https://bugs.launchpad.net/neutron/+bug/1988421> / https://review.opendev.org/c/openstack/neutron/+/885456 <https://review.opendev.org/c/openstack/neutron/+/885456> * Nova - https://review.opendev.org/c/openstack/nova/+/856757 <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