3

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

2 Answers 2

3

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.

0

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/

1
  • Thanks. Have tried upping the recovery level as suggested, still not happy. Now server has died entirely so I'm feeling fairly convinced this is all the result of an underlying hardware issue at the hosting company :(
    – Paolo
    Oct 25, 2011 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.