How to Fix InnoDB Tablespace Corruption in MySQL

The goal is to be able to successfully export your databases into .sql files. Once this has been completed you will be able to remove the databases and import your .sql file to get back to normal.

Symptoms:

When attempting to restart MySQL you receive a message similar to:

Starting MySQL..The server quit without updating PID file (/var/lib/mysql/server.domain.com.pid).[FAILED] or MySQL server PID file could not be found!

Checking the MySQL error log reveals something like:

129096 13:45:01 mysqld_safe Number of processes running now: 0
 129096 13:45:01 mysqld_safe mysqld restarted
 129096 13:45:01 [Note] Plugin 'FEDERATED' is disabled.
 129096 13:45:01 InnoDB: The InnoDB memory heap is disabled
 129096 13:45:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
 129096 13:45:01 InnoDB: Compressed tables use zlib 1.2.3
 129096 13:45:01 InnoDB: Using Linux native AIO
 129096 13:45:01 InnoDB: Initializing buffer pool, size = 128.0M
 129096 13:45:01 InnoDB: Completed initialization of buffer pool
 129096 13:45:01 InnoDB: highest supported file format is Barracuda.
 129096 13:45:01 InnoDB: 5.5.30 started; log sequence number 1584752745
 129096 13:45:01 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
 129096 13:45:01 [Note] - '0.0.0.0' resolves to '0.0.0.0';
 129096 13:45:01 [Note] Server socket created on IP: '0.0.0.0'.
 129096 13:45:01 [Note] Event Scheduler: Loaded 0 events
 129096 13:45:01 [Note] /usr/sbin/mysqld: ready for connections.
 Version: '5.5.30-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
 129096 13:45:02 InnoDB: Assertion failure in thread 47204348393792 in file trx0purge.c line 840
 InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
 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.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
 18:45:02 UTC - mysqld got signal 6 ;
 This could be because you hit a bug. It is also possible that this binary
 or one of the libraries it was linked against is corrupt, improperly built,
 or misconfigured. This error can also be caused by malfunctioning hardware.

This is typical of InnoDB corruption. To recover from this, perform the following steps:

MySQL will already be stopped but let’s make sure.

# service mysqld stop

Back up your current MySQL data directory

# cp -r /var/lib/mysql/* /root/mysql-recovery

Add innodb_force_recovery=1 in the my.cnf file. Make sure this option is added in the [mysqld] section of my.cnf. In CentOS, this is file is usually located in /etc/my.cnf. For Debian and related distributions, it will typically be found in /etc/mysql/my.cnf. Once you have made the modification to your my.cnf file, attempt to start MySQL.

innodb_force_recovery may be set as high as 6 but we should attempt recovery with 1 first. I don’t recall this ever working until I get to at least 4 but you should still start with 1. Corruption becomes possible at 4 so it is worth trying 1-3 first. There is a lot more detailed information in MySQL’s innodb_force_recovery documentation should you want more in-depth information.

If you have not done so already, attempt to restart MySQL.

# server mysqld start

If MySQL started successfully, dump all databases to a file:

# mysqldump -uroot -p -A | sed '1i\SET FOREIGN_KEY_CHECKS = 0;' > /root/mysql-dump.sql

If you are running Plesk, databases may be dumped with the following command:

# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` -A | sed '1i\SET FOREIGN_KEY_CHECKS = 0;' > /root/mysql-dump.sql

In the event the dump fails with an error such as:

Incorrect information in file: './database/table.frm' when using LOCK TABLES"`

You will need to increase innodb_force_recovery in your my.cnf file, restart MySQL and try again.

Once the dump has been completed, stop MySQL

# service mysqld stop

Now delete your MySQL data files:

# rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`

Restart MySQL:

# service mysqld restart

Import the databases back into MySQL:

# mysql -uroot -p < /root/mysql-dump.sql

If running Plesk, all databases may be imported with the following command:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` < /root/mysql-dump.sql

Assuming the import went ok, you can remove the innodb_force_recovery line from your my.cnf file and restart MySQL.

# service mysqld restart