BryanMLima commented on issue #7783:
URL: https://github.com/apache/cloudstack/issues/7783#issuecomment-1658944162
@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.
```sql
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:
```sql
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:
```sql
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](https://github.com/apache/cloudstack/pull/7095). Thus, it is required
manual intervention to normalize these isolated cases. The following query can
be executed to identify them:
```sql
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
https://github.com/apache/cloudstack/issues/7783#issuecomment-1655201143, 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.
--
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: commits-unsubscr...@cloudstack.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
