Duplicated guest OSes in ACS 4.18.0.0

[weizhouapache]

database query

mysql> select display_name,count(1) as count from guest_os group by display_name having count >1;
+------------------------------+-------+
| display_name | count |
+------------------------------+-------+
| SCO OpenServer 5 | 2 |
| Ubuntu 20.04 LTS | 2 |
| Debian GNU/Linux 11 (64-bit) | 2 |
| Debian GNU/Linux 11 (32-bit) | 2 |
| CentOS 9 | 2 |
| Oracle Linux 9 | 2 |
+------------------------------+-------+
6 rows in set (0.00 sec)

ISSUE TYPE

• Bug Report

COMPONENT NAME

CLOUDSTACK VERSION

4.18.0.0

CONFIGURATION

OS / ENVIRONMENT

SUMMARY

STEPS TO REPRODUCE

EXPECTED RESULTS

ACTUAL RESULTS

[DaanHoogland]

@weizhouapache there are no double mappings, do we need to solve this?

mysql> select * from guest_os_hypervisor goh
 -> where goh.guest_os_id in
 -> (select min(id) from guest_os go2
 -> where go2.display_name in
 -> (select display_name from
 -> (select display_name, count(1) as count from guest_os go1 group by display_name having count > 1) tab)
 -> group by display_name having count(display_name) > 1)
 -> or goh.guest_os_id in
 -> (select max(id) from guest_os go2
 -> where go2.display_name in
 -> (select display_name from
 -> (select display_name, count(1) as count from guest_os go1 group by display_name having count > 1) tab)
 -> group by display_name having count(display_name) > 1)
 -> order by goh.guest_os_name;
