Checking and Repairing Database Tables

Database damage occurs for a number of reasons and varies in extent. If you're lucky, you may simply have minor damage to a table or two (for example, if your machine goes down briefly due to a power outage). In this case, it's likely that the server can repair the damage when it comes back up. If you're not so lucky, you may have to replace your entire data directory (for example, if a disk died and took your data directory with it). Recovery also is needed under other circumstances, such as when users mistakenly drop databases or tables or delete a table's contents. Whatever the reason for these unfortunate events, you'll need to perform some sort of recovery.

This section describes table checking and repair procedures you can use to deal with more minor forms of damage. If you suspect that a table has become corrupted, check it for errors. If the table checks okay, you're done. If not, try to repair it using these guidelines:

• Begin with a faster but less thorough repair method to see if it will correct the damage.

• If you find that it is not sufficient, escalate to more thorough (but slower) repair methods, until either the damage has been repaired or you cannot escalate further.

In practice, most problems can be fixed without going to more extensive and slower repair methods.

In the event that tables or databases are lost or irreparably damaged, you'll need to restore them from your database backups and binary logs. "Using Backups for Data Recovery" later in the chapter discusses how to do that.

A general outline of the alternatives available to you for checking and repairing MyISAM and InnoDB tables follows. Specific details are given after that.

To check and repair MyISAM tables, you have several options:

• Use the CHECK TABLE and REPAIR TABLE statements.

• Use the mysqlcheck program, which connects to the server and issues those statements for you.

• Use the myisamchk program, which operates on the table files directly.

As mentioned earlier in the chapter, if you have a choice when performing table maintenance between letting the server do the work or running an external utility, it's generally better to let the server do the work. Then you need not be concerned about using any locking protocols to coordinate table access. That advantage applies when using CHECK TABLE and REPAIR TABLE (or mysqlcheck). If you use myisamchk, you must ensure that the server does not use the tables while you're working on them. Nevertheless, you might decide to use myisamchk for the following reasons:

• You can use myisamchk when the server is stopped. CREATE TABLE and REPAIR TABLE require that the server be running.

• You can tell myisamchk to use larger buffers to make checking and repair operations run faster. This can be helpful if you have very large tables.

To check an InnoDB table, use CHECK TABLE or mysqlcheck. If the table is found to have problems, dump it with mysqldump. Then drop the table and reload the dump file to re-create it. The following sequence of commands shows how you might check, dump, and reload the absence table in the sampdb database:

% mysqlcheck sampdb absence
% mysqldump --opt sampdb absence > absence.sql
% mysql sampdb < absence.sql

The --opt option to mysqldump enables the --add-drop-table option, so the dump file contains a DROP TABLE statement to drop the table before re-creating it.

Using CHECK TABLE to Check Tables

The CHECK TABLE statement provides an interface to the server's table checking capabilities. It works for MyISAM and InnoDB tables.

To use CHECK TABLE, provide a list of one or more table names, optionally followed by modifiers that indicate what type of check to do. For example, the following statement performs a medium-level check on three tables, but only if they have not been properly closed:

CHECK TABLE tbl1, tbl2, tbl3 FAST MEDIUM;

CHECK TABLE supports the following check mode options:

• CHANGED

  Don't check a table if it was properly closed and has not been changed since the last time it was checked.

• EXTENDED

  Perform an extensive check. This is the most thorough check available, and consequently the slowest. It attempts to verify that the table's data rows and indexes are fully consistent.

• FAST

  Check a table only if it was not properly closed.

• MEDIUM

  Perform a medium-level check. This is the default if you specify no options.

• QUICK

  Perform a quick check that scans only the index rows. It does not check the data rows.

The check options apply only to checking MyISAM tables. They are ignored for InnoDB tables.

It's possible that CHECK TABLE will actually modify a table in some cases. For example, if a table is marked as corrupt or as not having been closed properly, but the check finds no problems, CHECK TABLE marks the table as okay. This change involves only modifying an internal flag.

