Details
Major Description
We had an production issue today where two instances got the same id / instance name. This happened in a multi-master setup, with proper
'auto_increment_increment' and 'auto_increment_offset' variables set in MySQL.
I related the problem to the 'vm_instance' table as it seems the primary key, 'id', does not have an AUTO_INCREMENT set. Hence, two API calls around the same time on each of the management servers returned the same instance id with all kind of trouble as a result.
Could the AUTO_INCREMENT be added, or is there a reason it is missing?
Below is the CREATE statement of the table, which is CloudStack 4.2.1:
CREATE TABLE `vm_instance` (
`id` bigint(20) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`instance_name` varchar(255) NOT NULL COMMENT 'name of the vm instance running on the hosts',
`state` varchar(32) NOT NULL,
`vm_template_id` bigint(20) unsigned DEFAULT NULL,
`guest_os_id` bigint(20) unsigned NOT NULL,
`private_mac_address` varchar(17) DEFAULT NULL,
`private_ip_address` char(40) DEFAULT NULL,
`pod_id` bigint(20) unsigned DEFAULT NULL,
`data_center_id` bigint(20) unsigned NOT NULL COMMENT 'Data Center the instance belongs to',
`host_id` bigint(20) unsigned DEFAULT NULL,
`last_host_id` bigint(20) unsigned DEFAULT NULL COMMENT 'tentative host for first run or last host that it has been running on',
`proxy_id` bigint(20) unsigned DEFAULT NULL COMMENT 'console proxy allocated in previous session',
`proxy_assign_time` datetime DEFAULT NULL COMMENT 'time when console proxy was assigned',
`vnc_password` varchar(255) NOT NULL COMMENT 'vnc password',
`ha_enabled` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Should HA be enabled for this VM',
`limit_cpu_use` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Limit the cpu usage to service offering',
`update_count` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'date state was updated',
`update_time` datetime DEFAULT NULL COMMENT 'date the destroy was requested',
`created` datetime NOT NULL COMMENT 'date created',
`removed` datetime DEFAULT NULL COMMENT 'date removed if not null',
`type` varchar(32) NOT NULL COMMENT 'type of vm it is',
`vm_type` varchar(32) NOT NULL COMMENT 'vm type',
`account_id` bigint(20) unsigned NOT NULL COMMENT 'user id of owner',
`domain_id` bigint(20) unsigned NOT NULL,
`service_offering_id` bigint(20) unsigned NOT NULL COMMENT 'service offering id',
`reservation_id` char(40) DEFAULT NULL COMMENT 'reservation id',
`hypervisor_type` char(32) DEFAULT NULL COMMENT 'hypervisor type',
`uuid` varchar(40) DEFAULT NULL,
`disk_offering_id` bigint(20) unsigned DEFAULT NULL,
`cpu` int(10) unsigned DEFAULT NULL,
`ram` bigint(20) unsigned DEFAULT NULL,
`owner` varchar(255) DEFAULT NULL,
`speed` int(10) unsigned DEFAULT NULL,
`host_name` varchar(255) DEFAULT NULL,
`display_name` varchar(255) DEFAULT NULL,
`desired_state` varchar(32) DEFAULT NULL,
`dynamically_scalable` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'true if VM contains XS/VMWare tools inorder to support dynamic scaling of VM cpu/memory',
`display_vm` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Should vm instance be displayed to the end user',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `uc_vm_instance_uuid` (`uuid`),
KEY `i_vm_instance__removed` (`removed`),
KEY `i_vm_instance__type` (`type`),
KEY `i_vm_instance__pod_id` (`pod_id`),
KEY `i_vm_instance__update_time` (`update_time`),
KEY `i_vm_instance__update_count` (`update_count`),
KEY `i_vm_instance__state` (`state`),
KEY `i_vm_instance__data_center_id` (`data_center_id`),
KEY `fk_vm_instance__host_id` (`host_id`),
KEY `i_vm_instance__template_id` (`vm_template_id`),
KEY `fk_vm_instance__account_id` (`account_id`),
KEY `fk_vm_instance__service_offering_id` (`service_offering_id`),
KEY `fk_vm_instance__last_host_id` (`last_host_id`),
CONSTRAINT `fk_vm_instance__account_id` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`),
CONSTRAINT `fk_vm_instance__host_id` FOREIGN KEY (`host_id`) REFERENCES `host` (`id`),
CONSTRAINT `fk_vm_instance__last_host_id` FOREIGN KEY (`last_host_id`) REFERENCES `host` (`id`),
CONSTRAINT `fk_vm_instance__service_offering_id` FOREIGN KEY (`service_offering_id`) REFERENCES `service_offering` (`id`),
CONSTRAINT `fk_vm_instance__template_id` FOREIGN KEY (`vm_template_id`) REFERENCES `vm_template` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
I did not see any relevant change in 'schema-421to430.sql' for the upcoming 4.3 release.