My database table states “in use” how can I fix this?

QUESTIONS CENTRECategory: apacheMy database table states “in use” how can I fix this?
ZENHOST Support Staff asked 5 years ago

MyISAM is the default storage engine for MySQL database. MyISAM table gets corrupted very easily.

When a table is created under MySQL, it creates three different files: *.frm file to store table format, *.MYD (MyData) file to store the data, and *.MYI (MyIndex) to store the index. You should prefer to use InnoDB as the storage engine for bigger database, as it resembles Oracle and provides commit, rollback options.

From the error messages that you get, it is clear that your table is corrupted and needs to be reparied.

The corrupted table can be repaired using myisamchk as explained below.

Repair the corrupted table using myisamchk

Execute the myisamchk as shown below, with option to repair the corrupted tables identified with the problem.

 

Go to your database directory. Usually it is located on

/etc/var/lib/mysql/YOUR_DATABASE_NAME/

So use this command first
cd /etc/var/lib/mysql/YOUR_DATABASE_NAME/

Next, use this command
myisamchk --safe-recover --force YOUR_TABLE_NAME

And the MySQL table should be repaired now.

 

You may get error message: clients are using or haven’t closed the table properly, if the tables are still getting used by your application and other tables. To avoid this error message, shutdown mysqld before performing the repair, if you can afford to shutdown the DB for a while. If not, use FLUSH TABLES to force mysqld to flush any table modification that are still in memory.

Allocate additional memory for large MySQL database

For large database, it may take several hours to recover the tables. Depending on RAM available on your system, increase the memory parameters as shown below while executing the myisamchk.