This article applies to Plesk on Linux and involves working on the server over SSH with root privileges. If you do not have root access, or would rather not do this yourself, open a ticket from your client area.
Symptoms
Errors of the following kinds appear in Plesk:
ERROR: PleskMainDBException MySQL query failed: Incorrect information in file: './psa/misc.frm' ERROR: PleskDBException: Unable to connect to database: No such file or directory /var/run/mysqld/mysqld.sock (Error code: 2002)
The MySQL service does not start, tables cannot be queried, and repair does not help because InnoDB does not support it:
ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm' | psa.APSApplicationItems | repair | note | The storage engine for the table doesn't support repair |
The MySQL error log contains messages such as:
InnoDB: Cannot start InnoDB. The tail of the system tablespace is missing.
InnoDB: You can set innodb_force_recovery=1 in my.cnf to force a startup
InnoDB: Assertion failure in thread ... We intentionally generate a memory trap.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace
but not the InnoDB log files.
Cause
Most InnoDB corruption is caused by the underlying storage: a power failure, faulty memory, or a disk that filled up completely while the database was writing. Keeping InnoDB databases on network storage is another common cause, and is not recommended.
Consider restoring from a backup first
If you have a recent backup, restoring it is usually faster and always safer than repairing a corrupted tablespace — see Restoring a backup in Plesk. The procedure below is for cases where there is no usable backup, or where the data written since the last one must be recovered.
1. Copy the data directory before touching anything
Stop MySQL and take a copy of the raw data files. Every later step can make things worse; this copy is what allows a second attempt.
# systemctl stop mariadb # or mysqld / mysql # cp -a /var/lib/mysql /var/lib/mysql.broken
Check that there is enough free disk space first — df -h /var/lib. If the disk is full, that may itself be the cause, and freeing space is the first thing to do.
2. Start MySQL in recovery mode and dump the data
Add the following to the [mysqld] section of the configuration file (/etc/my.cnf or /etc/mysql/my.cnf, depending on the distribution):
innodb_force_recovery = 1
Start the service and, as soon as it comes up, dump everything:
# systemctl start mariadb # mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` --all-databases > /root/all-databases.sql
If the service still does not start, increase the value step by step — 2, then 3, then 4 — restarting after each change, and stop as soon as you can take a dump.
Do not go above 4 unless nothing else works. Levels 5 and 6 discard transactions and can leave the data permanently inconsistent. In recovery mode the database must be treated as read-only: do not resume normal work on it, only take the dump.
3. Rebuild the tablespace and restore
Once you have a dump you trust, stop MySQL, remove the system tablespace and log files, and start with a clean tablespace:
# systemctl stop mariadb # cd /var/lib/mysql # mv ibdata1 ib_logfile0 ib_logfile1 /var/lib/mysql.broken/
Remove the innodb_force_recovery line from the configuration file, start the service, and import the dump:
# systemctl start mariadb # mysql -uadmin -p`cat /etc/psa/.psa.shadow` < /root/all-databases.sql
4. Check Plesk
Log in to Plesk and check that it works and that the domains are listed. If the psa database itself was damaged, Plesk keeps daily dumps of it — look in /var/lib/psa/dumps/ for files named mysql.daily.dump.*.gz and restore the most recent one.
Then check that the sites open and that their databases respond.
Preventing a repeat
InnoDB corruption is almost always a symptom of something else. Keep an eye on free disk space, since a full disk during a write is the most common trigger. Make sure backups run and are stored off the server — see Backing up your account and websites in Plesk. If the corruption recurs on the same server, the hardware itself should be checked: open a ticket and we will do that.
If you are unsure at any point, stop and open a ticket from your client area, attaching the MySQL error log. Recovery attempts made in the wrong order are the usual reason data ends up unrecoverable.
