MySQL innodb_force_recovery Not Working: Causes and Fixes

The innodb_force_recovery option starts MySQL in a recovery mode that lets the InnoDB storage engine run on a corrupted database, so we can dump the data before we rebuild the damaged tables. When innodb_force_recovery is not working, the cause is either a problem in the option file or corruption that needs a higher recovery level.

We use innodb_force_recovery when MySQL crashes at startup or on a query because of a corrupted InnoDB page, and we have no fresh backup to restore. The goal of recovery mode is to get the data out, not to keep the server running in that mode.

The following example sets the option and checks that the server uses it. The example uses MySQL 9.7.2 LTS on Linux, and the result of each command is in the comment.

# 1. In the option file, under the [mysqld] section, add the line
#    innodb_force_recovery = 1
my_print_defaults mysqld | grep force                   # --innodb_force_recovery=1 (mysqld reads the option)

# 2. Restart the MySQL server (not the machine), then check the value
sudo systemctl restart mysql
mysql -u root -p -e "SELECT @@innodb_force_recovery"    # 1

# 3. Dump the data while the server runs in recovery mode
mysqldump -u root -p --databases recipes > recipes.sql

Notice that we check the value in two places, in the option file with my_print_defaults and in the running server with SELECT @@innodb_force_recovery. When either check shows nothing or 0, the server is not in recovery mode, whatever the configuration file seems to say.

Next, we look at the reasons why innodb_force_recovery does not work, with the real error messages, and fix each one. After that, we dump and rebuild a corrupted table, and cover the alternatives when recovery mode cannot help.

1. Reasons for MySQL ‘innodb_force_recovery‘ not Working Issue

InnoDB stops the server when it reads a damaged page, because writing to a corrupted table can spread the damage. With innodb_force_recovery greater than 0, InnoDB skips some of its own checks and background work, so the server can start and answer SELECT queries.

For example, the database server of a recipe-sharing app loses power during a busy evening. After the restart, MySQL starts, but every query that reads the recipe table stops the server, and the error log reports a corrupted page.

Some common causes that can lead to the “not working” issue are listed in the table. Each row shows the symptom we see, so we can match it with our case.

Symptom	Cause	Fix
The server does not start, and the error log says unknown variable ‘Innodb_force_recovery=2’	A typo or an uppercase letter in the option name	Write the name in lowercase, see section 2.3
The server does not start, and the error log says unknown option ‘–service mysql restart’	A shell command was pasted into the option file	Remove the command line from the file
SELECT @@innodb_force_recovery returns 0	The option is in a section such as [mysql] or [client], or in a file that the server does not read	Move the option under [mysqld] in a file from section 2.2
The value stays the same after we change the option file	A value saved with SET PERSIST_ONLY in mysqld-auto.cnf overrides the option file	Run RESET PERSIST innodb_force_recovery, see section 2.4
SET GLOBAL innodb_force_recovery fails with ERROR 1238	The variable is read only and takes effect only at startup	Set it in the option file and restart the server
INSERT fails with ERROR 1881	Recovery mode blocks all data changes, as designed	Dump the data, see section 3
The server still crashes with value 1	The corruption is in a part that level 1 does not skip	Raise the value by one and try again

Two more causes come from how people use the option.

The recovery level is too high. If we start with 6 right away, without trying the lower values first, recovery can corrupt the data files permanently, because values 4 and above skip steps that protect the data. So we raise the level one step at a time.
The server was not restarted. The server reads innodb_force_recovery only at startup, so after changing the option file we restart the MySQL server. Restarting the whole machine is not necessary.

2. Solutions to Resolve ‘innodb_force_recovery‘ not Working Issue in MySQL Server

The fixes follow the order in which we check the problem. First, we read the error log to see why the server stops. Then we make sure the server reads the option, and only after that we change the recovery level.

Flowchart for innodb_force_recovery not working. Read the error log first. If the log shows unknown variable or unknown option, fix the line in the option file. Next check my_print_defaults mysqld; if the option is missing, move it under [mysqld] in a file the server reads. Restart the MySQL server and run SELECT @@innodb_force_recovery; if it shows a different value, run RESET PERSIST. If the server still crashes, raise the value by one, up to 3, and test 4 or higher only on a copy of the data directory. When the server runs, dump the data, rebuild the tables and set the value back to 0. — We fix the option file first and raise the recovery level last, one step at a time.

