Over a million developers have joined DZone.
Platinum Partner

MySQL: Repairing broken tables/indices

· Performance Zone

The Performance Zone is brought to you in partnership with New Relic. Quickly learn how to use Docker and containers in general to create packaged images for easy management, testing, and deployment of software.

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 New Relic. Read more about providing a framework that gets you started on the right path to move your IT services to cloud computing, and give you an understanding as to why certain applications should not move to the cloud.


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 }}