Unable to remove MSSQL or MySQL server: 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 or MySQL 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.

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

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

Cause

Plesk database inconsistency. Some databases or database users from the removed MSSQL/MySQL instance were left over in 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. Log in to Plesk and remove the MSSQL/MySQL server from Tools & Settings > Database Servers.