Resolved Unable to delete database server

[schauhan11]

Server operating system version

Ubuntu 22.04.3 LTS

Plesk version and microupdate number

Version 18.0.55 Update #1

I created a new MySQL database server under Tools & Settings > Database Servers. However, I must have supplied wrong username and/or password because Plesk is not able to connect to the server. Now I cannot remove the server through UI because selection checkbox is grayed out. Is there any other way to delete inaccessible database server from Plesk?

-Sam

 

[Peter Debik]

You cannot remove a server when databases on that server are registered with Plesk. Please see Adding and Removing Database Servers for details on how to proceed.

 

[margaretcika]

Try using SSH:
- Connect to the Plesk server via SSH
- Run the command: sudo plesk dbm host-delete --hostname=<database server hostname>
- Enter your Plesk password

I am not sure about MySQL, but you can try it:
- Connect to the Plesk database
- Run the query: DELETE FROM data_bases WHERE name = '<database server name>';
- Save your changes and exit the MySQL client

(Of course, once you have deleted the database server, restart the Plesk service)

 

[schauhan11]

@Peter: I created the database through Tools & Settings >> Database Server >> Add Database Server because none existed. Plesk is unable to connect to the newly created MySQL database server and I cannot delete it because selection checkbox is grayed out. The server does not contain any active databases (see picture below).
I am just starting with Plesk so I am not sure when adding a database server whether Plesk creates a new server or tries to register an existing server. I had provided a random username and password on the Add Database Server screen and that has led to this situation. Any input is highly appreciated.

 

[Peter Debik]

