Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

MySQL: Repairing broken tables/indices

DZone's Guide to

MySQL: Repairing broken tables/indices

· Performance Zone
Free Resource

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

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



Learn tips and best practices for optimizing your capacity management strategy with the Market Guide for Capacity Management, brought to you in partnership with BMC.

Topics:

Published at DZone with permission of Mark Needham, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}