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 -- 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