+------+-----------------+--------------------------+-------------+--------------------+--------------------------------------+---------------------+---------+-----------------+
| id | hypervisor_type | guest_os_name | guest_os_id | hypervisor_version | uuid | created | removed | is_user_defined |
+------+-----------------+--------------------------+-------------+--------------------+--------------------------------------+---------------------+---------+-----------------+
| 8582 | KVM | CentOS 9 | 314 | default | a2153d17-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:54 | NULL | 0 |
| 9130 | VMware | centos9_64Guest | 314 | 7.0.1.0 | d8fb28ad-0d1c-4a0b-af25-fa952596e302 | 2023-07-26 12:04:59 | NULL | 0 |
| 8580 | XenServer | Debian Bullseye 11 | 312 | 8.2.1 | a2001556-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:54 | NULL | 0 |
| 8579 | XenServer | Debian Bullseye 11 | 311 | 8.2.1 | a1ffef97-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:54 | NULL | 0 |
| 9126 | VMware | debian11Guest | 312 | 7.0 | 2c06dcbc-c9d7-4b83-9758-1d08ec8ee71c | 2023-07-26 12:04:59 | NULL | 0 |
| 9125 | VMware | debian11_64Guest | 311 | 7.0 | bbe27bc1-ad3a-47a4-ab49-1675e4a761d2 | 2023-07-26 12:04:59 | NULL | 0 |
| 9317 | VMware | openServer5Guest | 357 | 7.0.3.0 | 605bbf84-88af-4625-9891-fd1ba442f39a | 2023-07-26 12:04:59 | NULL | 0 |
| 9308 | VMware | openServer5Guest | 357 | 6.0 | 3870cd2f-75e7-4860-8ed7-1ba12935f19d | 2023-07-26 12:04:59 | NULL | 0 |
| 9309 | VMware | openServer5Guest | 357 | 6.5 | ce445487-764e-493f-8a66-1727c62da60c | 2023-07-26 12:04:59 | NULL | 0 |
| 9310 | VMware | openServer5Guest | 357 | 6.7 | 0f74a9bc-fbaa-4ac0-8a97-3aa776caeabe | 2023-07-26 12:04:59 | NULL | 0 |
| 9311 | VMware | openServer5Guest | 357 | 6.7.1 | 74a386c4-bb7c-4f25-ae9f-291d7d1cdb9c | 2023-07-26 12:04:59 | NULL | 0 |
| 9312 | VMware | openServer5Guest | 357 | 6.7.2 | 8c5d7795-7095-475b-8603-56fc458fc676 | 2023-07-26 12:04:59 | NULL | 0 |
| 9313 | VMware | openServer5Guest | 357 | 6.7.3 | a55a5d0a-ff70-4b2a-ab63-5d140026cfd9 | 2023-07-26 12:04:59 | NULL | 0 |
| 9314 | VMware | openServer5Guest | 357 | 7.0 | 15483064-8f28-48b2-a76a-6931fe3d78ca | 2023-07-26 12:04:59 | NULL | 0 |
| 9315 | VMware | openServer5Guest | 357 | 7.0.1.0 | 6939f8cd-42c9-48c8-aa7b-c19d6a7d0b94 | 2023-07-26 12:04:59 | NULL | 0 |
| 9316 | VMware | openServer5Guest | 357 | 7.0.2.0 | f81774f0-6e04-4d88-b1e7-37b0681310ac | 2023-07-26 12:04:59 | NULL | 0 |
| 8583 | KVM | Oracle Linux 9 | 315 | default | a215774b-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:54 | NULL | 0 |
| 9135 | VMware | oracleLinux9_64Guest | 333 | 7.0.1.0 | c427cccb-1aec-4cf7-9308-fef6ad5b1446 | 2023-07-26 12:04:59 | NULL | 0 |
| 8963 | VMware | otherGuest | 85 | 8.0.0.1 | a21c2033-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:54 | NULL | 0 |
| 8708 | VMware | otherGuest | 85 | 8.0 | a21a5a3e-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:54 | NULL | 0 |
| 1387 | VMware | otherGuest | 85 | 4.0 | 9244e597-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:28 | NULL | 0 |
| 1528 | VMware | otherGuest | 85 | 4.1 | 924fe1ee-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:28 | NULL | 0 |
| 1669 | VMware | otherGuest | 85 | 5.0 | 925a6833-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:28 | NULL | 0 |
| 1810 | VMware | otherGuest | 85 | 5.1 | 9262dadf-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:28 | NULL | 0 |
| 8175 | VMware | otherGuest | 85 | 7.0.3.0 | a05d10ec-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:51 | NULL | 0 |
| 1951 | VMware | otherGuest | 85 | 5.5 | 926b4705-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:28 | NULL | 0 |
| 7920 | VMware | otherGuest | 85 | 7.0.2.0 | a05b1b62-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:51 | NULL | 0 |
| 2448 | VMware | otherGuest | 85 | 6.0 | 979422bf-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:37 | NULL | 0 |
| 2889 | VMware | otherGuest | 85 | 6.5 | 99bf7c1f-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:40 | NULL | 0 |
| 4822 | VMware | otherGuest | 85 | 6.7 | 9c020482-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:44 | NULL | 0 |
| 7659 | VMware | otherGuest | 85 | 7.0.1.0 | 9e2d4fb1-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:48 | NULL | 0 |
| 7404 | VMware | otherGuest | 85 | 7.0 | 9e2ac25c-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:48 | NULL | 0 |
| 5587 | VMware | otherGuest | 85 | 6.7.3 | 9c06981f-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:44 | NULL | 0 |
| 5332 | VMware | otherGuest | 85 | 6.7.2 | 9c04fba6-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:44 | NULL | 0 |
| 5077 | VMware | otherGuest | 85 | 6.7.1 | 9c0391d9-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:44 | NULL | 0 |
| 8591 | VMware | otherLinux64Guest | 315 | 7.0.1.0 | a2172a90-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:54 | NULL | 0 |
| 9081 | VMware | otherLinux64Guest | 315 | 8.0.0.1 | a21c2dd8-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:54 | NULL | 0 |
| 8826 | VMware | otherLinux64Guest | 315 | 8.0 | a21a6527-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 12:04:54 | NULL | 0 |
| 8599 | VMware | otherLinux64Guest | 315 | 7.0.3.0 | a2191662-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:54 | NULL | 0 |
| 8595 | VMware | otherLinux64Guest | 315 | 7.0.2.0 | a2184548-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:54 | NULL | 0 |
| 8587 | VMware | otherLinux64Guest | 315 | 7.0 | a2167342-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:54 | NULL | 0 |
| 202 | VmWare | SCO OpenServer 5 | 85 | default | 91751676-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:26 | NULL | 0 |
| 7808 | KVM | Ubuntu 20.04 LTS | 305 | default | 9f3c380a-2bac-11ee-8c22-e884a5ebaa69 | 2023-07-26 13:04:50 | NULL | 0 |
| 9123 | Xenserver | Ubuntu Focal Fossa 20.04 | 305 | 8.2.0 | f9d11dbf-6102-4c8b-835a-e5b85d501f97 | 2023-07-26 12:04:59 | NULL | 0 |
+------+-----------------+--------------------------+-------------+--------------------+--------------------------------------+---------------------+---------+-----------------+
44 rows in set (0.04 sec)

[weizhouapache]

@weizhouapache there are no double mappings, do we need to solve this?

