Actually, any problem this book discusses could have specific nuances based on the storage engine you use. We will touch upon these aspects throughout the book. In this section, I want to show a few features of storage engines that are independent of other problems. We’ll cover a few basic problems that use tools specific to MyISAM or InnoDB because these are the most popular and frequently used storage engines. If you use a third-party storage engine, consult its user manual for useful tools.
Errors related to a storage engine are either reported back to the client or recorded in the error logfile. Usually the name of the storage engine appears in the error message. In rare cases, you will get an error number not known by the perror utility. This is usually a symptom of an issue with a storage engine.
One common storage engine issue is corruption. This is not
always the fault of the storage engine, but can have an external cause
such as disk damage, a system crash, or a MySQL server crash. For example,
if somebody runs
kill -9 on the server’s process, she
is almost asking for data corruption. We will discuss here what to do in
case of MyISAM and InnoDB corruption. We will not discuss how to fix
corruption of a third-party storage engine; consult its documentation for
guidance. As a general recommendation, you can try
CHECK TABLE, which many storage engines support.
CHECK TABLE for the MyISAM storage
engine is explained in Repairing a MyISAM table from SQL.)
Corruption is a difficult problem to diagnose because the user might not notice it until the MySQL server accesses a corrupted table. The symptoms can also be misleading. In the best case, you will get an error message. However, the problem might be manifested by incorrect execution of queries or even a server shutdown. If problems crop up suddenly on a particular table, always check for corruption.
Once you suspect corruption, you need to repair the corrupted table. It’s always a good practice to back up table files before doing a repair so you can go back if something goes wrong.
MyISAM stores every table as a set of three files:
table_name.frm contains the table structure (schema),
table_name.MYD contains the data, and
table_name.MYI contains the index. Corruption can
damage the datafile, the index file, or both. In such cases, you will
get an error like
"ERROR 126 (HY000): Incorrect
key file for table './test/t1.MYI'; try to repair it" or
"Table './test/t2' is marked as crashed and
last (automatic?) repair failed" when you access the table.
The error message can vary, but check for the words “repair” or
“crashed” as a clue that the table is corrupted.
The SQL statements
troubleshoot corruption. From the operating system shell, you can also
used the myisamchk utility for
the same purpose. One advantage of myisamchk is that you can use it without
access to a running MySQL server. For instance, you can try to repair a
table after a crash before bringing up the server again.
CHECK TABLE t2;+---------+-------+----------+------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+------------------------------------------------------+ | test.t2 | check | warning | Table is marked as crashed and last repair failed | | test.t2 | check | warning | Size of indexfile is: 1806336 Should be: 495616 | | test.t2 | check | error | Record-count is not ok; is 780 Should be: 208 | | test.t2 | check | warning | Found 780 key parts. Should be: 208 | | test.t2 | check | error | Corrupt | +---------+-------+----------+------------------------------------------------------+ 5 rows in set (0.09 sec)
This is an example of output for a corrupted table. Your first
resort is to run
REPAIR TABLE t2;+---------+--------+----------+----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+--------+----------+----------------------------------------+ | test.t2 | repair | warning | Number of rows changed from 208 to 780 | | test.t2 | repair | status | OK | +---------+--------+----------+----------------------------------------+ 2 rows in set (0.05 sec)
This time we were lucky and the table was repaired successfully.
We can run
CHECK TABLE again to
CHECK TABLE t2;+---------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+-------+----------+----------+ | test.t2 | check | status | OK | +---------+-------+----------+----------+ 1 row in set (0.02 sec)
If a simple
run does not help, there are two more options.
REPAIR TABLE EXTENDED works more slowly than
REPAIR TABLE, but can fix
99% of errors. As a last resort, run
TABLE USE_FRM, which does not trust the information in the
index file. Instead, it drops and then recreates the index using the
description from the
table_name.frm file and fills the key with values
For the very same purpose, you can use a utility named
mysqlcheck. This program works by
REPAIR statements to the server. It also
has very nice options, such as
--all-databases, which can help you
perform table maintenance effectively.
mysqlcheck connects to the MySQL server as any other client does, and thus can be used remotely.
All of these steps can also be performed using myisamchk, which has a lot of additional table maintenance options. I won’t describe all the features of the utility here, but instead concentrate on those specific to table repair.
myisamchk directly accesses table files and does not require the MySQL server to be started. This can be very useful in some situations. At the same time, myisamchk requires exclusive access to table files, and you should avoid using it when the MySQL server is running.
If you have to use myisamchk while the server is running,
issue the queries
FLUSH TABLES and
LOCK TABLE , then wait until the latest query returns a command
prompt, and then run myisamchk in
a parallel session. If other processes besides myisamchk access the table while myisamchk is running, even worse
corruption can occur.
A basic recovery command is:
myisamchk --backup --recover t2- recovering (with sort) MyISAM-table 't2' Data records: 208 - Fixing index 1 - Fixing index 2 Data records: 780
--backup option tells
myisamchk to back up the datafile
before trying to fix the table, and
--recover does the actual repair. If this
command is insufficient, you can use the
--safe-recover option. The latter option
uses a recovery method that has existed since very early versions of
MySQL and can find issues that the simple
--recover option cannot. An even more
drastic option is
You can also use the option
--sort-recover, which uses sorting to
resolve the keys even when the temporary file is very large.
Among other options, which I recommend you study carefully, is
the very useful
--description option, which prints a
description of the table. Taken together with
-v or its synonym,
--verbose, it will print additional
information. You can specify the
option twice or even three times to get more information.
InnoDB stores its data and indexes in shared tablespaces. If the
server was started with the option
--innodb_file_per_table at the moment of table
creation, it also has its own datafile, but the table definition still
exists in a shared tablespace. Understanding how table files are stored
can help to effectively maintain the data directory and backups.
InnoDB is a transactional storage engine and has internal
mechanisms that automatically fix most kinds of
corruption. It does this recovery at server startup. The following
excerpt from the error log, taken after a backup by MySQL Enterprise Backup (MEB) using the
mysqlbackup --copy-back command, shows
a typical recovery:
InnoDB: The log file was created by ibbackup --apply-log at InnoDB: ibbackup 110720 21:33:50 InnoDB: NOTE: the following crash recovery is part of a normal restore. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 110720 21:37:15 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Last MySQL binlog file position 0 98587529, file name ./blade12-bin.000002 110720 21:37:15 InnoDB Plugin 1.0.17 started; log sequence number 1940779532 110720 21:37:15 [Note] Event Scheduler: Loaded 0 events 110720 21:37:15 [Note] ./libexec/mysqld: ready for connections. Version: '5.1.59-debug' socket: '/tmp/mysql_ssmirnova.sock' port: 33051 Source distribution
But sometimes corruption is extremely bad and InnoDB cannot repair
it without user interaction. For such situations, the startup option
--innodb_force_recovery exists. It
can be set to any value from 0 to 6 (0 means no forced recovery, 1 is
the lowest level, and 6 is the highest level). When recovery is
successful, you can run certain types of queries against the table that
was repaired, but you’re prevented from issuing certain commands. You
can’t issue operations that modify data, but the option still allows
SELECT select statements, as
DROP statements. At level 6,
for instance, you can run only queries of the form
SELECT * FROM
table_name with no qualifying condition—no
BY, or other clauses.
In case of corruption, try each level of
--innodb_force_recovery, starting from 1 and
increasing, until you are able to start the server and query the problem
table. Your prior investigation should have uncovered which table is
corrupted. Dump it to a file using
OUTFILE, then recreate it using
CREATE. Finally, restart the server with
--innodb_force_recovery=0 and load
the dump. If the problem persists, try to find other tables that are
corrupted and go through the process until the server is fine
If you need to begin your repair of a database by using a positive
--innodb_force_recovery, the error
log often mentions it explicitly through messages such as this:
InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: about forcing recovery.
You will also find information there about unsuccessful automatic recovery and startup failure.
InnoDB writes checksums for data, index, and log pages immediately before writing actual data, and confirms the checksums immediately after reading from the disk. This allows it to prevent a majority of problems. Usually when you encounter InnoDB corruption, this means you have issues with either the disk or RAM.