yahoo-eng-team team mailing list archive

[Attila Fazekas <afazekas@xxxxxxxxxx>]

Public bug reported:

Now the table is declared in this way:

show create table aggregate_hosts;

CREATE TABLE `aggregate_hosts` (
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `host` varchar(255) DEFAULT NULL,
  `aggregate_id` int(11) NOT NULL,
  `deleted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_aggregate_hosts0host0aggregate_id0deleted` (`host`,`aggregate_id`,`deleted`),
  KEY `aggregate_id` (`aggregate_id`),
  CONSTRAINT `aggregate_hosts_ibfk_1` FOREIGN KEY (`aggregate_id`) REFERENCES `aggregates` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8


The aggregate_hosts table in this form allows to have multiple deleted records for the same `host`,`aggregate_id`.
Does it really needed ?

- yes
Add an INDEX/KEY with (`deleted`,`host`)  OR Change the UNIQUE KEY to start with `deleted` : (`deleted`, `host`,`aggregate_id`) 
Add an INDEX/KEY with (`deleted`,`aggregate_id`) or extend the aggregate_id Index.

- no, enough to preserve only one record
Change the UNIQUE KEY  to (`host`,`aggregate_id`)   Consider using this as a  primary key instead of the id.
Add an INDEX/KEY with (`deleted`,`aggregate_id`) OR extend the aggregate_id Index.
Add an INDEX/KEY with (`deleted`,`host`)

- not at all
  Change the UNIQUE KEY  (`host`,`aggregate_id`)   Consider using this as a  primary key instead of the `id`.
  remove the `updated_at`, `deleted_at` , `deleted`  fields.

Note: `host` field should reference to an another table.

** 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/1442040

Title:
  aggregate_hosts does not uses deleted in search indexes

Status in OpenStack Compute (Nova):
  New

Bug description:
  Now the table is declared in this way:

  show create table aggregate_hosts;

  CREATE TABLE `aggregate_hosts` (
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `host` varchar(255) DEFAULT NULL,
    `aggregate_id` int(11) NOT NULL,
    `deleted` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_aggregate_hosts0host0aggregate_id0deleted` (`host`,`aggregate_id`,`deleted`),
    KEY `aggregate_id` (`aggregate_id`),
    CONSTRAINT `aggregate_hosts_ibfk_1` FOREIGN KEY (`aggregate_id`) REFERENCES `aggregates` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

  
  The aggregate_hosts table in this form allows to have multiple deleted records for the same `host`,`aggregate_id`.
  Does it really needed ?

  - yes
  Add an INDEX/KEY with (`deleted`,`host`)  OR Change the UNIQUE KEY to start with `deleted` : (`deleted`, `host`,`aggregate_id`) 
  Add an INDEX/KEY with (`deleted`,`aggregate_id`) or extend the aggregate_id Index.

  - no, enough to preserve only one record
  Change the UNIQUE KEY  to (`host`,`aggregate_id`)   Consider using this as a  primary key instead of the id.
  Add an INDEX/KEY with (`deleted`,`aggregate_id`) OR extend the aggregate_id Index.
  Add an INDEX/KEY with (`deleted`,`host`)

  - not at all
    Change the UNIQUE KEY  (`host`,`aggregate_id`)   Consider using this as a  primary key instead of the `id`.
    remove the `updated_at`, `deleted_at` , `deleted`  fields.

  Note: `host` field should reference to an another table.

To manage notifications about this bug go to:
https://bugs.launchpad.net/nova/+bug/1442040/+subscriptions