Public bug reported: 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. ** Affects: nova Importance: Undecided Status: New -- You received this bug notification because you are a member of Yahoo! Engineering Team, which is subscribed to OpenStack Compute (nova). https://bugs.launchpad.net/bugs/1442098 Title: instance_group_member entries not deleted when the instance deleted Status in OpenStack Compute (Nova): New Bug description: 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. To manage notifications about this bug go to: https://bugs.launchpad.net/nova/+bug/1442098/+subscriptions -- Mailing list: https://launchpad.net/~yahoo-eng-team Post to : yahoo-eng-team@lists.launchpad.net Unsubscribe : https://launchpad.net/~yahoo-eng-team More help : https://help.launchpad.net/ListHelp