Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012

    Unanswered: Innodb Table Maintenance/Recovery

    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?


  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts