How to fix InnoDB corruption cases for the MySQL database

Created:

2016-11-16 13:08:52 UTC

Modified:

2017-08-18 18:00:03 UTC

33

Was this article helpful?


Have more questions?

Submit a request

How to fix InnoDB corruption cases for the MySQL database

Applicable to:

  • Plesk

Symptoms

  1. The following error is shown in Plesk:

    ERROR: PleskMainDBException
    MySQL query failed: Incorrect information in file: './psa/misc.frm'
  2. Plesk upgrade fails with the following error:

    DATABASE ERROR!!!
    Database psa found, but version undefined
  3. The MySQL service does not start:

    # service mysqld start
    Timeout error occurred trying to start MySQL Daemon.
    Starting MySQL: [FAILED]
  4. mysqldump and mysqlcheck fail 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)
  5. A table cannot be properly queried with the mysql> SELECT statement:

    mysql> select * from db_example.misc;
    ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm'
  6. 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 |
    +-------------------------+--------+----------+---------------------------------------------------------+
  7. 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. Check the following articles for additional information regarding MySQL on different operating systems:

[Linux] Local MySQL server for all databases

[Windows] Local MySQL server for customers' databases

[Windows] Local MySQL server for Plesk-related databases (psa, apsc, horde)

For additional information check your operating system and MySQL documentation.

There are several ways to recover a failed MySQL database: 

I. Force InnoDB Recovery

  1. Stop the affected MySQL service. See the note above regarding service's control.

  2. 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/
  3. Set the innodb_force_recovery value 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 set innodb_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 .

  4. Start the MySQL service .

  5. Try to dump all databases:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin -A | sed '1i\\SET FOREIGN_KEY_CHECKS = 0;' > /root/all_db.sql
    • If the dump fails with an error like:

      Incorrect information in file: './psa/APSApplicationItems.frm' when using LOCK TABLES"`

      then you need to increase innodb_force_recovery and try to dump the databases again. It is better to dump databases one by one, separately. In that case, there is a no need to go through restore of all databases once again if restore failed for some reason.
      If you are not able to dump the databases, then try using method II ("Copy table content") or III ("Restore from the backup") which are described below.

  6. Remove all the MySQL data storage files except the mysql folder. For example:

    # rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`
  7. Remove the innodb_force_recovery option from the MySQL configuration file.

  8. Restart the MySQL service.

  9. Check the MySQL log file for any errors.

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

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

II. Copy table contents

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

  2. Try to make a copy of a table:

    mysql> CREATE TABLE <new_table> LIKE <crashed_table>;
    mysql> INSERT INTO <new_table> SELECT * FROM <crashed_table>;
  3. If the copy was created successfully, then replace corrupted table with newly created.

    mysql> RENAME TABLE <crashed_table> TO <old_table>;
    
    mysql> RENAME TABLE <new_table> TO <crashed_table>;
    mysql> DROP TABLE <old_table>;

    Note: Depending on your MySQL version it might be necessary to set lower innodb_force_recovery value or remove it from the MySQL configuration file and restart MySQL service to successfully perform the DROP and 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 the innodb_force_recovery option from the MySQL configuration file before restore.

  • To restore Plesk-related databases (psa, apsc, horde) see the How to backup/restore a Plesk database dump. 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
Have more questions? Submit a request
Please sign in to leave a comment.