2.1. Check the MySQL Error Log

MySQL Error Log records each activity on the MySQL Server. If you experience the ‘MySQL innodb_force_recovery not working’ issue, first check the MySQL error log to find out the reason behind the issue.

The location of the error log depends on the platform and on the log_error setting.

On Windows, the default error log is the file host_name.err in the data directory, for example in the Data folder under C:\ProgramData\MySQL\MySQL Server 9.7 for a server set up with the MySQL Configurator.
On Linux, a server without a log_error setting writes the log to the console, so a systemd service writes it to the journal (journalctl -u mysql). Linux packages set log_error to a file, such as /var/log/mysql/error.log on Debian and Ubuntu, or /var/log/mysqld.log on Red Hat and Oracle Linux.
When the server runs, SELECT @@log_error prints the file name.

A corrupted page shows up in the log as Database page corruption on disk. The following lines come from a MySQL 9.7.2 server that crashed when a query read a damaged page of the recipe table.

[ERROR] [MY-011906] [InnoDB] Database page corruption on disk or a failed file read of page [page id: space=2, page number=10]. You may have to recover from a backup.
[ERROR] [MY-011899] [InnoDB] [FATAL] Unable to read page [page id: space=2, page number=10] into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted.
[ERROR] [MY-013183] [InnoDB] Assertion failure: buf0buf.cc:4133:ib::fatal triggered thread 140463891691200
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/9.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

When the log shows unknown variable or unknown option instead, the server stops before InnoDB starts, and the problem is in the option file, not in the data.

2.2. Check the Configuration File

If innodb_force_recovery isn’t working, it could be due to the option being in the wrong file or the wrong section. The server reads only the [mysqld] section (and [server]), so the same line under [mysql] or [client] has no effect, and @@innodb_force_recovery stays at 0.

MySQL reads several option files, and a later file overrides an earlier one.

On Linux, the files are /etc/my.cnf, /etc/mysql/my.cnf, SYSCONFDIR/my.cnf, $MYSQL_HOME/my.cnf and ~/.my.cnf. Debian and Ubuntu packages include more files from /etc/mysql/mysql.conf.d/, such as mysqld.cnf.
On Windows, the MySQL Configurator puts my.ini in the folder C:\ProgramData\MySQL\MySQL Server 9.7, and the Windows service starts the server with –defaults-file pointing to that file. With –defaults-file, the server reads only that file, so a line in C:\my.ini or %WINDIR%\my.ini has no effect. Only a server started without –defaults-file reads %WINDIR%\my.ini, C:\my.ini and BASEDIR\my.ini.
On both platforms, the server reads DATADIR/mysqld-auto.cnf last, which holds the values saved with SET PERSIST.

The command mysqld –verbose –help prints the list of files that the server reads, in order. The tool my_print_defaults prints the options that the server gets from these files, so it shows at once whether our line reaches mysqld.

mysqld --verbose --help | grep -A1 "Default options"
# Default options are read from the following files in the given order:
# /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

my_print_defaults mysqld
# --datadir=/var/lib/mysql
# --log_error=/var/log/mysql/error.log
# --innodb_force_recovery=1

When –innodb_force_recovery is missing from the my_print_defaults output, the server never sees the option, so changing its value has no effect.

2.3. Set the innodb_force_recovery Value Correctly

If you’re facing the ‘MySQL innodb_force_recovery not working’ issue, it might be because you’re trying to start the MySQL server with innodb_force_recovery set to 6 right away. Instead of jumping to the highest value, start with 1 and increase the value one step at a time. This approach minimizes the risk of data loss while giving MySQL a better chance to recover.

Each innodb_force_recovery value includes the effects of all lower values, and the name of each value says what InnoDB stops doing.

