<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;"><div><span style="color: #cccccc;">Helolo openstack-discuss!</span></div><div><span style="color: #cccccc;">
</span></div><div><span style="color: #cccccc;">
</span></div><div><span style="color: #cccccc;">I recently saw lots of warnings the like of:
</span>
"/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)"
<span style="color: #cccccc;"></span></div><div><span style="color: #cccccc;">This originated from MySQL error </span>1831 (see [1]).</div><div><span style="color: #cccccc;">
</span></div><div><span style="color: #cccccc;">
</span></div><div><span style="color: #cccccc;">
</span></div><div><span style="color: #cccccc;">I then raised a few bugs and also did some changes in regards to the duplicate indices I noticed:</span></div><div><span style="color: #cccccc;">
</span></div><div><span style="color: #cccccc;"> * Placement </span><a href="https://storyboard.openstack.org/#!/story/2010251" class="external-link moz-txt-link-freetext" target="_blank" rel="nofollow noopener">https://storyboard.openstack.org/#!/story/2010251</a> / <a href="https://review.opendev.org/c/openstack/placement/+/856770" class="external-link moz-txt-link-freetext" target="_blank" rel="nofollow noopener">https://review.opendev.org/c/openstack/placement/+/856770</a></div><div> * Keystone - <a href="https://bugs.launchpad.net/keystone/+bug/1988297" class="external-link moz-txt-link-freetext" target="_blank" rel="nofollow noopener">https://bugs.launchpad.net/keystone/+bug/1988297</a> / <a href="https://review.opendev.org/c/openstack/keystone/+/885463" class="external-link moz-txt-link-freetext" target="_blank" rel="nofollow noopener">https://review.opendev.org/c/openstack/keystone/+/885463</a></div><div> * Neutron - <a href="https://bugs.launchpad.net/neutron/+bug/1988421" class="external-link moz-txt-link-freetext" target="_blank" rel="nofollow noopener">https://bugs.launchpad.net/neutron/+bug/1988421</a> / <a href="https://review.opendev.org/c/openstack/neutron/+/885456" class="external-link moz-txt-link-freetext" target="_blank" rel="nofollow noopener">https://review.opendev.org/c/openstack/neutron/+/885456</a></div><div> * Nova - <a href="https://review.opendev.org/c/openstack/nova/+/856757" class="external-link moz-txt-link-freetext" target="_blank" rel="nofollow noopener">https://review.opendev.org/c/openstack/nova/+/856757</a></div></div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">
</div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">
</div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">
</div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">
</div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">But running Percona's pt-duplicate-key-checker ([2]) there are quite a few more redundant indices reported:</div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">
</div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;"><blockquote type="cite"><div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">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`;
</div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">
<div><span style="color: #cccccc;"># ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes 35810
# Total Duplicate Indexes 84
# Total Indexes 1792
</span></div><div><span style="color: #cccccc;">
</span></div><div><span style="color: #cccccc;">
</span></div></div></blockquote>
(When adding more services (Telemetry, ...) there are even more indices reported.)</div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">
</div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">
</div>
<div style="color: rgb(204, 204, 204); background-color: rgb(31, 31, 31); font-weight: normal; font-size: 14px; line-height: 19px; white-space: pre;">
Does it make sense to address this more broadly maybe?
Especially with all the changes happening for SQLAlchemy 2.x currently?
Regards
Christian
[1] <a class="moz-txt-link-freetext" href="https://mariadb.com/kb/en/mariadb-error-codes/">https://mariadb.com/kb/en/mariadb-error-codes/</a>
[2] <a class="moz-txt-link-freetext" href="https://docs.percona.com/percona-toolkit/pt-duplicate-key-checker.html">https://docs.percona.com/percona-toolkit/pt-duplicate-key-checker.html</a>
<div><span style="color: #cccccc;">
</span></div>
</div>
</body>
</html>