Using REPAIR TABLE to Repair Tables

The REPAIR TABLE statement provides an interface to the server's table repair capabilities. It works only for MyISAM tables.

To use REPAIR TABLE, provide a list of one or more table names, optionally followed by modifiers that indicate what type of repair to do. For example, the following statement tries to repair three tables in quick repair mode:

REPAIR TABLE tbl1, tbl2, tbl3 QUICK;

REPAIR TABLE supports the following repair mode options:

• EXTENDED

  Attempt a repair by re-creating the indexes.

• QUICK

  Attempt a quick repair of just the indexes. Do not touch the data file.

• USE_FRM

  Attempt a repair using the table's .frm format file. MySQL uses the index information in the .frm file to reinitialize the index file, and then rebuilds the indexes based on the contents of the data rows. This repair mode is useful for rebuilding the indexes under circumstances when the index file is missing or corrupted to the point of being unusable.

With no options, REPAIR TABLE performs a repair operation corresponding to that done by myisamchk --recover. With EXTENDED, the operation is like that done by myisamchk --safe-recover.

Using mysqlcheck to Check and Repair Tables

The mysqlcheck program provides a command-line interface to the CHECK TABLE and REPAIR TABLE statements. It connects to the server and issues the appropriate statements for you based on the options you specify. mysqlcheck can check MyISAM and InnoDB tables and repair MyISAM tables.

Typically, you invoke mysqlcheck with a database name, optionally followed by one or more table names. With just a database name, mysqlcheck checks all the tables in the database:

% mysqlcheck sampdb

With table names following the database name, mysqlcheck checks only those tables:

% mysqlcheck sampdb president member

If you specify the --databases option, all following arguments are interpreted as database names and mysqlcheck checks all the tables in each database:

% mysqlcheck --databases sampdb test

If you specify --all-databases, mysqlcheck checks all tables in all databases. No database or table name arguments are needed:

% mysqlcheck --all-databases

mysqlcheck is more convenient than issuing the CHECK TABLE and REPAIR TABLE statements directly, because those statements require that you explicitly name each table to be checked or repaired. With mysqlcheck, it's much easier to check all tables in a database: It looks up the names of the tables in the database for you and constructs statements that name the appropriate tables.

By default, mysqlcheck checks tables using a medium check, but supports options that enable explicit selection of the type of operation to perform. The following table shows some mysqlcheck options and the CHECK TABLE options to which they correspond. (As with CHECK TABLE, these options apply only to MyISAM tables and are ignored for InnoDB tables.)

mysqlcheck Option	CHECK TABLE Option
--check-only-changed	CHANGED
--extended	EXTENDED
--fast	FAST
--medium-check	MEDIUM
--quick	QUICK

mysqlcheck can also perform table repair operations, but only for MyISAM tables. The following table shows some mysqlcheck options and the REPAIR TABLE options to which they correspond:

mysqlcheck Option	REPAIR TABLE Option
--repair	No options (performs a standard repair operation)
--repair --extended	EXTENDED
--repair --quick	QUICK
--repair --use-frm	USE_FRM

Using myisamchk to Check and Repair Tables

The myisamchk utility can check or repair MyISAM tables. myisamchk does its work by accessing table files directly, so before invoking it, you might want to stop the server to prevent it from accessing the table files while you're using them. If you leave the server running, make sure you read "Performing Database Maintenance with the Server Running." That section discusses the proper locking protocols for preventing the server from using a table at the same time that you're performing checking or repair procedures on it with myisamchk. The following discussion assumes that you have either stopped the server or are using the appropriate locking protocol.

myisamchk makes no assumptions about where tables are located. To run it, specify the pathnames to the table files you want to use. It's most convenient to do this if you're in the directory that contains the tables. Typically, you change location into the relevant database directory first before invoking myisamchk, and then tell it which tables you want to check or repair, along with the options that indicate what type of operation to perform:

