[ https://issues.apache.org/jira/browse/CLOUDSTACK-6195?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Joris van Lieshout updated CLOUDSTACK-6195: ------------------------------------------- Description: This is the table in our 4.2.1 env that has been upgraded from 2.2.14. CREATE TABLE `host_details` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `host_id` bigint(20) unsigned NOT NULL COMMENT 'host id', `name` varchar(255) NOT NULL, `value` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `fk_host_details__host_id` (`host_id`), CONSTRAINT `fk_host_details__host_id` FOREIGN KEY (`host_id`) REFERENCES `host` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=752966 DEFAULT CHARSET=utf8; And this is the table of a fresh 4.x install: CREATE TABLE `host_details` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `host_id` bigint(20) unsigned NOT NULL COMMENT 'host id', `name` varchar(255) NOT NULL, `value` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_host_id_name` (`host_id`,`name`), KEY `fk_host_details__host_id` (`host_id`), CONSTRAINT `fk_host_details__host_id` FOREIGN KEY (`host_id`) REFERENCES `host` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=242083 DEFAULT CHARSET=utf8; The effect of this missing bug is a lot of duplicate entries in the host_details table. The duplicate information on the host_details table causes the api call listHosts to return the same host tag multiple time (to be exact: the number of duplicate entries in the host_details table for that host). was: This is the table in our 4.2.1 env that has been upgraded from 2.2.14. CREATE TABLE `host_details` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `host_id` bigint(20) unsigned NOT NULL COMMENT 'host id', `name` varchar(255) NOT NULL, `value` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `fk_host_details__host_id` (`host_id`), CONSTRAINT `fk_host_details__host_id` FOREIGN KEY (`host_id`) REFERENCES `host` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=752966 DEFAULT CHARSET=utf8; And this is the table of a fresh 4.x install: CREATE TABLE `host_details` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `host_id` bigint(20) unsigned NOT NULL COMMENT 'host id', `name` varchar(255) NOT NULL, `value` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_host_id_name` (`host_id`,`name`), KEY `fk_host_details__host_id` (`host_id`), CONSTRAINT `fk_host_details__host_id` FOREIGN KEY (`host_id`) REFERENCES `host` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=242083 DEFAULT CHARSET=utf8; > an ACS db upgraded from Pre-4.0 version is missing unique key constraint on > host_details > ---------------------------------------------------------------------------------------- > > Key: CLOUDSTACK-6195 > URL: https://issues.apache.org/jira/browse/CLOUDSTACK-6195 > Project: CloudStack > Issue Type: Bug > Security Level: Public(Anyone can view this level - this is the > default.) > Components: Upgrade > Affects Versions: 4.0.0, 4.0.1, 4.0.2, 4.1.0, 4.1.1, 4.2.0, 4.2.1, 4.1.2 > Environment: Pre-4.0 db upgraded to 4.x. We have confirmed this bug > in a db that started out as 2.2.14. > Reporter: Joris van Lieshout > > This is the table in our 4.2.1 env that has been upgraded from 2.2.14. > CREATE TABLE `host_details` ( > `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, > `host_id` bigint(20) unsigned NOT NULL COMMENT 'host id', > `name` varchar(255) NOT NULL, > `value` varchar(255) NOT NULL, > PRIMARY KEY (`id`), > KEY `fk_host_details__host_id` (`host_id`), > CONSTRAINT `fk_host_details__host_id` FOREIGN KEY (`host_id`) REFERENCES > `host` (`id`) ON DELETE CASCADE > ) ENGINE=InnoDB AUTO_INCREMENT=752966 DEFAULT CHARSET=utf8; > And this is the table of a fresh 4.x install: > CREATE TABLE `host_details` ( > `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, > `host_id` bigint(20) unsigned NOT NULL COMMENT 'host id', > `name` varchar(255) NOT NULL, > `value` varchar(255) NOT NULL, > PRIMARY KEY (`id`), > UNIQUE KEY `uk_host_id_name` (`host_id`,`name`), > KEY `fk_host_details__host_id` (`host_id`), > CONSTRAINT `fk_host_details__host_id` FOREIGN KEY (`host_id`) REFERENCES > `host` (`id`) ON DELETE CASCADE > ) ENGINE=InnoDB AUTO_INCREMENT=242083 DEFAULT CHARSET=utf8; > The effect of this missing bug is a lot of duplicate entries in the > host_details table. The duplicate information on the host_details table > causes the api call listHosts to return the same host tag multiple time (to > be exact: the number of duplicate entries in the host_details table for that > host). -- This message was sent by Atlassian JIRA (v6.2#6252)