Over a million developers have joined DZone.

MySQL: Repairing broken tables/indices

· Performance Zone

See Gartner’s latest research on the application performance monitoring landscape and how APM suites are becoming more and more critical to the business, brought to you in partnership with AppDynamics.

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



The Performance Zone is brought to you in partnership with AppDynamics.  See Gartner’s latest research on the application performance monitoring landscape and how APM suites are becoming more and more critical to the business.

Topics:

Published at DZone with permission of Mark Needham , DZone MVB .

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}