Attempted MariaDB database server upgrade results in Plesk GUI inaccessibility: DB query failed: SQLSTATE[HY000] [2002] No such file or directory

Symptoms

• Plesk Obsidian running on a RHEL-based operating system

• Unable to log into the Plesk GUI with the following error:

  Server Error
  500
  PleskExceptionDatabase
  DB query failed: SQLSTATE[HY000] [2002] No such file or directory: 0: /usr/local/psa/admin/plib/Db/Adapter/Pdo/Mysql.php:60

• The MariaDB database server service cannot be started after an attempt to upgrade it to a newer version:

  # systemctl status mysqld.service
  Unit mysqld.service could not be found.

  # systemctl status mariadb
  Unit mariadb.service could not be found.

  # systemctl status mysql.service
  Unit mysql.service could not be found.

Cause

The update or upgrade for MariaDB failed and did not fully install the latest MariaDB version, due to which the current database server installation is corrupted.

Resolution

1. Connect to a Plesk server via SSH

2. Create a backup of the following Plesk databases:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin psa > /root/psa_backup.sql
   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin apsc > /root/apsc_backup.sql

3. Backup the MySQL configuration file my.cnf by executing the following command:

   # cp /etc/my.cnf{,_original}

4. Backup the directory /var/lib/mysql:

   # cp -a /var/lib/mysql{,_original}

5. Enable the official MariaDB repository (instructions are available here), but do not install it.

6. Remove the mysql-server package:

   # rpm -e --nodeps `rpm -q --whatprovides mysql-server`

7. Remove all leftovers and install MariaDB packages in one transaction:

   # yum shell
   > remove mysql mysql-server plesk-mysql
   > install MariaDB-server MariaDB-client MariaDB-compat MariaDB-shared
   > run
   > exit

8. Start MySQL:

   # service mysql start

9. Upgrade the MySQL installation:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin

10. If Plesk is inaccessible after the database server upgrade, restore the Plesk system databases by executing the following commands:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin psa < /root/psa_backup.sql
    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin apsc < /root/apsc_backup.sql