Thursday, December 23, 2010

How to repair MyISAM tables in MySQL 5.0

MyISAM is the default storage engine in MySQL server. It consists of three types of files: .myi, .frm, and .myd. Of these three, the MYI file is the index file that stores a counter in its header. This header is used to signify whether a particular table was closed properly or not. Sometimes, while using the myisamchk command if you get an error message that means the table has corrupted. Such problems can occur due to various reasons such as power outages, abrupt system shutdown, mysqld process getting killed in between a write operation, etc. You should use in-built workarounds to repair the table. If you are not able to do this, then you should use a third-party MySQL repair software to recover MySQL database.

Consider a scenario wherein you have installed MySQL 5.0 in your system. One day, when you try to run the myisamcheck command to check a database table, it fails to run. An error message is displayed, that is:

“clients are using or haven't closed the table properly”

Cause:

The root cause of this error could be either of the following reasons:

You copied a MyISAM table without using the Lock Table or Flush Tables command.
MySQL crashed while you were about to commit a change in the database table.
Either myisamchk --recover or myisamchk –update-state updated a table while it is used by the mysqld process.
Many mysqld servers are referring to the same table at the same time and one of them performed Repair Table or Check Table command.

For details how to overcome from above error click here

No comments:

Post a Comment