I recently was dropping an index from a large MySQL InnoDB table. The MySQL server crashed and when I started the server back up, it wouldn't start. The error log indicated that I needed to force InnoDB Recovery.
I set innodb_force_recovery to 4, restarted mysql, dumped my tables, and then re-loaded them into the database. However, I would like to know what I need to do to avoid this problem in the future. Is there an optimize or check statement that I should be running to detect innodb table failures? There was no hardware failure, power, or OS disruptions at the time of the failure. The only unusual activity was dropping the index.
How can I avoid encountering this error in the future? Or is there a better way to fix it?
If this was purely a MySQL crash then this could have resulted in the issues you have described. Consistency of the data and data files are performed by the software but if this crashes (especially at a critical point) then it could leave the data corrupted. I do not believe that any change to file system type (one with journaling) will have avoided this problem either. If anything I would suggest looking at the version of MySQL and attempt to upgrade to the latest version. If you built the MySQL server rather than using binaries I would suggest using the binaries as the underlying libraries used in the binary versions during compilation might be more robust than the ones on your OS.