Articles in this section

See more

How to fix InnoDB corruption cases for the MySQL databases on Plesk for Linux?

Avatar
Leonid Gukhman
Updated
Follow

Applicable to:

  • Plesk for Linux

Symptoms

  • The following error is shown in Plesk:

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

    OR

    PLESK_INFO: ERROR: PleskDBException: Unable to connect to database: mysql_connect(): No such file or directory /var/run/mysqld/mysqld.sock (Error code: 2002). Please check that database server is started and accessible. (Abstract.php:69)

    OR

    PLESK_INFO: ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [2002] No such file or directory

    OR

    PLESK_INFO: ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [2002] Connection refused:
    0: /usr/local/psa/admin/externals/Zend/Db/Adapter/Pdo/Abstract.php:144

  • Domain overview page on Domains > example.com is not accessible:

    CONFIG_TEXT: 500 Server Error
    Type SB_Facade_Exception_Generic
    Message
    File generic.php
    Line 33

  • Plesk upgrade fails with the following error:

    PLESK_INFO: DATABASE ERROR!!!
    Database psa database found, but version undefined

  • MySQL service does not start:

    # service mysqld start
    Timeout error occurred trying to start MySQL Daemon.
    Starting MySQL: [FAILED]

  • mysqldump and mysqlcheck utilities 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)

  • A table cannot be properly queried with the SELECT statement:

    MYSQL_LIN: 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 repair queries:

    # 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 /var/log/mysql/error.log, /var/log/mysqld.log, or /var/log/mariadb/mariadb.log:

    CONFIG_TEXT: 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.

    Or

    CONFIG_TEXT: InnoDB: Assertion failure in thread 3876 in file ha_innodb.cc line 17352
    InnoDB: We intentionally generate a memory trap.
    InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
    InnoDB: If you get repeated assertion failures or crashes, even
    InnoDB: immediately after the mysqld startup, there may be
    InnoDB: corruption in the InnoDB tablespace. Please refer to
    InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
    InnoDB: about forcing recovery.

    Or

    CONFIG_TEXT: InnoDB: Assertion failure in thread 140154354255616 in file trx0purge.c line 848
    InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
    InnoDB: We intentionally generate a memory trap.

    OR

    CONFIG_TEXT: InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery

    OR

    CONFIG_TEXT: [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace database/table uses space ID: 882 at filepath

    OR

    CONFIG_TEXT: [Note] InnoDB: Starting crash recovery.
    [ERROR] InnoDB: Tablespace 11904 was not found at ./example_db/example1.ibd.
    [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
    [ERROR] InnoDB: Tablespace 11905 was not found at ./example_db/example2.ibd.
    [ERROR] InnoDB: Cannot continue operation.

    OR

    CONFIG_TEXT: InnoDB: Error: space header page consists of zero bytes in data file ./ibdata1

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 article for additional information regarding MySQL on different operating systems
[Linux] Local MySQL server for all databases
For additional information check your operating system and MySQL documentation.

Note: for Windows, see the following article: How to fix InnoDB corruption cases for the MySQL databases on Plesk for Windows?

There are several ways to recover a failed MySQL database:

Firstly, connect to the server using SSH.

I. Force InnoDB Recovery

  1. Stop the affected MySQL service:

    # service mysql stop

  2. Back up all the MySQL data storage files. By default, they are located in /var/lib/mysql/

    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 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 = 4

  4. Start the MySQL service.

    • If the service fails to start, set the parameter innodb_force_recovery to greater value and try starting MySQL again.
      Value of the parameter innodb_force_recovery can be from 1 to 6.

      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.

    • If the service fails to start with an error like:

      CONFIG_TEXT: InnoDB: Waiting for the background threads to start

      Add directive innodb_purge_threads according to the following article: Unable to start MySQL service: InnoDB: Waiting for the background threads to start

  5. Try to dump all databases:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -Ns -uadmin psa -Ne"show databases"|grep -v information_schema | grep -v performance_schema > /root/db_list.txt
    # mkdir /root/db_backup/
    # cat /root/db_list.txt | while read i; do MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin "$i" --routines --databases > /root/db_backup/"$i".sql; echo $i; sleep 5; done

    • If the dump fails with an error like:

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

    then increase innodb_force_recovery value, restart MySQL service, and try to dump the databases again. It is better to dump databases one by one, separately. In that case, there is no need to go through restore of all databases once again if restore failed for some reason.
    If unable 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:

    # service mysqld restart

  9. Check the MySQL log file for any errors.

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

    # for db in `cat /root/db_list.txt`; do echo -e "Importing $db..."; MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin < /root/db_backup/$db.sql; done

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_LIN: CREATE TABLE <new_table> LIKE <crashed_table>;
    MYSQL_LIN: INSERT INTO <new_table> SELECT * FROM <crashed_table>;

  3. If the copy was created successfully, then replace corrupted table with newly created:

    MYSQL_LIN: RENAME TABLE <crashed_table> TO <old_table>;
    MYSQL_LIN: RENAME TABLE <new_table> TO <crashed_table>;
    MYSQL_LIN: DROP TABLE <old_table>;

    Note: Depending on the MySQL version used, 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 How to backup/restore a Plesk database dump article. For example:

    # ls -tl /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: If timeouts are encountered when restoring databases, 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

Comments

6 comments

Sort by Date Votes
  • Avatar
    Pedro Telmo

    Hi:

    What information must content the file /root/db_list.txt ?

    Regards

    0
    Comment actions Permalink
  • Avatar
    Mskumar Blaze

    2018-02-21T14:59:14.908905Z 12 [ERROR] Plugin group_replication reported: 'Fatal error during the Recovery process of Group Replication. The server will leave the group.'

    0
    Comment actions Permalink
  • Avatar
    Mskumar Blaze

    I have some doubt I have configured innodb cluster (3 cluster) after I created I’ll be going to stop group replication in (cluster2) then I had to rejoin cluster2 after I rejoined cluster2 status as recovering after some times it’s going to show missing. My cluster1-(primary) log status Warning] Plugin group_replication reported: 'Members removed from the group: 608781bce352:3306'
    2018-02-21T14:59:16.115814Z 0 [Note] Plugin group_replication reported: 'Group membership changed to d5e07af19ef5:3306, 812606eb6abb:3306 on view 15191888786747824:12.'
    2018-02-21T15:00:13.434470Z 148 [Note] Aborted connection 148 to db: 'unconnected' user: 'mysql_innodb_cluster_r1721619408' host: 'cluster2.willing' (failed on flush_net())
    2018-02-21T15:16:48.046190Z 141 [Note] Aborted connection 141 to db: 'unconnected' user: 'root' host: 'd5e07af19ef5' (Got an error reading communication packets)

    My cluster2 log status Warning] Plugin group_replication reported: 'Members removed from the group: 608781bce352:3306'
    2018-02-21T14:59:16.115814Z 0 [Note] Plugin group_replication reported: 'Group membership changed to d5e07af19ef5:3306, 812606eb6abb:3306 on view 15191888786747824:12.'
    2018-02-21T15:00:13.434470Z 148 [Note] Aborted connection 148 to db: 'unconnected' user: 'mysql_innodb_cluster_r1721619408' host: 'cluster2.willing' (failed on flush_net())
    2018-02-21T15:16:48.046190Z 141 [Note] Aborted connection 141 to db: 'unconnected' user: 'root' host: 'd5e07af19ef5' (Got an error reading communication packets). I got confused kindly help me please...

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    @Mskumar Blaze

    Hello!

    I would suggest contacting MySQL Technical Support regarding this issue as they are experts in their product.

    0
    Comment actions Permalink
  • Avatar
    Artyom Baranov

    @Pedro Telmo,

    Hi! After executing the following command:

    # mysql -Ns -uadmin -p`cat /etc/psa/.psa.shadow` -e"SHOW DATABASES" > db_list.txt

    it should contain the names of all databases that are stored on MySQL server.

    0
    Comment actions Permalink
  • Avatar
    Pedro Telmo (Edited )

    Yes Artyom.

    Alisa Kasyanova add this line after my comment :)

    Thanks

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles