Resolved ERROR 1049 (42000): Unknown database 'psa'

[geekaboo]

Server operating system version

Ubuntu 22.04

Plesk version and microupdate number

Plesk Obsidian 18.0.41

I guess I did something really stupid, but any help or advice will be welcome.

Long story short: My server was full, so I couldn't login into Plesk and all my sites were down. I tried to fix it with the repair tool kit, but I could not login, my credentials were rejected (I tried to use root & password, but it didn't work). Next thing I did was to try to reset Plesk admin's password using this command: plesk bin admin --set-admin-password -passwd "new_password", but I got the following error: DB query failed: SQLSTATE[HY000] [2002] No such file or directory, so I read somewhere that I could free some space by deleting logs in /var/log and dumps in /var/lib/psa/dumps/ …so I did. *HUGE MISTAKE, I GUESS*

Now, I can't access to Plesk, not even the repair tool kit, and every time I try to run plesk login command, I get this error: DB query failed: SQLSTATE[HY000] [1049] Unknown database 'psa' exit status 1. Also, I get ERROR 1049 (42000): Unknown database 'psa' when I try to run plesk db command.

Please, tell me if there is a way I can get everything back on its right place without losing any of my websites data.

Thank you very much in advance!

 

[Peter Debik]

Hi @geekaboo, it seems that you also deleted the database(s), at least the psa database. Normally, this can easily be restored from the daily dumps, but as you write that you also deleted /var/lib/psa/dumps I assume that you do not have these daily dumps anymore either (unless your local backup path had been changed before). If you would like to try to restore the psa database, here is advice:

https://support.plesk.com/hc/en-us/articles/12376967822231-How-to-restore-Plesk-system-databases-from-a-Plesk-daily-pre-upgrade-dump

If you do not find the psa database on your server, very likely other databases will be missing, too. In that case I can only recommend to setup a new server, to install Plesk on it and to restore your Plesk contents from your server backup (that you hopefully did on an external FTP storage space).

If you are on a container solution, such as Virtuozzo or similar, it is also thinkable that your provider does daily snapshot backups of your container. In that case the best solution could be to restore the full instance from your provider's daily backups.

 

[geekaboo]

I can see all my databases, including psa db, in /var/lib/mysql1687372073 (via SFTP), but files are not .sql, when I open any of them, the files (I assume they're the tables) look like the ones in the second image:

Is there a way to export them? I tried to use mysqldump with no success.

 

[Peter Debik]

Regarding your previous post with the "Unknown database 'psa'" I assumed that the database server is running, but the database cannot be found.

Have you checked whether your database server is running? I assume you are using MariaDB, then it would be
# systemctl status mariadb
The database server fails when there is not enough disk space. If you have freed disk space, you can start the database server with
# systemctl start mariadb

Does that work?

 

[geekaboo]

Yes, I have freed disk space (it has 55% free space).
Yes, database server is running.
And yes, I am using MariaDB.

This is what I get after running # systemctl status mariadb

● mariadb.service - MariaDB 10.6.12 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2023-08-02 01:41:56 UTC; 6h ago
Docs: man:mariadbd(8)
systemd
Process: 6035 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, statu>
Process: 6036 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, stat>
Process: 6038 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; >
Process: 6091 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, sta>
Process: 6093 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 6079 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 9 (limit: 4605)
Memory: 87.3M
CPU: 5.152s
CGroup: /system.slice/mariadb.service
└─6079 /usr/sbin/mariadbd

Notice: journal has been rotated since unit was started, output may be incomplete.
lines 1-19/19 (END)

So, I also think that the database cannot be found. What would you suggest me to do?

 

[Peter Debik]

The /var/lib/mysql path is the default path for the databases, but not /var/lib/mysql1687372073.

MariaDB should either automatically find the databases in /var/lib/mysql or at least report errors in case of corrupted databases or tables. If it does not do that, there is a chance that it was attempted to change the path of the database directory. Do you have any entries like "datadir=" in either /etc/my.cnf or the included directories? Or was the formerly correct path /var/lib/mysql renamed to /var/lib/mysql1687372073? In that case it would help to
1) Stop the mariadb service
# service mariadb stop
2) Rename /var/lib/mysql1687372073 to /var/lib/mysql
# mv /var/lib/mysql1687372073 /var/lib/mysql
3) Start mariadb service
# service mariadb start
Before you do these steps, please make sure that no /var/lib/mysql directory exists and that no datadir entry in /etc/my.cnf or included directories exists. The default path of the data directory must not be changed in a Plesk installation, because it is hard coded in some places. So /var/lib/mysql would normally be the correct path.

 

[geekaboo]

Hi there again, Peter.

Thank you so much for your help, but I am afraid that it didn't work.

I followed all the steps you told me to, but I miserably failed.

Anyway, it is 3:15 am in here, so I guess I'll take a nap and will comeback later to see if I can fix it someway.

Thank you once again, Peter!

Have a great day!

 

[Peter Debik]

Yeah, sometimes at night it is best to sleep over it. If you don't get any further, maybe it is best to turn to official Plesk support so that they can check the issue and solve it directly on your server. https://support.plesk.com

 

[geekaboo]

Yeah, sometimes at night it is best to sleep over it. If you don't get any further, maybe it is best to turn to official Plesk support so that they can check the issue and solve it directly on your server. https://support.plesk.com

I followed your advice and finally subscribed to official Plesk support. The best decision I could have made. They solved everything in less than 30 minutes.

Thank you very much again!

 

[Peter Debik]

You are welcome. Indeed, Plesk ticket support is an excellent choice. Many on here hesitate to use it, but those who do receive world class service and get their server issues solved. Thank you for sharing your experience.

 

[Jan Bludau]

This Support Page is helping @unkown database psa.

Unable to access Plesk: Unknown database ‘psa’ - Support Cases from Plesk Knowledge Base

cd /var/lib/psa/dumps
zcat mysql.daily.dump.0.gz | sed -n '/-- Current Database: `psa`/,/-- Current Database:*/p' | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin

But the instructions are not complete. You have to / I had to run plesk repair installation in the Secure Shell (SSH) so that the installation worked again.