Missing contraint foreign key in "vm_template" table

[weizhouapache]

ISSUE TYPE

• Bug Report

The table "user_vm" and "vm_template" are expected to have the contraint foreign key, which link to "user_data.id".
However, the key exist in "user_vm" table, but missing in "vm_template" table.

Reason

The SQL procedure IDEMPOTENT_ADD_FOREIGN_KEY is used in the following SQL statements

CALL `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`('cloud.volumes', 'passphrase', 'id');
CALL `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`('cloud.user_vm', 'user_data', 'id');
CALL `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`('cloud.vm_template', 'user_data', 'id');

The results are

table name	constraint foreign key	note
volumes	fk_passphrase_id	OK
user_vm	fk_user_data_id	OK
vm_template	fk_user_data_id	Missing on mysql 5.5/mysql 8, Error on mysql 5.6/5.7 (#7358)

The keys are same in "user_vm" and "vm_template" table, which causes MySQL error Can't write; duplicate key in table '#sql-3755_b'

The error is ignored in mysql 5.5 and 8.0, but throws an exception in mysql 5.6/5.7 as the error codes are different
(1005 on mysql 5.5, 1826 on mysql 8.x, 1022 on mysql 5.6/5.7)

Suggestion

The key name should be renamed to f_<table name>_<foreign table name>_<foreign column name>, for example f_user_vm_user_data_id, instead of f_user_data_id

(refer to the old key names in #7255)

If so, need to

• create a new procedure ot remove the foreign keys if exists
• update the procedure to create new keys with new names

Related PRs:

• Volume encryption feature #6522
• engine/schema: fix IDEMPOTENT_ADD_FOREIGN_KEY procedure does not work on mysql8 #7252
• Userdata MySQL changes to use procedures to avoid duplicates #7255

user_vm table

MariaDB [cloud]> show create table user_vm\G
*************************** 1. row ***************************
       Table: user_vm
Create Table: CREATE TABLE `user_vm` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `iso_id` bigint(20) unsigned DEFAULT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `user_data` mediumtext,
  `user_data_id` bigint(20) unsigned DEFAULT NULL COMMENT 'id of the user data',
  `user_data_details` mediumtext COMMENT 'value of the comma-separated list of parameters',
  `update_parameters` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Defines if the parameters have been updated for the vm',
  `user_vm_type` varchar(255) DEFAULT 'UserVM' COMMENT 'Defines the type of UserVM',
  PRIMARY KEY (`id`),
  KEY `fk_user_data_id` (`user_data_id`),
  CONSTRAINT `fk_user_data_id` FOREIGN KEY (`user_data_id`) REFERENCES `user_data` (`id`),
  CONSTRAINT `fk_user_vm__id` FOREIGN KEY (`id`) REFERENCES `vm_instance` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

vm_template table

MariaDB [cloud]> show create table vm_template\G
*************************** 1. row ***************************
       Table: vm_template
Create Table: CREATE TABLE `vm_template` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `unique_name` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `uuid` varchar(40) DEFAULT NULL,
  `public` int(1) unsigned NOT NULL,
  `featured` int(1) unsigned NOT NULL,
  `type` varchar(32) DEFAULT NULL,
  `hvm` int(1) unsigned NOT NULL COMMENT 'requires HVM',
  `bits` int(6) unsigned NOT NULL COMMENT '32 bit or 64 bit',
  `url` varchar(255) DEFAULT NULL COMMENT 'the url where the template exists externally',
  `format` varchar(32) NOT NULL COMMENT 'format for the template',
  `created` datetime NOT NULL COMMENT 'Date created',
  `removed` datetime DEFAULT NULL COMMENT 'Date removed if not null',
  `account_id` bigint(20) unsigned NOT NULL COMMENT 'id of the account that created this template',
  `checksum` varchar(255) DEFAULT NULL COMMENT 'checksum for the template root disk',
  `display_text` varchar(4096) DEFAULT NULL COMMENT 'Description text set by the admin for display purpose only',
  `enable_password` int(1) unsigned NOT NULL DEFAULT '1' COMMENT 'true if this template supports password reset',
  `enable_sshkey` int(1) unsigned NOT NULL DEFAULT '0' COMMENT 'true if this template supports sshkey reset',
  `guest_os_id` bigint(20) unsigned NOT NULL COMMENT 'the OS of the template',
  `bootable` int(1) unsigned NOT NULL DEFAULT '1' COMMENT 'true if this template represents a bootable ISO',
  `prepopulate` int(1) unsigned NOT NULL DEFAULT '0' COMMENT 'prepopulate this template to primary storage',
  `cross_zones` int(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Make this template available in all zones',
  `extractable` int(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Is this template extractable',
  `hypervisor_type` varchar(32) DEFAULT NULL COMMENT 'hypervisor that the template belongs to',
  `source_template_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Id of the original template, if this template is created from snapshot',
  `template_tag` varchar(255) DEFAULT NULL COMMENT 'template tag',
  `sort_key` int(32) NOT NULL DEFAULT '0' COMMENT 'sort key used for customising sort method',
  `size` bigint(20) unsigned DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `update_count` bigint(20) unsigned DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  `dynamically_scalable` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'true if template contains XS/VMWare tools inorder to support dynamic scaling of VM cpu/memory',
  `parent_template_id` bigint(20) unsigned DEFAULT NULL COMMENT 'If datadisk template, then id of the root template this template belongs to',
  `direct_download` tinyint(1) DEFAULT '0' COMMENT 'Indicates if Secondary Storage is bypassed and template is downloaded to Primary Storage',
  `deploy_as_is` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'True if the template should be deployed with disks and networks as defined by OVF',
  `user_data_id` bigint(20) unsigned DEFAULT NULL COMMENT 'id of the user data',
  `user_data_link_policy` varchar(255) DEFAULT NULL COMMENT 'user data link policy with template',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uc_vm_template__uuid` (`uuid`),
  KEY `i_vm_template__removed` (`removed`),
  KEY `i_vm_template__public` (`public`)
) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

COMPONENT NAME

DB

CLOUDSTACK VERSION

4.18

CONFIGURATION

OS / ENVIRONMENT

SUMMARY

STEPS TO REPRODUCE

EXPECTED RESULTS

ACTUAL RESULTS

[soreana]

@harikrishna-patnala @weizhouapache What is the status of this issue, I see that its respective PR was merged. Shall we close the issue as well?

[weizhouapache]

@harikrishna-patnala @weizhouapache What is the status of this issue, I see that its respective PR was merged. Shall we close the issue as well?

@soreana
I will double check

[weizhouapache]

looks perfect

user_vm:

  KEY `fk_user_vm__user_data_id` (`user_data_id`),
  CONSTRAINT `fk_user_vm__user_data_id` FOREIGN KEY (`user_data_id`) REFERENCES `user_data` (`id`)

volumes

  CONSTRAINT `fk_volumes__passphrase_id` FOREIGN KEY (`passphrase_id`) REFERENCES `passphrase` (`id`),

vm_template:

  KEY `fk_vm_template__user_data_id` (`user_data_id`),
  CONSTRAINT `fk_vm_template__user_data_id` FOREIGN KEY (`user_data_id`) REFERENCES `user_data` (`id`)