Unable to restore the Plesk psa database: ERROR 1813 (HY000) at line 104: Tablespace for table '`psa`.`tablename`' exists. Please DISCARD the tablespace before IMPORT

Symptoms

• Unable to restore the psa database:

  # ERROR 1813 (HY000) at line 151: Tablespace for table 'psa.APSLicenses' exists. Please DISCARD the tablespace before IMPORT

• Unable to create a dump of the psa database:

  # mysqldump: Got error: 1932: "Table 'psa.APSLicenses' doesn't exist in engine" when using LOCK TABLES

Cause

The psa database is corrupted.

Resolution

1. Connect to the Plesk server via SSH.

2. Create a backup of the files stored in /var/lib/mysql/psa:

   # mkdir /root/varlibmysqlpsa/

   # cp -a /var/lib/mysql/psa/* /root/varlibmysqlpsa

3. Remove the files:

   # rm -rf /var/lib/mysql/psa/*

4. Remove the psa database:

   # plesk db "DROP DATABASE psa"

5. Connect to MySQL and create an empty psa database:

   # MYSQL_PWD=$(cat /etc/psa/.psa.shadow) mysql -uadmin

   MYSQL_LIN: MariaDB [(none)]> create database psa;
   
   Query OK, 1 row affected (0.002 sec)

   MYSQL_LIN: MariaDB [(none)]> quit

6. Restore the psa database from the latest daily backup:

   # 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 --default-character-set=utf8