% myisamchk options tbl_name ...

A tbl_name argument can be either a table name or the name of the table's index file. These commands are equivalent:

% myisamchk member
% myisamchk member.MYI

To name all the relevant index files in the database directory, use a filename pattern:

% myisamchk options *.MYI

If you don't want to attempt a maintenance operation with myisamchk on the original table files, copy them to another directory and then work with the copies in that directory.

Checking Tables with myisamchk

myisamchk provides table-checking methods that vary in how thoroughly they examine a table. To perform a normal table check, use either of the following commands:

% myisamchk tbl_name
% myisamchk --check tbl_name

myisamchk's default action with no options is --check, so those commands are equivalent.

The normal check method usually is sufficient to identify problems. If it reports no errors but you still suspect damage (perhaps because queries do not seem to return the correct results), perform an intermediate-level check by specifying the --medium-check option. This is somewhat slower but more thorough. Finally, you can perform the most extensive check by specifying the --extend-check option. This can be very slow, but it is extremely thorough. For each record in the table's data file, the associated key for every index in the index file is checked to make sure it really points to the correct record.

If no errors are reported for a check with --extend-check, you can be sure your table is okay. If you still have problems with the table, the cause must lie elsewhere. Re-examine any statements that seem to yield problematic results to verify that they are written correctly. If you believe the problem might be with the MySQL server, consider filing a bug report or upgrading to a newer version.

If myisamchk reports that a table has errors, you should try to repair it.

Repairing Tables with myisamchk

To perform a repair operation on a table with myisamchk, use the following procedure. It first tries the repair types that are faster but less thorough, and then escalates to slower but more thorough methods if faster methods fail to correct the problems. Before using the repair procedure, make copies of the table files in case something goes wrong. That is unlikely, but if it happens, you can make a new copy of the table from the copied files and try a different recovery method.

Note: If the table contains any FULLTEXT indexes, it might be necessary to use additional myisamchk options during repairs. See the FULLTEXT-related notes in the myisamchk description in Appendix F for details.

It's possible when you run these commands that myisamchk will stop with an error message of the form Can't create new temp file: file_name. Typically, this indicates the presence of a temporary file that was left around from a previous failed repair attempt. To force removal of the temporary file, repeat the command but add the --force option.

If the preceding repair procedure fails to repair the table, your index file may be missing or damaged beyond repair. It's also possible, although unlikely, that the table's .frm format file is missing. In either of these cases, you'll need to replace the affected files, and then try the repair procedure again.

To regenerate the index file for a table t, use this procedure if you have the table's format file, t.frm:

mysql> TRUNCATE TABLE t;

If you don't have the table's .frm format file, you'll need to recover it from your backup files first. Then use the procedure at the beginning of this section. If you are missing the .frm file and also have no backup, but you know the CREATE TABLE statement that must be issued to create the table, you still might be able to repair it as follows:

Getting myisamchk to Run Faster

myisamchk can take a long time to run, especially if you're working with a large table or using one of the more-extensive checking or repair methods. You can speed up this process by telling myisamchk to use more memory when it runs. myisamchk has several variables that can be set, the most important of which control the sizes of the buffers that it uses:

Variable	Meaning
key_buffer_size	Size of buffer used to hold index blocks
read_buffer_size	Size of buffer used for read operations
sort_buffer_size	Size of buffer used for sorting
write_buffer_size	Size of buffer used for write operations

To find out what values myisamchk uses for these variables by default, run it with the --help option. To specify a different value, use --var_name=value on the command line. For example, if you have lots of memory, you can tell myisamchk to use a 512MB sort buffer and 1MB read and write buffers by invoking it like this:

% myisamchk --sort_buffer_size=512M --read_buffer_size=1M \
    --write_buffer_size=1M other-options tbl_name

For repair operations, --sort_buffer_size applies when the --recover option is given, but not with --safe-recover. The --key_buffer_size option applies when --safe-recover is given or when you check the table with --extend-check.