Performance Zone is brought to you in partnership with:

Mark is a graph advocate and field engineer for Neo Technology, the company behind the Neo4j graph database. As a field engineer, Mark helps customers embrace graph data and Neo4j building sophisticated solutions to challenging data problems. When he's not with customers Mark is a developer on Neo4j and writes his experiences of being a graphista on a popular blog at http://markhneedham.com/blog. He tweets at @markhneedham. Mark is a DZone MVB and is not an employee of DZone and has posted 543 posts at DZone. You can read more from them at their website. View Full User Profile

MySQL: Repairing broken tables/indices

04.09.2013
| 1954 views |
  • submit to reddit

I part time administrate a football forum that I used to run when I was at university and one problem we had recently was that some of the tables/indices had got corrupted when MySQL crashed due to a lack of disc space.

We weren’t seeing any visible sign of a problem in any of the logs but whenever you tried to query one of the topics it wasn’t returning any posts.

I eventually came across a useful article which explained how to check whether some of the tables in a MySQL database had been corrupted and how to fix them.

I first shutdown the database using the following command:

mysqladmin shutdown

And then I ran this command to check on the status of each of the tables:

for path in `ls /var/lib/mysql/forum/*.MYI`; do echo $path; myisamchk $path; done

This gave an output like the following for each table:

Checking MyISAM file: /var/lib/mysql/forum/forum.MYI
Data records:     217   Deleted blocks:       4
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
MyISAM-table '/var/lib/mysql/forum/forum.MYI' is usable but should be fixed

If you pass the ‘–recover’ flag to myisamchk it will attempt to fix any problems it finds. I therefore ran the following command:

for path in `ls /var/lib/mysql/forum/*.MYI`; do echo $path; myisamchk --recover $path; done

After I’d run that it seemed to fix most of the problems we’d been experiencing. There are still a couple of edge cases left but at least the majority of the forum is now in a usable state.

I think we could just as easily run myisamchk by passing a wildcard selection of files for it to run against but I didn’t realise that until afterwards!

The following would therefore work just as well:

myisamchk --recover /var/lib/mysql/forum/*.MYI



Published at DZone with permission of Mark Needham, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)