The Ultimate Guide to High Availability with MariaDB

Repairing MariaDB Tables for SQL Server Users

You are viewing an old version of this article. View the current version here.

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 to repair tables in different ways. The default storage engine is InnoDB.

MariaDB provides specific SQL statements to deal with corrupted tables:

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.

InnoDB

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

To repair an InnoDB table after a crash:

  1. Restart MariaDB with the --innodb-force-recovery 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 DROP DATABASE.
  2. Restore a backup of the database. The exact procedure depends from the type of backup.

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 SHOW CREATE TABLE.
  3. Restart MariaDB.
  4. Drop the table with DROP TABLE.
  5. Recreate the table and restore the dump.

For more details, see InnoDB Recovery Modes.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.