Open Stack

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.openstack.org/pipermail/openstack-discuss/attachments/20230615/f7cf5818/attachment.htm>