Just the not deleted members needs to be selected, an instance group can gather many-many deleted instances during on his lifetime.
The selecting query contains a condition for omitting the deleted records:
SELECT instance_groups.created_at AS instance_groups_created_at, instance_groups.updated_at AS instance_groups_updated_at, instance_groups.deleted_at AS instance_groups_deleted_at, instance_groups.deleted AS instance_groups_deleted, instance_groups.id AS instance_groups_id, instance_groups.user_id AS instance_groups_user_id, instance_groups.project_id AS instance_groups_project_id, instance_groups.uuid AS instance_groups_uuid, instance_groups.name AS instance_groups_name, instance_group_policy_1.created_at AS instance_group_policy_1_created_at, instance_group_policy_1.updated_at AS instance_group_policy_1_updated_at, instance_group_policy_1.deleted_at AS instance_group_policy_1_deleted_at, instance_group_policy_1.deleted AS instance_group_policy_1_deleted, instance_group_policy_1.id AS instance_group_policy_1_id, instance_group_policy_1.policy AS instance_group_policy_1_policy, instance_group_policy_1.group_id AS instance_group_policy_1_group_id, instance_group_member_1.created_at AS instance_group_member_1_created_at, instance_group_member_1.updated_at AS instance_group_member_1_updated_at, instance_group_member_1.deleted_at AS instance_group_member_1_deleted_at, instance_group_member_1.deleted AS instance_group_member_1_deleted, instance_group_member_1.id AS instance_group_member_1_id, instance_group_member_1.instance_id AS instance_group_member_1_instance_id, instance_group_member_1.group_id AS instance_group_member_1_group_id FROM instance_groups LEFT OUTER JOIN instance_group_policy AS instance_group_policy_1 ON instance_groups.id = instance_group_policy_1.group_id AND instance_group_policy_1.deleted = 0 AND instance_groups.deleted = 0 LEFT OUTER JOIN instance_group_member AS instance_group_member_1 ON instance_groups.id = instance_group_member_1.group_id AND instance_group_member_1.deleted = 0 AND instance_groups.deleted = 0 WHERE instance_groups.deleted = 0 AND instance_groups.project_id = '6da55626d6a04f4c99980dc17d34235f';
(Captured at $nova server-group-list)
But actually nova fetches the deleted records because the `deleted` field is 0,
even if the instance already deleted.
For figuring out the instance is actually deleted the nova API issues other otherwise not needed queries.
The instance_group_member records actually set to deleted only when instance_group deleted.
show create table instance_group_member;
CREATE TABLE `instance_group_member` (
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
`deleted` int(11) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`instance_id` varchar(255) DEFAULT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `group_id` (`group_id`),
KEY `instance_group_member_instance_idx` (`instance_id`),
CONSTRAINT `instance_group_member_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `instance_groups` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1, Please delete the instance_group_member records when the instance gets deleted.
2, Please add (`deleted`,`group_id`) BTREE index as combined index, in this way it will be usable in other situations as well, for example when only a single group's members is needed.
Fix proposed to branch: master
Review: https:/