Repairing MariaDB Tables for SQL Server Users

Repairing tables in MariaDB is not similar to repairing tables in SQL Server.

The first thing to understand is that every MariaDB table is handled by a storage engine. Storage engines are plugins that know how to physically read and write a table, so each storage engine allows one to repair tables in different ways. The default storage engine is InnoDB.

MariaDB provides specific SQL statements to deal with corrupted tables:

• CHECK TABLE checks if a table is corrupted;

• REPAIR TABLE repairs a table if it is corrupted.

As a general rule, there is no reason why a table that is corrupted on a master should also be corrupted on the slaves. Therefore, REPAIR is generally used with the NO_WRITE_TO_BINLOG option, to avoid replicating it to the slaves.

Partitioned Tables

are normally split into multiple physical files (one per partition). Even if one of the partitions is corrupted, in most cases other partitions are healthy.

For this reason, CHECK TABLE and REPAIR TABLE don't work on partitioned tables. Instead, use to check or repair a single partition.

For example:

Indexes

Indexes can get corrupted. However, as long as data is not corrupted, indexes can always be dropped and rebuilt with :

Checking and Repairing Tables

Here we discuss how to repair tables, depending on the storage engine.

InnoDB

InnoDB follows the "fail fast" philosophy. If table corruption is detected, by default InnoDB deliberately causes MariaDB to crash to avoid corruption propagation, logging an error into the . This happens even if the corruption is found with a CHECK TABLE statement. This behavior can be changed with the server variable.

To repair an InnoDB table after a crash:

1. Restart MariaDB with the option set to a low but non-zero value.

2. If MariaDB fails to start, retry with a higher value. Repeat until you succeed.

At this point, you can follow two different procedures, depending if you can use a backup or not. Provided that you have a usable backup, it is often the best option to bring the database up quickly. But if you want to reduce the data loss as much as possible, you prefer to follow the second method.

Restoring a backup:

1. Drop the whole database with .

2. Restore a backup of the database. The exact procedure depends on the .

Recovering existing data:

1. Dump data from the corrupter table, ordered by primary key. MariaDB could crash when it finds damaged data. Repeat the process skipping damaged data.

2. Save somewhere the table structure with .

3. Restart MariaDB.

4. Drop the table with .

For more details, see .

Aria and MyISAM

is not crash-safe. In case of a MariaDB crash, the changes applied to MyISAM tables but not yet flushed to the disk are lost.

is crash-safe by default, which means that in case of a crash, after repairing any table that is damaged, no changes are lost. However, Aria tables are not crash-safe if created with TRANSACTIONAL=0 or ROW_FORMAT set to FIXED or DYNAMIC.

System tables use the Aria storage engine and they are crash-safe.

To check if a MyISAM/Aria table is corrupted, we can use . To repair a MyISAM/Aria table, one can use . Before running REPAIR TABLE against big tables, consider increasing or .

MyISAM and Aria tables can also be automatically repaired when corruption is detected. This is particularly useful for Aria, in case corrupted system tables prevent MariaDB from starting. See and . By default Aria runs the quickest repair type. Occasionally, to repair a system table, we may have to start MariaDB in this way:

It is also possible to stop MariaDB and repair MyISAM tables with , and Aria tables with . With default values, a repair can be unnecessarily very slow. Before running these tools, be sure to check the page.

Other Storage Engines

Notes on the different storage engines:

• For , see .

• With , REPAIR TABLE also improves the compression rate.

• For , see .

• Some special storage engines, like or , do not support any form of check and repair.

_{This page is licensed: CC BY-SA / Gnu FDL}