MySQL Troubleshooting

Issues with Solutions Specific to Storage Engines

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.

Note

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 Corruption

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 CHECK TABLE and REPAIR TABLE 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.

Repairing a MyISAM table from SQL

CHECK TABLE without parameters shows the current table status:

mysql> 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 without parameters:

mysql> 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 confirm this:

mysql> CHECK TABLE t2;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t2 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.02 sec)

If a simple REPAIR TABLE run does not help, there are two more options. REPAIR TABLE EXTENDED works more slowly than the bare REPAIR TABLE, but can fix 99% of errors. As a last resort, run REPAIR 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 from the table_name.MYD file.

Note

For the very same purpose, you can use a utility named mysqlcheck. This program works by sending CHECK and 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.

Repairing a MyISAM table using myisamchk

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.

Warning

If you have to use myisamchk while the server is running, issue the queries FLUSH TABLES and LOCK TABLE table_name WRITE, 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

The --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 --extend-check.

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 -v option twice or even three times to get more information.

InnoDB Corruption

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^[6]:

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 certain SELECT select statements, as well 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 WHERE, ORDER 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 SELECT INTO OUTFILE, then recreate it using DROP and 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 again.

If you need to begin your repair of a database by using a positive value for --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.

Note

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.

---

^[6] MySQL Enterprise Backup (MEB), formerly known as InnoDB HotBackup, is a tool that creates hot online backups of InnoDB tables and warm online backups of tables that use other storage engines. We will discuss backup methods in Chapter 7.