Skip to content

Duplicated guest OSes in ACS 4.18.0.0 #7783

Closed
#7799
Closed
Duplicated guest OSes in ACS 4.18.0.0#7783
#7799
Assignees
DaanHoogland
Labels
Severity:CriticalCritical bug
Milestone
4.18.1.0

Description

@weizhouapache
weizhouapache
opened on Jul 27, 2023

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

Activity

  1. weizhouapache
    changed the title [-]Duplicated guest oses in ACS 4.18.0.0[/-] [+]Duplicated guest OSes in ACS 4.18.0.0[/+] on Jul 27, 2023
  2. weizhouapache
    added this to the 4.18.1.0 milestone on Jul 27, 2023

  3. DaanHoogland commented on Jul 27, 2023

    @DaanHoogland
    DaanHoogland
    on Jul 27, 2023
    Contributor

    @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)

  4. weizhouapache commented on Jul 27, 2023

    @weizhouapache
    weizhouapache
    on Jul 27, 2023
    MemberAuthor

    @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).

  5. weizhouapache
    self-assigned this
    on Jul 28, 2023
  6. weizhouapache
    added
    Severity:CriticalCritical bug
    on Jul 28, 2023

  7. weizhouapache commented on Jul 28, 2023

    @weizhouapache
    weizhouapache
    on Jul 28, 2023
    MemberAuthor

    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).

  8. DaanHoogland commented on Jul 28, 2023

    @DaanHoogland
    DaanHoogland
    on Jul 28, 2023
    Contributor

    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

  9. BryanMLima commented on Jul 31, 2023

    @BryanMLima
    BryanMLima
    on Jul 31, 2023
    Contributor

    @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>);
    1. 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.

  10. weizhouapache commented on Jul 31, 2023

    @weizhouapache
    weizhouapache
    on Jul 31, 2023
    MemberAuthor

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

  11. DaanHoogland commented on Aug 1, 2023

    @DaanHoogland
    DaanHoogland
    on Aug 1, 2023
    Contributor

    @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 )

  12. harikrishna-patnala commented on Aug 1, 2023

    @harikrishna-patnala
    harikrishna-patnala
    on Aug 1, 2023
    Member

    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.

  13. weizhouapache commented on Aug 2, 2023

    @weizhouapache
    weizhouapache
    on Aug 2, 2023
    MemberAuthor

    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

  14. weizhouapache
    linked a pull request that will close this issueengine/schema: fix duplicated guest OSes in 4.18.0.0 #7799on Aug 2, 2023

  15. DaanHoogland commented on Aug 2, 2023

    @DaanHoogland
    DaanHoogland
    on Aug 2, 2023
    Contributor

    @weizhouapache, I added some pseudo code for the deduplication, will refine that later today.

  16. BryanMLima commented on Aug 2, 2023

    @BryanMLima
    BryanMLima
    on Aug 2, 2023
    Contributor

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

    I would like to, though at the moment I am focusing on other tasks, I will help to review and test the PR and any other way I can.

  17. DaanHoogland commented on Aug 10, 2023

    @DaanHoogland
    DaanHoogland
    on Aug 10, 2023
    Contributor

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

    I would like to, though at the moment I am focusing on other tasks, I will help to review and test the PR and any other way I can.

    I am working on this though still privately. I'll share something later today or tomorrow.

  18. weizhouapache
    assigned
    DaanHoogland
    on Aug 10, 2023

  19. DaanHoogland commented on Aug 11, 2023

    @DaanHoogland
    DaanHoogland
    on Aug 11, 2023
    Contributor

    @BryanMLima @weizhouapache see my comment in #7781. (should we keep multiple issues open for this work?

  20. BryanMLima commented on Aug 15, 2023

    @BryanMLima
    BryanMLima
    on Aug 15, 2023
    Contributor

    @BryanMLima @weizhouapache see my comment in #7781. (should we keep multiple issues open for this work?

    I think it is for the best to maintain multiple issues, even though they are similar and are being addressed in the same PR.

  21. weizhouapache
    removed their assignment
    on Aug 15, 2023

  22. weizhouapache commented on Aug 23, 2023

    @weizhouapache
    weizhouapache
    on Aug 23, 2023
    MemberAuthor

    fixed by #7799

  23. weizhouapache
    closed this as completedon Aug 23, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

Severity:CriticalCritical bug

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions