Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

MySQL will not start after a crash: InnoDB errors and innodb_force_recovery used safely

Technical article
Backup & Recovery

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

When MySQL refuses to start after a crash and the error log blames InnoDB, a common cause is damage that normal crash recovery cannot repair: a corrupt page, a lost or unreadable data file, or a redo log that cannot be replayed. The innodb_force_recovery setting can get the server far enough up to read your data, but it is an emergency exit, not a repair. Copy the data directory first, start at level 1, dump what you can, and rebuild on a clean instance.

What the error means

InnoDB runs crash recovery every time the server starts after an unclean stop: it replays the redo log and rolls back transactions that never committed. If that process meets something it cannot reconcile, such as a page that fails its checksum, a missing tablespace file, or a log it cannot apply, the server stops rather than guess.

The error log is where the reason appears. By default on Unix, if --log-error is not given the destination is the console, and if it is given without a file name the destination is host_name.err in the data directory (MySQL 8.4: Default Error Log Destination Configuration). The manual also suggests running the server from a command prompt, not through mysqld_safe or a Windows service, when troubleshooting, so that you see what is happening (MySQL 8.4: InnoDB Troubleshooting).

A page-corruption failure looks like this in a 5.5.29 report on the MySQL bug tracker: InnoDB: Database page corruption on disk or a failed file read of page 27863. followed by InnoDB: You may have to recover from a backup. (MySQL bug #69476). That wording is from 2013. Wording can differ between versions, so match on the meaning (a page failed its integrity check), not the literal string.

What innodb_force_recovery does

It is a global, non-dynamic server variable with a default of 0 and a range of 0 to 6, "typically only changed in serious troubleshooting situations" (MySQL 8.4: InnoDB Startup Options and System Variables). Because it is not dynamic, you set it in the option file and restart. Each level includes the levels below it (MySQL 8.4: Forcing InnoDB Recovery):

Level Name in the manual What it skips
1 SRV_FORCE_IGNORE_CORRUPT Runs despite corrupt pages; SELECT tries to skip corrupt index records and pages
2 SRV_FORCE_NO_BACKGROUND Stops the master and purge threads
3 SRV_FORCE_NO_TRX_UNDO No transaction rollback after crash recovery
4 SRV_FORCE_NO_IBUF_MERGE No insert buffer merges, no table statistics; can permanently corrupt data files; read-only
5 SRV_FORCE_NO_UNDO_LOG_SCAN Ignores undo logs, so incomplete transactions are treated as committed; can permanently corrupt data; read-only
6 SRV_FORCE_NO_LOG_REDO No redo log roll-forward; can permanently corrupt data and leave pages obsolete; read-only

While the value is above 0, INSERT, UPDATE and DELETE are blocked, and a setting of 4 or greater puts InnoDB in read-only mode. The manual warns that values of 4 or more can permanently corrupt data files and should be tried on a separate physical copy first, and that you should always start at 1 and move up one level at a time. These restrictions can also make replication administration commands fail, because the replica status logs live in InnoDB tables.

MariaDB numbers some levels differently. Its page says that from MariaDB 10.6.5 level 4 is SRV_FORCE_NO_DDL_UNDO and level 5 treats incomplete transactions as committed, while before 10.6.5 level 4 was SRV_FORCE_NO_IBUF_MERGE and "the same as 3" (MariaDB KB: InnoDB Recovery Modes). That page is also inconsistent about whether writes are allowed at level 4, so do not assume a MariaDB server is writable at any non-zero level.

Likely causes

Cause What you see How to confirm
Operating system or file-access problem InnoDB prints an operating system error number during a file operation Check the data and log directories exist, mysqld can write to them, the option file is read, the disk is not full and no quota is exceeded (MySQL 8.4: Troubleshooting InnoDB I/O Problems)
Corrupt page on disk "Database page corruption on disk or a failed file read of page" and "You may have to recover from a backup" Same message on repeated starts; storage and operating system logs show read errors
Missing file-per-table file "Cannot open datafile for read-only: './test/t1.ibd'" and "Ignoring tablespace ... because it could not be opened" The named .ibd file is absent from the database directory (MySQL 8.4: Troubleshooting InnoDB Data Dictionary Operations)
Redo log ran out of space during or just after recovery Recovery cannot finish; checkpoints cannot advance Error log shows the failure at recovery time (MySQL 8.4: Troubleshooting Recovery Failures)
Very long rollback after a failed mass import or ALTER TABLE Server starts but sits rolling back SHOW PROCESSLIST shows Rolling back (MySQL 8.4: InnoDB Error Handling)
Operating system corrupted its own file cache Apparent page corruption, disk data may be fine The manual advises restarting the computer first (MySQL 8.4: InnoDB Recovery)

Prove the cause before changing anything

These steps are read-only. Do them in this order.

  1. Stop retrying. Leave the server stopped while you read the log, so the first failure is not buried under repeated attempts. (A practical caution: failing hardware can also get worse the more it is read.)
  2. Read the whole error log, from the first InnoDB line after the crash, and note any operating system error number. The manual's I/O troubleshooting list (above) covers the non-corruption causes: missing directories, permissions, unreadable option file, full disk, quota and name clashes.
  3. Check the storage layer with your operating system and hardware tools. A corruption message that returns on the same page points at the disk, not at MySQL. If the disk is failing, image it before anything else; see the related article on imaging a failing Linux disk.
  4. Decide whether a restart of the computer is worth trying first. The manual notes that apparent page corruption can be the operating system corrupting its own file cache while the data on disk is fine, and that restarting the computer first is best.

If a tablespace file is simply missing, the manual's guidance is to issue DROP TABLE to remove the missing table's data from the data dictionary. That only helps once the server is running, which for a damaged instance can mean level 1 or higher.

Fix

Step 0: copy the data directory (do this before anything else)

With the server stopped, copy the whole data directory and the option file to separate storage. The manual's cold-backup procedure lists the InnoDB data files (ibdata and .ibd), the redo log files (#ib_redo_N_) and my.cnf (MySQL 8.4: InnoDB Backup). That procedure assumes a slow shutdown, which a crashed server cannot give you, so the copy is a rescue copy of a damaged state. It is still the right copy to take, because levels 4 and above can change files for good. Do all experiments on a second copy, never on the only one.

Step 1: start at level 1 and dump

Add the setting to the option file under [mysqld], as the manual shows:

[mysqld]
innodb_force_recovery = 1

Start the server (from the command line if you can, so the log is in front of you). If it starts, dump every database you can with mysqldump or SELECT ... INTO OUTFILE. The manual's tips for stubborn tables:

  • If corruption stops a full dump, try ORDER BY primary_key DESC to read the part after the damaged region.
  • At higher levels complex queries (WHERE, ORDER BY) may fail, leaving only a plain SELECT * FROM t.
  • With level 3 or lower you can SELECT, DROP and CREATE tables.

If the server still will not start, stop it, raise the value by one, and try again. Do not jump to 6.

Step 2: special cases

  • Runaway rollback after a failed mass import or ALTER TABLE: kill mysqld, start with level 3, then drop the offending table (manual).
  • Redo log out of space during recovery: the manual says an innodb_force_recovery restart may be required, starting with at least SRV_FORCE_NO_IBUF_MERGE (level 4) or, if that fails, SRV_FORCE_NO_LOG_REDO (level 6). If that also fails, recovery from backup may be necessary. Levels 4 and 6 are the dangerous ones, so work on the copy.
  • After level 4 or above: you may need to drop and recreate all secondary indexes.

Step 3: rebuild, do not stay on the recovered instance

Create a fresh instance on healthy storage, load the dump into it, and cut over. The manual describes the setting as something to use "in an emergency situation so that you can start InnoDB and dump your tables". Remove innodb_force_recovery from the option file before the next normal start; leaving it in place keeps the instance blocked from writes.

Example (illustrative): after a power cut, DB01 will not start and the log reports a corrupt page. A rescue copy is made. At level 1 the server starts, 38 of 40 tables dump cleanly, and two large tables stop partway through. ORDER BY id DESC recovers the tail of one of them. The team loads the dumps into a new DB02, restores the two incomplete tables from the last good backup plus the binary logs, and retires DB01. The values are invented for the example.

Confirm the fix

A server that starts is not proof. On the rebuilt instance:

  1. Compare row counts and key totals between the dump source and the new tables, table by table.
  2. Run CHECK TABLE on each table. The manual notes it checks index structure but does not detect inconsistencies that affect only header data, and that innochecksum is the tool for verifying a whole .ibd file (MySQL 8.4: CHECK TABLE).
  3. Run the application's own consistency checks or a business report that you can compare with a known figure.
  4. Confirm innodb_force_recovery is back to 0 and the instance accepts writes.
  5. Take a fresh backup and restore it to a scratch server to prove the backup works.

What to watch afterwards

  • The error log for new InnoDB warnings, especially a repeat of the page-corruption message, which suggests the storage is still failing.
  • Hardware and operating system logs for read errors.
  • Backups. The manual recommends regular logical backups with mysqldump because "a binary file might be corrupted without you noticing it", while dumped text makes corruption easier to spot. A restore test is the real measure.

Risks and limits

  • No command or setting in this article has been run or lab-tested. Syntax and behaviour are taken from the documentation pages listed below.
  • The level descriptions are from the MySQL 8.4 manual. Other MySQL versions and MariaDB differ, and MariaDB's page contradicts itself on writes at level 4.
  • Levels 4 to 6 can permanently corrupt data. Use them only on a copy.
  • The manual warns that CHECK TABLE cannot detect every kind of corruption.
  • The page-corruption log wording is from a 5.5.29 report, not from 8.4 documentation.
  • A practical caution: a failing disk can get worse every time it is read, so image it first if hardware is suspected.

Sources