Plesk Server issues: [How to] How to fix InnoDB corruption cases for the MySQL database

The following error is shown in Plesk:

ERROR: PleskMainDBException
MySQL query failed: Incorrect information in file: './psa/misc.frm'

Plesk upgrade fails with the following error:

DATABASE ERROR!!!
Database psa found, but version undefined

The MySQL service does not start:

/etc/init.d/mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]

mysqldump and mysqlcheckfail with an error message claiming a table does not exist (use the MySQL administrator account to check):
```
mysqlcheck -uadmin -p****** db_example

db_example.BackupTasks
error    : Can't find file: 'BackupTasks.MYD' (errno: 2)
```

A table cannot be properly queried with the SELECTstatement:

mysql> select * from db_example.misc;
ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm'

The table cannot be repaired because the InnoDB engine does not support reparation.

mysql> repair table misc;
+-------------------------+--------+----------+---------------------------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                                                |
+-------------------------+--------+----------+---------------------------------------------------------+
| psa.APSApplicationItems | repair | note     | The storage engine for the table doesn't support repair |
+-------------------------+--------+----------+---------------------------------------------------------+

The following information can be found in the MySQL log file:

150704 19:09:27 InnoDB: Waiting for the background threads to start
150704 19:09:28 InnoDB: Error: tablespace size stored in header is 3712 pages, but
150704 19:09:28 InnoDB: the sum of data file sizes is only 3072 pages
150704 19:09:28 InnoDB: Cannot start InnoDB. The tail of the system tablespace is
150704 19:09:28 InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an
150704 19:09:28 InnoDB: inappropriate way, removing ibdata files from there?
150704 19:09:28 InnoDB: You can set innodb_force_recovery=1 in my.cnf to force
150704 19:09:28 InnoDB: a startup if you are trying to recover a badly corrupt database.

Cause

InnoDB corruption.

Most InnoDB corruptions are hardware-related. Corrupted page writes can be caused by power failures or bad memory. The issue also can be caused by using network attached storage (NAS) and allocating InnoDB databases on it.

Resolution

Note: Since the MySQL service's control, logs and configuration file's location is different on the different operating systems, this article provides general command examples only.

I. Force InnoDB Recovery

Stop the affected MySQL service. See the note above regarding service's control.
Back up all the MySQL data storage files. They are located in the following directories:

/var/lib/mysql/ - all databases on Linux

%plesk_dir%\Databases\MySQL\Data\- customers' databases on Windows

%plesk_dir%\MySQL\Data\ - Plesk-related databases (psa, apsc, horde) on Windows.

For example:
```
# mkdir /root/mysql_backup
# cp -a /var/lib/mysql/* /root/mysql_backup/
```
Set theinnodb_force_recoveryvalue under the [mysqld]section ([PleskSQLServer]for Plesk SQL Server on Windows) in the MySQL configuration file. This option will allow you to start MySQL service and create all databases dump.

For example:
```
# vi /etc/my.cnf
[mysqld]
innodb_force_recovery = 1
```
WARNING: Only setinnodb_force_recovery to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. Values of 4 or greater can permanently corrupt data files. Therefore, increase this value incrementally, as necessary. Please see more details in the official MySQL Documentation.
Start the MySQL service.
Try to dump all databases:
```
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin -A | sed '1i\SET FOREIGN_KEY_CHECKS = 0;' > /root/dumpall.sql
```
- If the dump fails with an error that reads:
```
Incorrect information in file: './psa/APSApplicationItems.frm' when using LOCK TABLES"`
```
  then you need to increaseinnodb_force_recoveryand try to dump the databases again.
- If you are not able to dump the databases, then try using method II ("Copy table content") or III ("Restore from the backup") below.
Remove all the MySQL data storage files except the mysqlfolder. For example:
```
# rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`
```
Remove theinnodb_force_recoveryoption from the MySQL configuration file.
Restart the MySQL service.
Check the MySQL log file for any errors.

Restore the databases from the dump made on the step #5 above. For example:

# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin < /root/dumpall.sql

II. Copy table contents

Repeat steps #1-4 from themethod I to back up all the MySQL data storage files and enable InnoDB recovery mode.

Try to make a copy of a table:

CREATE TABLE <new_table> LIKE <crashed_table>;
INSERT INTO <new_table> SELECT * FROM <crashed_table>;

If the copy was created successfully, then drop the corrupted table and rename the new table to the old one.
```
DROP TABLE <crashed_table>;
RENAME TABLE <new_table> TO <crashed_table>;
```
Note: Depending on your MySQL version it might be necessary to set lowerinnodb_force_recoveryvalue or remove it from the MySQL configuration file and restart MySQL service to successfully perform the DROPand RENAME operations. Please see more details in the official MySQL Documentation.

III. Restore from a backup

If the instructions above did not help, the only remaining method is to restore the databases from backups. Do not forget to remove theinnodb_force_recovery option from the MySQL configuration file before restore.

To restore Plesk-related databases (psa, apsc, horde) see the KB #881. For example:

# ls -l /var/lib/psa/dumps
-rw------- 1 root   root   141960 Aug  8 01:03 mysql.daily.dump.0.gz
-rw------- 1 root   root   141925 Aug  7 01:03 mysql.daily.dump.1.gz

# zcat /var/lib/psa/dumps/mysql.daily.dump.0.gz | MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa

To restore customer's databases from Plesk backup see the Restoring Data from Backup Archives section in the Administrator's Guide.

Note: You may encounter timeouts when restoring databases. If so, set the wait_timeout value in the MySQL configuration file and restart the MySQL service. For example:

# vi /etc/my.cnf
[mysqld]
wait_timeout = 1800

Thursday, August 4, 2016

[How to] How to fix InnoDB corruption cases for the MySQL database

Cause

Resolution

I. Force InnoDB Recovery

II. Copy table contents

III. Restore from a backup