Managing & Using MySQL, 2nd Edition

Checking a Table

If you suspect errors on a table, the first thing you should do is use one of the utilities to check it out. You can tell what kind of table you are dealing with by looking at the extension of the data file. An extension of .MYI tells you it is a MyISAM table, and .ISM indicates an ISAM table. As we said earlier, myisamchk and mysqlcheck are used only with .MYI files, and isamchk with .ISM files.

Assume we have a database called test with two tables: table1, which is an ISAM table, and table2, which is a MyISAM table. First, check your table, using the appropriate utility. If you use myisamchk or isamchk, make sure your MySQL server is not running to prevent the server from writing to the file while you are reading it.

$ myisamchk table2.MYI 
Data records:       0   Deleted blocks:       0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
$ isamchk table1.ISM
Checking ISAM file: table1.ISM
Data records:       0   Deleted blocks:       0
- check file-size
- check delete-chain
- check index reference
$ mysqlcheck test table2
test.table2                                        OK

This output shows that there are no errors in either of the tables.

The default method is usually adequate for detecting errors. However, if no errors are reported but you still suspect damage, you can perform an extended check using the --extend-check option with myisamchk/isamchk or the --extend option with mysqlcheck. This will take a long time, but is very thorough. If the extended check does not report any errors, ...