Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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/

share|improve this answer
    
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 '11 at 9:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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