Value	Name	What InnoDB skips
0	(default)	Nothing, normal operation
1	SRV_FORCE_IGNORE_CORRUPT	Runs even when it finds a corrupt page, and SELECT tries to skip corrupt records and pages
2	SRV_FORCE_NO_BACKGROUND	Does not start the master thread and the purge threads
3	SRV_FORCE_NO_TRX_UNDO	Does not roll back unfinished transactions after crash recovery
4	SRV_FORCE_NO_IBUF_MERGE	Does not merge the insert buffer or calculate table statistics, and InnoDB becomes read only
5	SRV_FORCE_NO_UNDO_LOG_SCAN	Does not read the undo logs, so unfinished transactions count as committed
6	SRV_FORCE_NO_LOG_REDO	Does not apply the redo log, which leaves pages in an old state

With values 1 to 3, we can still run SELECT, CREATE TABLE and DROP TABLE. Values 4 and above can permanently corrupt the data files, so we use them only after testing them on a copy of the data directory. A value above 6 is not an error, because the server changes it to 6 and logs option ‘innodb-force-recovery’: unsigned value 7 adjusted to 6.

To set the option, locate the configuration file, go to the [mysqld] section, and insert the line. The option name is in lowercase, and the restart command goes in the terminal, not in the file.

[mysqld]
innodb_force_recovery = 1

sudo systemctl restart mysql
mysql -u root -p -e "SELECT @@innodb_force_recovery"    # 1

Two mistakes show up often in copied snippets, an uppercase letter such as Innodb_force_recovery=2, and the line service mysql restart inside the file. MySQL 9.7.2 refuses to start with either line, and the error log shows the reason.

[ERROR] [MY-000067] [Server] unknown variable 'Innodb_force_recovery=2'.
[ERROR] [MY-010119] [Server] Aborting

[ERROR] [MY-000068] [Server] unknown option '--service mysql restart'.
[ERROR] [MY-010119] [Server] Aborting

The dash form innodb-force-recovery = 1 also works, because MySQL treats dashes and underscores in option names the same way.

The default value of innodb_force_recovery is 0. The server reads the value only at startup, and SET GLOBAL innodb_force_recovery = 1 fails with ERROR 1238 (HY000): Variable ‘innodb_force_recovery’ is a read only variable.

If innodb_force_recovery is set to 1 and the server still crashes, raise the value to 2, and then to 3. If the server still crashes at 3, we copy the whole data directory, and test 4 or higher on the copy only. To avoid losing important data, always copy the data directory before making any changes.

2.4. Remove a Persisted Value

Since MySQL 8.0, the statement SET PERSIST_ONLY saves a read-only variable to the file mysqld-auto.cnf in the data directory. The server reads that file after all option files, so a persisted value wins over the line in my.cnf.

For example, a DBA ran SET PERSIST_ONLY innodb_force_recovery = 2 last month to test recovery mode, and forgot it. Today, my.cnf says 0, but the server starts with 2 and rejects every INSERT.

The table performance_schema.variables_info shows which file set the current value.

SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATH
FROM performance_schema.variables_info
WHERE VARIABLE_NAME = 'innodb_force_recovery';
-- innodb_force_recovery | PERSISTED | /var/lib/mysql/mysqld-auto.cnf

RESET PERSIST innodb_force_recovery;
-- after the restart: innodb_force_recovery | EXPLICIT | /etc/mysql/my.cnf

After RESET PERSIST and a restart, the source changes to EXPLICIT, and the value comes from the option file again.

3. Dump and Rebuild the Corrupted Tables

Recovery mode is a way to read the data, not a way to repair it. With innodb_force_recovery greater than 0, InnoDB rejects every change, so the app cannot run normally.

INSERT INTO recipes.recipe (name, minutes) VALUES ('Soup', 30);
-- ERROR 1881 (HY000): Operation not allowed when innodb_force_recovery > 0.

The repair is a rebuild. We dump the data, drop the damaged table, turn recovery mode off and load the dump into a fresh table. The following example rebuilds the recipe table with 192 rows, after the crash from section 2.1.

# 1. With innodb_force_recovery = 1, dump the database
mysqldump -u root -p --databases recipes --set-gtid-purged=OFF > recipes-dump.sql

# 2. Drop the damaged table (allowed with values 1 to 3)
mysql -u root -p -e "DROP TABLE recipes.recipe"

# 3. Remove innodb_force_recovery from my.cnf (or set it to 0) and restart the server
sudo systemctl restart mysql

# 4. Load the dump into a new table
mysql -u root -p < recipes-dump.sql

