MySQL

Here are useful tips I found when dealing with MySQL servers.

Repairing crashed tables

Sometimes a MySQL utility will report an error such as:

Got error: 145: Table '<tablename>' is marked as crashed and should be 
repaired when using LOCK TABLES

You can check the index files using the myisamchk utility,

cd /var/lib/mysql/<dbname>
myisamchk -e *.MYI

which should show an output similar to:

Checking MyISAM file: <tablename>.MYI
Data records: 130 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check records and index references

MyISAM-table '<tablename>.MYI' is usable but should be fixed

No errors reported

In this case, the table is fine, but just marked as crashed. Make sure the MySQL server is stopped

/etc/init.d/mysql stop

and then use myisamchk to mark the table is ok.

myisamchk --update-state <tablename>.MYI

You can then restart MySQL

/etc/init.d/mysql start

If the index file is actually corrupt, but the data is ok, the index file can be rebuilt:

myisamchk -r -q <tablename>

See also

Full details are available on the MySQL website: 5.6.3. How to Repair MyISAM Tables

Article last modified on January 26, 2014 at 3:45 pm.

Leave a Reply

Your email address will not be published. Required fields are marked *