How to repair MyISAM tables in MySQL 5.0

How to repair MyISAM tables in MySQL 5.0

<!– @page { margin: 2cm } P { margin-bottom: 0.21cm } –>

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.

 

Resolution:

To resolve this erroneous situation, you should take the following precautions:

You should stop the mysqld process using mysqladmin. Then, run myisamchk –silent –force */* .MYI from the data directory. This would check all tables and restart mysqld.

You should start the mysqld process by enabling the query log. By doing this, you can check the details from the log whether some particular query is aborting the server.

You should check that the operating system you are using has the latest updates installed. This would ensure that the issue is not because of any bug in the operating system.

 

Using these methods will be able to resolve the situation. However, if the problem is still persisting then the table has corrupted. In such cases, you should use a third-party MySQL repair software to repair MySQL file. Such tools are read-only in nature that do not overwrite the existing database while recovering

 

Stellar Phoenix Database Recovery for MySQL is a MySQL recovery software that is able to repair MySQL database from all instances of logical corruption. It is designed to repair MySQL files created in InnoDB and MyISAM database engines. This MySQL repair utility is capable of recovering various database objects such as tables, views, etc. It is compatible with Windows 7, Vista, Server 2003, XP, and 2000.

My name is Mark Willium, I have done Ph.D in computer science and currently doing research on how to repair mysql and restore corrupted/damaged MySQL database.

Article from articlesbase.com

No related content found.

Revisions

There are no revisions for this post.

Tags: , , , ,

No comments yet.

Leave a Reply