@DaanHoogland
there are no double mappings - that's the problem. It means one of the guest OS is not good (guest os mappings are missing).

[weizhouapache]

as @BryanMLima mentioned in #7095 (comment)
I have some environments that were recently updated to the 4.18 from the 4.16 and there are 43 duplicate Guest OS types, specifically, those changed in this PR (#7095).

[DaanHoogland]

copying from #7095 (comment) :

@weizhouapache @BryanMLima , seems like we need;

1. a reconsiliation method (merging duplicates)
2. an extra check in addGuestOsAndHypervisorMappings to make sure such situations don't arise again

[BryanMLima]

@DaanHoogland @weizhouapache, to solve this issue, we used the query below to identify the Guest OS entries that were duplicate and were not used by any VM or template.

SELECT g_os.id
FROM cloud.guest_os g_os
WHERE g_os.removed IS NULL
AND id NOT IN (
 SELECT MIN(sub_g_os.id)
 FROM cloud.guest_os sub_g_os
 GROUP BY sub_g_os.display_name, sub_g_os.category_id
)
AND (
 SELECT count(*)
 FROM cloud.vm_template vt
 WHERE vt.state <> 'Inactive'
 AND vt.guest_os_id = g_os.id
) + (
 SELECT count(*)
 FROM cloud.vm_instance vi
 WHERE vi.removed IS NULL
 AND vi.guest_os_id = g_os.id
) = 0;

To remove the duplicate Guest OS types and hypervisors mappings, the following queries were executed:

1. Normalize duplicate Guest OS:

UPDATE cloud.guest_os
SET removed = "2000-01-01 00:00:00"
WHERE id IN (<list of IDs>);

2. Normalize duplicate Guest OS hypervisors mappings:

UPDATE cloud.guest_os_hypervisor
SET removed = "2000-01-01 00:00:00"
WHERE guest_os_id IN (<list of IDs>);

It was used the date 2000-01-01 to easily identify that the entry was not removed by an API call. However, it is possible that in some environments, templates, and VMs were created using the duplicate entries introduced in PR#7095. Thus, it is required manual intervention to normalize these isolated cases. The following query can be executed to identify them:

SELECT *
FROM cloud.guest_os g_os
WHERE g_os.removed IS NULL
AND id NOT IN (
 SELECT MIN(sub_g_os.id)
 FROM cloud.guest_os sub_g_os
 GROUP BY sub_g_os.display_name, sub_g_os.category_id
);

As @DaanHoogland mentioned in #7783 (comment), we need to find a way to automate the normalization of these isolated cases, and to validate if a Guest OS and hypervisors mappings already exists when using the method addGuestOsAndHypervisorMappings. The second is trivial, the problem lies in the automation of the normalization.

[weizhouapache]

@DaanHoogland @BryanMLima
great, thanks for your replies.
would like you to pick this up ?

[DaanHoogland]

@BryanMLima , I think your scripts are a good step into solving "issue 1". I think we should not update the removed field on guest_os_hypervisor but instead the guest_os_id field. Next we must also update guest_os_id in vm_instance. After that I think we can set the abundant guest_os to removed.
I like your use of the date 2000-01-01 to make clear these where accidents and to enable further trouble shooting if we were not complete in our actions.

Am I missing something? (cc @weizhouapache )

[harikrishna-patnala]

copying from #7095 (comment) :

@weizhouapache @BryanMLima , seems like we need;

1. a reconsiliation method (merging duplicates)
2. an extra check in addGuestOsAndHypervisorMappings to make sure such situations don't arise again

Agree with this approach, I had a quick discussion with @weizhouapache on this in similar terms.

On the second part "an extra check in addGuestOsAndHypervisorMappings to make sure such situations don't arise again", for guest OS entry check, do we need to mark "is_user_defined" as false? which I dont think it is not required to change, since it is already added.

[weizhouapache]

copying from #7095 (comment) :
@weizhouapache @BryanMLima , seems like we need;

1. a reconsiliation method (merging duplicates)
2. an extra check in addGuestOsAndHypervisorMappings to make sure such situations don't arise again

Agree with this approach, I had a quick discussion with @weizhouapache on this in similar terms.

On the second part "an extra check in addGuestOsAndHypervisorMappings to make sure such situations don't arise again", for guest OS entry check, do we need to mark "is_user_defined" as false? which I dont think it is not required to change, since it is already added.

yes @harikrishna-patnala
based on what we have discussed, I pushed a commit to shapeblue repository. see #7799

we can work together to fix it. @DaanHoogland @harikrishna-patnala