Databases Reference
In-Depth Information
If there's a severe problem with your hardware, such as memory or disk corruption, or
if you run into a bug in MySQL or InnoDB, you might have to intervene and either
force recovery or prevent the normal recovery from happening.
Causes of InnoDB corruption
InnoDB is very robust. It is built to be reliable, and it has a lot of built-in sanity checks
to prevent, find, and fix corrupted data—much more so than other MySQL storage
engines, and even more than some other databases. However, it can't protect itself
against everything.
At a minimum, InnoDB relies on unbuffered I/O calls and
fsync()
calls not returning
until the data is safely written to physical media. If your hardware doesn't guarantee
this behavior, InnoDB can't protect your data, and a crash can cause corruption.
Many InnoDB corruption problems are hardware-related (e.g., corrupted page writes
caused by power failures or bad memory). However, misconfigured hardware is a much
bigger source of problems in our experience. Common misconfigurations include en-
abling the writeback cache on a RAID card that doesn't have a battery backup unit, or
enabling the writeback cache on hard drives themselves. These mistakes will cause the
controller or drive to lie and say the
fsync()
completed, when the data is in fact only
in the writeback cache, not on disk. In other words, the hardware doesn't provide the
guarantees InnoDB needs to keep your data safe.
Sometimes machines are configured this way by default, because it gives better perfor-
mance—which might be fine for some purposes, but not for a transactional database
server.
You can also get corruption if you run InnoDB on network-attached storage (NAS),
because completing an
fsync()
to such a device might just mean the device received
the data. The data is safe if InnoDB crashes, but not necessarily if the NAS device
crashes.
Sometimes the corruption is worse than other times. Severe corruption can crash
InnoDB or MySQL, but less severe corruption might just mean some transactions are
lost because the log files weren't really synced to disk.
How to recover corrupted InnoDB data
There are three major types of InnoDB corruption, and each requires a different level
of effort to recover the data:
Secondary index corruption
You can often fix a corrupt secondary index with
OPTIMIZE TABLE
; alternatively,
you can use
SELECT INTO OUTFILE
, drop and recreate the table, then use
LOAD DATA
INFILE
. (You can also alter the table to MyISAM and back.) These processes fix the
corruption by building a new table, and hence rebuilding the affected index.
