yahoo-eng-team team mailing list archive

[Attila Fazekas <afazekas@xxxxxxxxxx>]

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