The server that you see is the server that is needed by Plesk to run. Plesk has a database where it stores all configuration data. You cannot remove that server (or if you remove it from the operating system, Plesk won't work any longer). Why do you want to remove that database server?

 

[schauhan11]

I am not able to edit the username and password in Plesk so my idea was to remove this server and then create another server outside of Plesk. Then I can add it to Plesk after providing correct username and password.
I can keep this server if it is needed by Plesk. But rightnow Plesk is not able to connect to this server because of wrong username/password so how is it able to use it?

 

[Peter Debik]

If Plesk wasn't able to connect to the database server, you could not see the page where you manage database servers.

The root-username of the database on a Plesk system is "admin". The password is managed by Plesk. Do not edit either. It will break the installation.

 

[margaretcika]

Try going to Tools & Settings > Database Servers and click the Remove button.

If you are unable to edit the username and password for the database server in Plesk, you can remove the server from Plesk and then create a new database server outside of Plesk. You can then add the new database server to Plesk and provide the correct username and password.

Plesk is able to use a database server even if it cannot connect to it, but it will not be able to manage the databases on that server((

 

[schauhan11]

@margaretcika Thanks for your responses. Here's the sequence of events that has led to this situation. When I went to Tools & Settings >> Database Server the first time there was no database sever listed there so I decided to add a MySQL/MariaDB server.
I was under the impression that Plesk is going to create a new server with the user name and password I supplied on Add Database Server screen so I picked some random username/password combo. Right after adding the server I got the message "The test connection to this server has failed because no valid administrator's username and password were specified.."
Now Plesk is not able to access the server although I do have a MariaDB running with root username and a different password. I am unable to change username/password or delete the server as selection checkbox is grayed out. Also it is not allowing me to add another database server with localhost host name and correct username/password combo so I am really stuck.

 

[Peter Debik]

What is your explanation that you can access Plesk if you don't have a database server?

 

[Kaspar]

Plesk requires either a MySQL or MariaDB database to run, because thats where Plesk stores (most) of it's data. Fortunately the fact that you are able to login to Plesk testifies that there is a local database server up and running. Although it is weird that there previously was no database server shown on the Tools & Settings > Database Servers. It should have been listed there.

Any way, to solve the issue with the login credentials you can try and run the plesk repair mysql command via SSH. The command will verify the integrity of MySQL databases and database users and repair them if there are any inconsistencies. Although I am not 100% sure if this will solve the issue. If it does not, report back. In that case you'll probably have to manually add the admin user credentials. I'll try and guide you trough the steps.

If you are in a hurry to get this issue solved though, consider contacting Plesk support directly via https://support.plesk.com/hc/en-us/requests/new.

 

[mow]

Try going to Tools & Settings > Database Servers and click the Remove button.

If you are unable to edit the username and password for the database server in Plesk, you can remove the server from Plesk and then create a new database server outside of Plesk. You can then add the new database server to Plesk and provide the correct username and password.

Plesk is able to use a database server even if it cannot connect to it, but it will not be able to manage the databases on that server((

Why does this look like the question was run through and remixed by ChatGPT or similar generative AI?

BTT: I'd guess @schauhan11 just didn't notice the server at first. Or the internal server only shows up since a later update.
If you enter invalid authentication data, you can't finish adding a new server - after pressing OK, you just get thrown back to the same setup screen. You can only leave by entering valid data and then press OK, or bail out by pressing cancel. In the latter case there will NOT be a new server in the list.

 

[margaretcika]

Err, I'm not an AI, what's going on just 0_0

 

[schauhan11]

@Kaspar Here's the output I got after running 'plesk repair mysql' command. I am not sure what this means.

Checking MySQL database servers
Unable connect to localhost:3306: Administrator credentials are not set ............................................................... [ERROR]
Repair databases on available servers ............................... [OK]
Repair database users on available servers .......................... [OK]
Error messages: 1; Warnings: 0; Errors resolved: 0


@Peter Debik It is clear that Plesk is using MySQL server otherwise I will not be able to login and interact with Plesk. However, I am quite sure when I went first time to the Tools & Settings >> Database Server, there was no server listed there. In fact I got a screen telling "Nothing to See Here" or something to that effect. I wish I had a capture of that screen.

@mow Plesk did finish adding the server with invalid username/password. The problem is that it's a dead entry now and I can't find a way to either remove it or enter valid login credentials. It does seem that Plesk did not check authentication criteria before an creating entry in the server list. This should be easy to reproduce on another installation.

 

[mow]

@mow Plesk did finish adding the server with invalid username/password. The problem is that it's a dead entry now and I can't find a way to either remove it or enter valid login credentials. It does seem that Plesk did not check authentication criteria before an creating entry in the server list. This should be easy to reproduce on another installation.

I actually did try before posting and couldn't reproduce. plesk just wouldn't proceed with adding another server.
It looks like this:

Note that there is the same message you have, but for the postgreSQL connector, which we do not use.
Does the account you use for plesk have full admin rights?
What does "plesk bin database-server -l" show?

 

[schauhan11]

@mow Response to "plesk bin database-server -l" is "mysql localhost:3306 default"
It almost seems like Plesk has created an entry that it treats as the default server and hence not allowing me to delete it. But since the entry was created with invalid credentials Plesk is not able to connect with the server. However, Plesk is using the server all along. It is all so confusing!
Here's the server settings screen shown when I click on the little gray X button.

 

[Kaspar]

@Kaspar Here's the output I got after running 'plesk repair mysql' command. I am not sure what this means.

Checking MySQL database servers
Unable connect to localhost:3306: Administrator credentials are not set ............................................................... [ERROR]
Repair databases on available servers ............................... [OK]
Repair database users on available servers .......................... [OK]
Error messages: 1; Warnings: 0; Errors resolved: 0

That what I expect, I was hoping that the repair utility would not the issue automatically. But is does not, so we'll have to do this manually.

Can you post the current database users? You can find the database users running the following commands.

# plesk db

mysql> use mysql;

SELECT user FROM user;

You can use the CTRL + C keys exit MySQL once your done.

 

[schauhan11]

@Kaspar Here's the list of users. They are all for localhost.

 

[Kaspar]

@schauhan11 great, that looks good. Next step is to change the database user associated the Plesk administrator user. From the screenshot you posted previously I gather that the current database user is named server_admin, we'll need to change that to just admin. As thats the database user Plesk need to authenticate. This will probably will solve your issue, but additional steps might be needed later.

To do this run the following commands:

# plesk db

UPDATE `DatabaseServers` SET `admin_login` = 'admin' WHERE `admin_login` = 'server_admin';

Next login to Plesk, navigate to Tools & Settings > Database Servers and click on the listed database. Check to see to displayed username is admin. See example image bellow.

Last, but important, step. If the displayed username is admin, click the OK button. If it's not admin, click the Cancel button and let me know.

 

[schauhan11]

@Kaspar YES! That worked. Thank you so much for patiently walking me through the process. I was so close to deleting the server and starting over. You saved me a lot of time. Thanks everyone.