Repairing Corrupted MyISAM Tables

2020-06-07 · mysql, myisam, repair, recovery

Every few months an old application of ours loses power and a MyISAM table comes back with ERROR 145 (HY000): Table './appdb/hits' is marked as crashed and should be repaired. MyISAM isn't crash-safe by design. There's no redo log, so an interrupted write leaves the .MYD data file and .MYI index file out of step. Most of us moved to InnoDB years ago, but MyISAM survives in old applications, in the mysql system schema before 8.0, and in tables someone created with ENGINE=MyISAM for full-text search in 2011.

I'll go through the repair tools from least to most destructive, how to run them without making things worse, and how to stop having this conversation by converting the table. It applies to MySQL 5.6, 5.7 and MariaDB 10.x. MySQL 8.0 still supports MyISAM user tables and the same tools.

Copy first

Repair rewrites files in place. If it fails partway, you want the original. I've skipped this step exactly once.

sudo systemctl stop mysqld
sudo cp -a /var/lib/mysql/appdb/hits.{frm,MYD,MYI} /root/myisam-backup/

If you cannot stop the server, at least flush and lock the table for the copy:

FLUSH TABLES appdb.hits WITH READ LOCK;
-- copy the files from another shell
UNLOCK TABLES;

Assess the damage

Inside the server:

CHECK TABLE appdb.hits;

The result row's Msg_text column says OK, or something like Table is marked as crashed, Size of datafile is: 1048576 Should be: 1052672, or Found 3 keys of 4. The last two tell you whether the data file or the index file is the broken one, which decides how much you can recover.

Offline, with the server stopped, myisamchk does the same and is safer for large tables because it does not compete with the server for memory:

sudo -u mysql myisamchk --check /var/lib/mysql/appdb/hits.MYI
sudo -u mysql myisamchk --check --extend-check /var/lib/mysql/appdb/hits.MYI

--extend-check verifies every row against every index; it is slow but exact. Never run myisamchk on a table while mysqld has it open: the two will corrupt it further.

Repairing from inside the server

The least invasive fix is REPAIR TABLE, which rebuilds the index file from the data file:

REPAIR TABLE appdb.hits;

If the data file itself has bad rows, add EXTENDED, which rebuilds row by row and drops rows it cannot parse:

REPAIR TABLE appdb.hits EXTENDED;

And if the index file is so damaged that REPAIR TABLE cannot read the table definition from it, USE_FRM reconstructs the .MYI from the .frm definition:

REPAIR TABLE appdb.hits USE_FRM;

USE_FRM loses the AUTO_INCREMENT counter and any index statistics, so run it only after the previous two forms fail. Each form holds a write lock on the table for the duration; on a multi-gigabyte table that is minutes to hours.

Offline repair with myisamchk

For big tables, or when the server won't start because a system table is crashed, use the offline tool. Give it memory or it'll take all day:

sudo systemctl stop mysqld
sudo -u mysql myisamchk --recover --sort_buffer_size=512M --key_buffer_size=512M \
  --read_buffer_size=8M --write_buffer_size=8M /var/lib/mysql/appdb/hits.MYI

--recover (-r) rebuilds the index and can fix most things. If it fails with Can't create new tempfile or reports rows it cannot handle, escalate to --safe-recover (-o), which uses an older, slower algorithm that handles more kinds of damage:

sudo -u mysql myisamchk --safe-recover --sort_buffer_size=512M /var/lib/mysql/appdb/hits.MYI

--force (-f) makes it repair without asking even if it thinks the table is fine. Always run as the mysql user, or the files end up owned by root and the server cannot open them.

To do a whole schema at once:

sudo -u mysql myisamchk --recover --sort_buffer_size=512M /var/lib/mysql/appdb/*.MYI

Making sure it doesn't happen again

Set the server to check and repair MyISAM tables automatically when it opens one that was not closed cleanly. In /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
myisam_recover_options = BACKUP,FORCE

BACKUP saves a copy of the data file as hits-datetime.BAK before repairing; FORCE repairs even if more than one row would be lost. On MySQL 5.5 and MariaDB the option is called myisam-recover.

Then convert the table, which is the real fix:

ALTER TABLE appdb.hits ENGINE=InnoDB;

Before you do, check for MyISAM-only features the table depends on: FULLTEXT indexes are fine on InnoDB since 5.6, but a SPATIAL index requires 5.7.5+, and tables relying on the MyISAM behaviour of AUTO_INCREMENT on a secondary column of a composite key have no direct InnoDB equivalent.

Find all remaining MyISAM tables so you can plan:

SELECT table_schema, table_name, ROUND((data_length + index_length) / 1024 / 1024) AS mb
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY mb DESC;

Did I lose rows?

CHECK TABLE appdb.hits EXTENDED;
SELECT COUNT(*) FROM appdb.hits;
SHOW TABLE STATUS FROM appdb LIKE 'hits'\G

Compare the row count with what the application expects, or with a recent backup. REPAIR ... EXTENDED and myisamchk --safe-recover may have dropped rows; the repair output tells you how many with Data records: 1049231 Deleted: 0 before and after. Also confirm the Auto_increment value in SHOW TABLE STATUS is higher than the maximum key in the table, and fix it if USE_FRM reset it:

ALTER TABLE appdb.hits AUTO_INCREMENT = 1049232;

Errors after a repair

ERROR 144 (HY000): Table './appdb/hits' is marked as crashed and last (automatic?) repair failed. The automatic repair from myisam_recover_options gave up. Go offline and use myisamchk --safe-recover.

myisamchk: error: 140 when opening MyISAM-table or Table is in use. The server still has the table open. Stop mysqld, or FLUSH TABLES and try again.

myisamchk: Disk is full writing '/var/lib/mysql/appdb/hits.TMD'. Repair needs space for a second copy of the data file. Use --tmpdir=/mnt/spare to point the temporary files elsewhere.

ERROR 1034 (HY000): Incorrect key file for table 'hits'; try to repair it after a repair that reported OK. The .MYI was written by a different MySQL version or copied between architectures. Run REPAIR TABLE ... USE_FRM to regenerate it for the running server.

Honestly, the best repair is ALTER TABLE ... ENGINE=InnoDB before the crash happens.