`removeManagementServer` API fails with HTTP 530 (ERROR 1288: target table mshost_view is not updatable)

[wverleger]

problem

When attempting to remove a management server via the removeManagementServer API, the operation fails with an HTTP 530 error. The underlying issue is a MySQL database error because the application attempts to perform an UPDATE on mshost_view to set the removed timestamp. Since mshost_view uses a LEFT JOIN (mshost left join mshost_status), MySQL rejects the update as the view is not updatable.

https://cloudstack.apache.org/api/apidocs-4.22/apis/removeManagementServer.html

versions

CloudStack version:
4.22.0.0

OS and Database versions:

• OS: Ubuntu 24.04 LTS
• Database: MySQL (8.x)

The steps to reproduce the bug

Steps to reproduce:

1. Have an inactive/down management server.
2. Call the API to remove it: remove managementserver id=<uuid-of-mgmt-server>
3. The API call fails with the following error:
   Error: (HTTP 530, error code 4250) Unable to update on DB, due to: The target table mshost_view of the UPDATE is not updatable

Expected behavior:
The API should successfully soft-delete the management server by setting the removed timestamp in the database and return a success response.

Actual behavior:
The API fails and throws a database exception because the API targets the view instead of the base table.

What to do about it?

Root Cause Analysis:
Checking the view definition:
SHOW CREATE VIEW mshost_view;

Shows that it joins mshost and mshost_status. The UPDATE for soft-deleting needs to be explicitly directed at the mshost base table, not the mshost_view.

Workaround:
Manually update the base table in the database:
UPDATE mshost SET removed = NOW() WHERE uuid = '<uuid-of-mgmt-server>';

Reference to PR:
#10325

[weizhouapache]

good catch

lesson learned: we should avoid updating mysql reviews directly.

[DaanHoogland]

@wverleger #12147 fixes this issue it has been released in 22.1

[wverleger]

@DaanHoogland @weizhouapache Thanks for having a look and letting me know.