MySQL corruption even after full dump and re-import

Question

We have a production issue where MySQL has started spitting InnoDB corrupt page errors and falling over.

The error message seem to relate to indexes so I assume the data itself is ok and when I restarted the server with innodb_force_recovery=1 I was able to dump the lot using mysqldump.

However, re-importing hasn't fixed the error, dropping/recreating the database didn't help either and I've even gone as far as moving the innodb files out the way and letting MySQL recreate them before re-importing.

No joy, corruptions keep re-occuring within a few minutes and the server dies again.

The weird thing is the database is a mix of ISAM and InnoDB tables and I had a couple of errors in the ISAM tables as well on some attempts.

Is this likely to be something data-related, something corrupt deeper in MySQL that isn't cleared by the above or signs of a hardware fault underneath it all?

EDIT: This is Mysql 5.0.51 running on Ubuntu 8.04

Answer 1

Paolo, can you please provide the mysql error log? If one isn't available, you can add one by adding this line to your my.cnf file

[mysqld]

log-error=/var/log/mysql/mysql.err

One possible idea to narrow down your research.

You already know how to remove the ibdata and iblog files to create new ones. If you just want to test your mysql installation.

1. Remove the Innodb_force_recovery or comment it out
2. Backup your databases : mysqldump --all-databases -u root -p | gzip -9 > all_databases.sql.gz
3. Move your MySQL data directory somewhere else: mv /var/lib/mysql{,.bak}
4. Create a new MySQL data directory: mkdir /var/lib/mysql
5. Give rights to the MySQL user: chown -R mysql:mysql /var/lib/mysql
6. Start MySQL: /etc/init.d/mysql start
7. Run a tail -f on the mysql log you added to my.cnf: tail -f /var/log/mysql/mysql.err

If MySQL isn't running then something else on the system level isn't right. As Gopinath's link mentionned. Is there enough disk space: df -h and inodes: df -i? Is you file system healthy? A system reboot or using fsck on the disk partition where MySQL is installed, would detect and possibly fix file system problems.

If MySQL comes back, then you can restore your databases:

zcat all_databases.sql.gz | mysql -u root -p

Send us the logs after completing the steps I sent, and we'll take it from there.

Answer 2

Hi Paolo might be issue with the innodb_force_recovery=1 which you has used in force recovery. try using value 4. There might be some back ground process running. With innodb_force_recovery=4 value set to 4, No background process is allowed to modify the database so that we can safely do the recovery process.

Please follow the below link for in detail :

http://tech.amikelive.com/node-36/mysql-50-recovering-crashedcorrupted-innodb-database/