[
https://issues.apache.org/jira/browse/CLOUDSTACK-6212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14236418#comment-14236418
]
Rohit Yadav commented on CLOUDSTACK-6212:
-----------------------------------------
I've add some alter table statements for resource tables such as vm_instance,
domain_router, user_vm etc. which have a primary key (id) but it does not
auto_increment. I will request any of the API/DB gurus to review the PR:
https://github.com/apache/cloudstack/pull/52 cc [~alena1108] [~kishan]
[~minchen07] [~prachidamle]
Also, I don't know if we should do the same for following tables as I'm not
sure if it will break anything in the CloudStack, please advise for following
tables:
| cluster_vsm_map |
| op_host |
| op_host_tranfer |
| op_host_upgrade |
| op_it_work |
| op_lock |
| op_networks |
| op_router_monitoring_services |
| op_user_stats_log |
| user_vm_clone_setting |
| vm_work_job |
> '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)
