innodb_force_recovery

This page is part of MariaDB's Documentation.

The parent of this page is: System Variables for MariaDB Enterprise Server

Topics on this page:

Overview
USAGE
DETAILS
- SYNONYMS
- SCHEMA
- PARAMETERS
- SKYSQL
- PRIVILEGES
EXAMPLES
ERROR HANDLING
FEATURE INTERACTION
RESPONSES
DIAGNOSIS
ISO 9075:2016
CHANGE HISTORY
RELATED TOPICS
EXTERNAL REFERENCES

Overview

Disables certain InnoDB background and crash recovery operations to forcefully recover data from a corrupt database.

USAGE

DETAILS

The default value of 0 means that InnoDB is running normally.

The recovery mode behavior depends on the MariaDB server version, see the tables below for details.

In MariaDB 10.5 and later: Write transactions are permitted with innodb_force_recovery<=4
In MariaDB 10.4 and earlier: Write transactions are permitted with innodb_force_recovery<=3
In MariaDB 10.2.6 and earlier: Write transactions are only permitted with innodb_force_recovery=0

Regardless of the version of the MariaDB server, the values 5 and 6 can lead to data corruption and should be used very carefully and only if you have a database backup.

Mode	Description
`0`	The default mode while InnoDB is running normally.
`1`	Allows the server to keep running even if corrupt pages are detected. It does so by making redo log based recovery ignore certain errors, such as missing data files or corrupted data pages. Any redo log for affected files or pages will be skipped. You can facilitate dumping tables by getting the `SELECT * FROM table_name` statement to jump over corrupt indexes and pages (internal name `SRV_FORCE_IGNORE_CORRUPT`).
`2`	Stops the master thread from running, preventing a crash that occurs during a purge. No purge will be performed, so the undo logs will keep growing (internal name `SRV_FORCE_NO_BACKGROUND`).
`3`	Does not roll back transactions after the crash recovery. Does not affect rollback of currently active transactions, also prevents some undo-generating background tasks from running. These tasks could hit a lock wait due to the recovered incomplete transactions whose rollback is being prevented (internal name `SRV_FORCE_NO_TRX_UNDO`).
`4`	Does not calculate tables statistics and prevents insert buffer merges (internal name `SRV_FORCE_NO_IBUF_MERGE`). In MariaDB 10.5 - 10.6.4: The same as `3`. In MariaDB 10.6.5 and later: Does not roll back transactions after the crash recovery. Does not affect rollback of currently active transactions. Will also prevent some undo-generating background tasks from running. These tasks could hit a lock wait due to the recovered incomplete transactions whose rollback is being prevented (internal name `SRV_FORCE_NO_DDL_UNDO`).
`5`	Treats incomplete transactions as committed, and does not look at the undo logs when starting (internal name `SRV_FORCE_NO_UNDO_LOG_SCAN`). In MariaDB 10.6.5 and later: Additionally, any DDL log for InnoDB tables will be essentially ignored by InnoDB, but the server will start up.
`6`	Does not perform redo log roll-forward as part of recovery. Running queries that require indexes are likely to fail with this mode active. However, if a table dump still causes a crash, you can try using a `SELECT * FROM tab ORDER BY primary_key DESC` to dump all the data portion after the corrupted part (internal name `SRV_FORCE_NO_LOG_REDO`).

SYNONYMS

SCHEMA

PARAMETERS

Command-line	--innodb_force_recovery=#
Configuration file	Supported
Dynamic	No
Scope	Global
Data Type	BIGINT UNSIGNED
Minimum Value	0
Maximum Value	6
Product Default Value	0

SKYSQL

Cloud	SkySQL Topology	ES Ver
AWS	Multi-Node Analytics	10.6
	Single Node Analytics	10.6
	Replicated Transactions	10.4
		10.5
		10.6
	Single Node Transactions	10.4
		10.5
		10.6
GCP	Multi-Node Analytics	10.6
	Single Node Analytics	10.6
	Replicated Transactions	10.4
		10.5
		10.6
	Single Node Transactions	10.4
		10.5
		10.6

Cloud	SkySQL Topology	ES Ver
AWS	ColumnStore Data Warehouse	10.6
	Enterprise Server With Replica(s)	10.4
		10.5
		10.6
	Enterprise Server Single Node	10.4
		10.5
		10.6
GCP	ColumnStore Data Warehouse	10.6
	Enterprise Server With Replica(s)	10.4
		10.5
		10.6
	Enterprise Server Single Node	10.4
		10.5
		10.6

See SkySQL details in MariaDB SkySQL previous release
See SkySQL details in MariaDB SkySQL new release

PRIVILEGES

EXAMPLES

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series	History
11.4 Enterprise	Present starting in MariaDB Enterprise Server 11.4.3-1.
10.6 Enterprise	Present starting in MariaDB Enterprise Server 10.6.4-1.
10.6 Community	Present starting in MariaDB Community Server 10.6.0.
10.5 Enterprise	Present starting in MariaDB Enterprise Server 10.5.3-1.
10.5 Community	Present starting in MariaDB Community Server 10.5.0.
10.4 Enterprise	Present starting in MariaDB Enterprise Server 10.4.6-1.
10.4 Community	Present starting in MariaDB Community Server 10.4.0.
10.3 Enterprise	Present starting in MariaDB Enterprise Server 10.3.16-1.
10.3 Community	Present starting in MariaDB Community Server 10.3.0.
10.2 Enterprise	Present starting in MariaDB Enterprise Server 10.2.25-1.
10.2 Community	Present starting in MariaDB Community Server 10.2.0.

Release Series	History
11.4 Enterprise	Present starting in MariaDB Enterprise Server 11.4.3-1.
10.6 Enterprise	Present starting in MariaDB Enterprise Server 10.6.4-1.
10.5 Enterprise	Present starting in MariaDB Enterprise Server 10.5.3-1.
10.4 Enterprise	Present starting in MariaDB Enterprise Server 10.4.6-1.