weizhouapache opened a new issue, #7370:
URL: https://github.com/apache/cloudstack/issues/7370
#### ISSUE TYPE
<!-- Pick one below and delete the rest -->
* 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, <br> 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:
- #6522
- #7252
- #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
<!--
Categorize the issue, e.g. API, VR, VPN, UI, etc.
-->
~~~
DB
~~~
##### CLOUDSTACK VERSION
<!--
New line separated list of affected versions, commit ID for issues on main
branch.
-->
~~~
4.18
~~~
##### CONFIGURATION
<!--
Information about the configuration if relevant, e.g. basic network,
advanced networking, etc. N/A otherwise
-->
##### OS / ENVIRONMENT
<!--
Information about the environment if relevant, N/A otherwise
-->
##### SUMMARY
<!-- Explain the problem/feature briefly -->
##### STEPS TO REPRODUCE
<!--
For bugs, show exactly how to reproduce the problem, using a minimal
test-case. Use Screenshots if accurate.
For new features, show how the feature would be used.
-->
<!-- Paste example playbooks or commands between quotes below -->
~~~
~~~
<!-- You can also paste gist.github.com links for larger files -->
##### EXPECTED RESULTS
<!-- What did you expect to happen when running the steps above? -->
~~~
~~~
##### ACTUAL RESULTS
<!-- What actually happened? -->
<!-- Paste verbatim command output between quotes below -->
~~~
~~~
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
