Unable to remove a MSSQL, PostgreSQL or MySQL / MariaDB server from the Plesk GUI: A database server cannot be deleted while there are databases or database users hosted on it

Symptoms

• It is not possible to remove a MSSQL, PostgreSQL or MySQL / MariaDB database server from Tools & Settings > Database Servers because of the following error:

  PLESK_ERROR: A database server cannot be deleted while there are databases or database users hosted on it.

• The PostgreSQL, MSSQL, MySQL / MariaDB instance has already been removed directly from the operating system or from a remote server.

• There are no databases assigned to this instance in the Plesk GUI.

Cause

Plesk database inconsistency. Some databases or database users from the removed MSSQL, PostgreSQL, MySQL / MariaDB instance were left over in the Plesk database (psa).

Resolution

1. Log in to the server via SSH (Linux) or via RDP (Windows)

2. Back up the Plesk database

3. Access the Plesk database:

   C:\> plesk db

4. Check the removed database server's ID - for example, the database server was MSSQL 2017 so its ID is 2:

   MYSQL_WIN: MariaDB [psa]> select * from databaseservers;
   +----+-------------------+------+-------+----------------+-------------+------------------------------------------------------------+---------------+------------+---------+
   | id | host | port | type | server_version | admin_login | admin_password | parameters_id | last_error | fork |
   +----+-------------------+------+-------+----------------+-------------+------------------------------------------------------------+---------------+------------+---------+
   | 1 | localhost | 3306 | mysql | 10.3.22 | admin | $AES-128-CBC$uVEi85mXuzXihJtsuHnvSA$sk8DnrU+DcDpXOfSIBh3jw | NULL | no_error | mariadb |
   | 2 | .\MSSQLSERVER2017 | 0 | mssql | 14.00.1000 | sa | $AES-128-CBC$AkXGyKIuRdtJFcHJ8OEMSA$tlGRDfoeP8NWqrnCJQ0i8Q | NULL | | |
   | 3 | .\MSSQLSERVER2019 | 0 | mssql | 15.00.2000 | sa | $AES-128-CBC$Jo7wEW9/UD1s4FzXDO9Evw$jBkDTIrbwg+qHm7be1lNYw | NULL | no_error | |
   | 4 | localhost | 3308 | mysql | 5.7.30 | root | $AES-128-CBC$02FtrbM5P8+MXTvKgzRhWQ$T3bKMmo9eRX2FYqJQ7j0yg | NULL | no_error | mysql |
   +----+-------------------+------+-------+----------------+-------------+------------------------------------------------------------+---------------+------------+---------+
   4 rows in set (0.004 sec)

5. Using the database server's ID, find leftover records in data_bases and db_users tables:

   MYSQL_WIN: MariaDB [psa]> select * from data_bases where db_server_id=2;
   Empty set (0.010 sec)

   MYSQL_WIN: MariaDB [psa]> select * from db_users where db_server_id=2;
   +----+-------+------------+-------+--------+--------+--------------+-------------+-----------+
   | id | login | account_id | db_id | status | dom_id | db_server_id | external_id | role |
   +----+-------+------------+-------+--------+--------+--------------+-------------+-----------+
   | 4 | test | 13 | 0 | normal | 3 | 2 | NULL | readWrite |
   +----+-------+------------+-------+--------+--------+--------------+-------------+-----------+
   1 row in set (0.013 sec)

   Note: one of these queries may return empty results - it means that there are either no orphaned databases (like in this example) or no orphaned database users

6. Remove all leftover records found in these tables:

   MYSQL_WIN: MariaDB [psa]> delete from data_bases where db_server_id=2;
   MYSQL_WIN: MariaDB [psa]> delete from db_users where db_server_id=2;

7. Exit the Plesk database editing process by typing exit

8. Log into the Plesk GUI

9. Remove the PostgreSQL, MSSQL or MySQL / MariaDB database server from Plesk > Tools & Settings > Database Servers.