The option –set-gtid-purged=OFF leaves the GTID statement out of the dump. MySQL 9.7 has GTIDs turned on, and without the option, loading the dump into the same server fails with ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED.

The rebuilt table passes CHECK TABLE, and all 192 rows are back. But recovery mode does not fix the data on the damaged page, so a row from that page can contain damaged values. In the example, one row has broken bytes in its notes column. Every notes value in the test data is a run of one letter, such as aaaa, so the query below finds the rows that no longer match that pattern.

CHECK TABLE recipes.recipe;              -- recipes.recipe | check | status | OK
SELECT COUNT(*) FROM recipes.recipe;     -- 192

SELECT id, name FROM recipes.recipe
WHERE notes NOT REGEXP '^(a+|b+|c+)$';   -- 36 | Omelettexz (damaged row)

We always compare the restored data with a backup or with what the app expects, because a successful dump does not prove that every row is correct. When the whole server is damaged, we dump all databases with –all-databases, move the old data directory away, initialize a new one and load the dump there.

4. Alternate Methods to Restore Corrupt MySQL Database

If you fail to troubleshoot the “innodb_force_recovery not working” issue or if you want to recover the MySQL database without any data loss, you can use one of the following two methods.

4.1. Restore Database from Backup

If you have an updated backup of the MySQL database, you can restore the InnoDB tables from a mysqldump file. A restore from backup brings back the state at the time of the backup, so the changes made after it are lost.

First, create an empty database to restore into.

CREATE DATABASE recipes;

Then, restore the database from the dump file in the terminal.

mysql -u root -p recipes < dump.sql

The import restores all database objects in the dump. We can check the restored tables in the mysql client.

USE recipes;
SHOW TABLES;

When the dump was made with –databases, as in section 3, the file contains its own CREATE DATABASE and USE statements, so we run mysql -u root -p < dump.sql without a database name.

4.2. Use a Professional MySQL Recovery Tool

If you do not have a backup of the MySQL database file, you can use a MySQL database repair tool, such as Stellar Repair for MySQL. These tools read the damaged data files and recover the database objects from them. They can also repair corrupted InnoDB tables that the server can no longer open, and they support selective recovery of database objects.

You can save the repaired file in multiple formats, like MySQL, MariaDB, HTML, and CSV. The tool is compatible with both Windows and Linux operating systems. It can help resolve complex MySQL errors related to corruption in database and tables.

5. innodb_force_recovery FAQs

5.1. Can We Change innodb_force_recovery Without Restarting MySQL?

No. The variable is read only, so SET GLOBAL fails with ERROR 1238. We set it in the option file, or with SET PERSIST_ONLY, and restart the server.

5.2. Which innodb_force_recovery Value Should We Use?

We start with 1, because it already lets SELECT skip corrupt pages. We go to 2 and 3 only when the server still crashes. If we can dump the tables with a value of 3 or less, the dump is relatively safe, and only some data on the corrupt pages is lost.

5.3. Is innodb_force_recovery=6 Safe?

No. Value 6 skips the redo log, so the server starts with an old copy of the pages, which can add more corruption. We use 4, 5 or 6 only on a copy of the data directory, and only to dump the data.

5.4. How Do We Turn Off innodb_force_recovery?

We remove the line from the option file or set it to 0, run RESET PERSIST innodb_force_recovery if the value was persisted, and restart the server. After the restart, SELECT @@innodb_force_recovery returns 0, and INSERT works again.

6. Conclusion

When MySQL’s innodb_force_recovery fails to start the server, the cause is rarely the recovery itself. A typo, a shell command or a wrong section in the option file stops the server or hides the option, and a value in mysqld-auto.cnf can override the file. The error log, my_print_defaults and performance_schema.variables_info show which case we have.

Once the server reads the option, we start at level 1 and go up one step at a time. Levels 4 to 6 can permanently corrupt data files, so we test them only on a copy.

Recovery mode is read only, so its purpose is to dump the data. We turn the option off, rebuild the damaged tables from the dump and check the rows. If these solutions do not work, restoring from a backup or using a professional MySQL repair tool becomes necessary. Tools like Stellar Repair for MySQL can help recover corrupted databases. To prevent such issues in the future, we keep regular, tested backups.

7. References

Happy Learning !!