[jira] [Assigned] (CLOUDSTACK-6212) 'vm_instance' table has no AUTO_INCREMENT on 'id' field

["Rohit Yadav (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CLOUDSTACK-6212?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rohit Yadav reassigned CLOUDSTACK-6212:
---------------------------------------

    Assignee: Rohit Yadav

> 'vm_instance' table has no AUTO_INCREMENT on 'id' field
> -------------------------------------------------------
>
>                 Key: CLOUDSTACK-6212
>                 URL: https://issues.apache.org/jira/browse/CLOUDSTACK-6212
>             Project: CloudStack
>          Issue Type: Bug
>      Security Level: Public(Anyone can view this level - this is the default.) 
>    Affects Versions: 4.2.1, 4.3.0
>            Reporter: Remi Bergsma
>            Assignee: Rohit Yadav
>             Fix For: 4.5.0, 4.6.0
>
>
> 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.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)