Articles in this section

See more

Unable to remove a database: This resource is used by installed web application

Avatar
Kuzma Ivanov
Updated
Follow
Plesk for Windows Plesk for Linux kb: technical ABT: Group B

Applicable to:

  • Plesk for Linux
  • Plesk for Windows

Symptoms

  • Unable to remove a database in Plesk:

    1. The option Remove Database is grayed out at Plesk > Domains > example.com > Databases.
    2. The check-box next to the database name is grayed out and the database name contains This resource is used by the installed site application at  Tools & Settings > Database Server > Databases.

  • There is no application installed at Domains > example.com > Applications.

  • Unable to change a domain for the database in Plesk > Domains > example.com > Databases, there is no option to change the domain/subdomain that is related to a database.

Cause

Database inconsistency.

Resolution

In order to fix this, the following can be done:

Click on a section to expand

Through SSH connection and CLI

  1. Connect to a Plesk server via SSH/RDP.

  2. Create a backup of the Plesk database:

    • on Linux, run the command:

      # plesk db dump psa > /root/psa_backup.sql

    • on Windows Server, start a command prompt as an Administrator and run the command:

      C:\> plesk db dump psa > C:\psa_backup.sql

  3. Connect to the Plesk database:

    MYSQL_LIN: plesk db

  4. Find ID of the subscription to which the affected domain belongs:

    MYSQL_LIN: mysql> select id from domains where name='example.com';
    +----+
    | id |
    +----+
    | 23 |
    +----+

  5. Find the connection ID between an application and a database using ID from the step 4:

    MYSQL_LIN: mysql> SELECT data_bases.name,apsResources.pleskId FROM `data_bases` INNER JOIN `DatabaseServers` ON data_bases.db_server_id = DatabaseServers.id LEFT JOIN `apsResources` ON apsResources.pleskId = data_bases.id AND apsResources.pleskType="db" WHERE (dom_id = 23);
    +------------+---------+
    | name       | pleskId |
    +------------+---------+
    | example_db |      17 |
    +------------+---------+

  6. Delete a connection record using pleskId from the step 5:

    MYSQL_LIN: mysql> DELETE FROM apsResources WHERE pleskType="db" AND pleskId=17;

  7. Remove the database at Plesk > Domains > example.com > Databases.

Through Plesk interface

  1. Log in to Plesk

  2. Go to Tools and settings > Database Servers and access PhpMyAdmin

  3. Create psa database dump through select psa database and clicking on the export tab and clicking on Go

  4. Click SQL tab to access SQL query box

    mceclip0.png

  5. Find ID of the subscription to which the affected domain belongs:

    MYSQL_LIN: mysql> select id from domains where name='example.com';
    +----+
    | id |
    +----+
    | 23 |
    +----+

  6. Find the connection ID between an application and a database using ID from the step 4:

    MYSQL_LIN: mysql> SELECT data_bases.name,apsResources.pleskId FROM `data_bases` INNER JOIN `DatabaseServers` ON data_bases.db_server_id = DatabaseServers.id LEFT JOIN `apsResources` ON apsResources.pleskId = data_bases.id AND apsResources.pleskType="db" WHERE (dom_id = 23);
    +------------+---------+
    | name       | pleskId |
    +------------+---------+
    | example_db |      17 |
    +------------+---------+

  7. Delete a connection record using pleskId from the step 5:

    MYSQL_LIN: mysql> DELETE FROM apsResources WHERE pleskType="db" AND pleskId=17;

  8. Remove the database at Plesk > Domains > example.com > Databases.

Comments

4 comments

Sort by Date Votes
  • Avatar
    Rajkumar Janarthanan

    Excellent. It worked the way it should be... Thanks Mr. Marc Lopez.

    0
    Comment actions Permalink
  • Avatar
    Michael Gellner

    I get 

        Zeige Datensätze 0 - 0 (1 insgesamt, Die Abfrage dauerte 0.0006 Sekunden.)

    Means: 0 (zero)

    (Obsidian, Ubuntu 18.04)

    What else can I do?

     

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello Michael Gellner

    If you have the same symptoms, there's a possibility that inconsistency you have differs.

    A deeper investigation is required for such cases. I would suggest submitting a support request to Plesk or to the reseller from which the license was purchased.

    0
    Comment actions Permalink
  • Avatar
    Del C (Edited )

    I found a unique situation where I had an orphan db tied to an active domain and I could NOT delete the old db via Plesk panel (since it thought it was still in use as main db for that domain), so I ran the commands listed above.

    However, when backing up the server I get this error:

    Warning: Application "WordPress"
    Unable to back up database #. Error: Unable to find row with id in data_bases table.

    In order to fix this, you have to manually add the record of the actual main db you're using with the domain to the apsResources table to associate that db as the 'master' for that domain.

    Prior to doing #6 you should run these two commands:

    mysql> SELECT id, registryId FROM apsResources WHERE pleskType="db" AND pleskId=17;
    +----+--------------------------------------+
    | id | registryId                           |
    +----+--------------------------------------+
    | 3  | 3a50ca33-b863-456e-b5da-c60e91ae4b26 |
    +----+--------------------------------------+

    ^ make note of id, registryID

    mysql> SELECT id, name FROM data_bases where dom_id=23;
    +-----+--------------------------+
    | id  | name                     |
    +-----+--------------------------+
    | 17  | example_db               |
    | 27  | example_db_NEW           |
    +-----+--------------------------+ 

    ^ Make note of id (27) - this is ID of the existing database you want to be associated with your domain 
    (associating it within wordpress toolkit won't do this for you)

    After doing step #7:

    mysql> INSERT into apsResources values (3,'3a50ca33-b863-456e-b5da-c60e91ae4b26','db',27);

     

    1
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles