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

NetLeverage
NetLeverage

MySQL or MariaDB: 'Table is marked as crashed and should be repaired'

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.

The message Table '%s' is marked as crashed and should be repaired means the server found a MyISAM or Aria table whose index file says it was not closed cleanly, for example after a crash, an unexpected shutdown, a hardware fault or a file copy taken while the table was in use. The table may be fine, or it may have damaged indexes or lost rows. Copy the table's files first, check it, repair it with the least aggressive method that works, and then move it to InnoDB.

What the error means

Error 1194 is ER_CRASHED_ON_USAGE: Table '%s' is marked as crashed and should be repaired. Error 1195 is ER_CRASHED_ON_REPAIR: Table '%s' is marked as crashed and last (automatic?) repair failed. Related errors are 1034, Incorrect key file for table '%s'; try to repair it, and 1035, Old key file for table '%s'; repair it! (MySQL 8.4 Error Reference). The manual's description of corrupted MyISAM tables names Incorrect key file for table: '...'. Try to repair it as the typical error on SELECT, or queries that miss rows or return incomplete results (Corrupted MyISAM Tables, MySQL 8.4).

Each MyISAM index file (.MYI) has a counter in its header that records whether the table was closed properly. It rises the first time a table is updated and falls when the last instance closes. A CHECK TABLE or myisamchk warning of clients are using or haven't closed the table properly means the counter is out of step. The manual says that "doesn't necessarily mean that the table is corrupted, but you should at least check the table" (Problems from Tables Not Being Closed Properly).

The manual lists the ways the counter goes wrong:

  • A MyISAM table is copied without first issuing LOCK TABLES and FLUSH TABLES.
  • MySQL crashed between an update and the final close.
  • A table was modified by myisamchk --recover or --update-state while mysqld was using it.
  • Several mysqld servers share the table and one runs REPAIR TABLE while another uses it.

Corruption causes named on the other page are mysqld killed mid-write, an unexpected computer shutdown, hardware failure, an external program such as myisamchk changing a table while the server does, and a software bug.

The MariaDB side

MariaDB's Aria engine has its own repair tools and automatic-repair setting, so Aria tables can need the same treatment. MariaDB's Aria pages do not give this exact message text, so treat an Aria table that reports corruption as the same class of fault, not a guaranteed match. The Aria page says that with the TRANSACTIONAL table option set to 1, tables are crash-safe, and that "all system tables are Aria" (Aria Storage Engine, MariaDB KB). Automatic repair is governed by aria_recover_options, with values NORMAL, BACKUP, FORCE, QUICK and OFF; the default is BACKUP,QUICK, introduced in MariaDB 10.2.0 (Aria System Variables).

Likely causes

Cause What you see How to confirm
Server killed, crashed or machine lost power Error 1194 on first use after restart Error log shows a restarted mysqld message just before (Corrupted MyISAM Tables)
Live file copy or snapshot of a MyISAM table The restored copy shows the warning or the 1194 error at once The backup method copied .MYD and .MYI without FLUSH TABLES ... WITH READ LOCK or a server stop
Hardware failure Repeated corruption on the same table or others Storage and operating system logs show read or write errors
An external tool (myisamchk, aria_chk) used on a live table Counter warnings The tool was run while the server was using the table
Several servers sharing one data directory Warnings from other servers after one runs CHECK or REPAIR Topology review; the manual says sharing a data directory is "a bad idea"
Failed automatic repair Error 1195 myisam_recover_options or aria_recover_options is set; error log has Couldn't repair table

Prove the cause before changing anything

  1. Confirm the engine. SHOW CREATE TABLE tbl_name\G shows the ENGINE clause. A crashed flag only applies to MyISAM and Aria; an InnoDB problem needs a different route (see the related article on InnoDB crash recovery).
  2. Read the error log for a recent restarted mysqld message. If it is present, the server probably died and caused the corruption. If it is not, the manual treats corruption during normal operation as a probable bug and recommends a reproducible test case.
  3. Check how the table last reached this server. A restored file copy, a snapshot or an rsync of a live data directory all produce the same flag.
  4. Check free disk space and storage logs with your operating system tools before any repair. A repair writes new files, so leave room for a second copy of the table.
  5. Run CHECK TABLE tbl_name; while the server is running. The result set ends with a row where Msg_type is status and Msg_text is normally OK. The manual says it is safe to use CHECK TABLE even where several servers share a table, though you may see the warning from the others. For MyISAM, QUICK skips the row scan, FAST checks only tables not closed properly, MEDIUM is the default for dynamic-format MyISAM tables (and for static-format tables unless CHANGED or FAST is given, which then use QUICK), and EXTENDED "ensures that the table is 100% consistent, but takes a long time" (CHECK TABLE, 8.4). The manual says the problem is most likely in the indexes, which all check types test thoroughly, but that QUICK carries "a very small risk" of missing a data-file error.

Fix

These statements are taken from the documentation and have not been run. The manual's REPAIR TABLE and myisamchk pages both warn that "under some circumstances the operation might cause data loss", so the first step is always a copy.

Step 0: copy the table files

Stop writes to the table (or stop the server). Copy the table's .MYD and .MYI files, and the .sdi file the manual names alongside them, to separate storage. For a consistent copy of live MyISAM tables the manual's method is to stop the server, or run FLUSH TABLES tbl_list WITH READ LOCK;, copy, then unlock (Database Backup Methods, 8.4). A file copy taken without either step will carry the same crashed flag.

Step 1: REPAIR TABLE (server running)

REPAIR TABLE tbl_name QUICK;

REPAIR TABLE works for MyISAM, ARCHIVE and CSV tables and requires SELECT and INSERT on the table (REPAIR TABLE, 8.4). Options, listed here from least to most invasive (the manual does not rank them):

  • QUICK repairs only the index file, like myisamchk --recover --quick.
  • No option does the normal repair.
  • EXTENDED builds the index row by row instead of sorting, like myisamchk --safe-recover.
  • USE_FRM is for a missing or corrupt .MYI header. The manual says to use it "only if you cannot use regular REPAIR modes", and it loses the current AUTO_INCREMENT value and the link to deleted records.

Notes from the manual: by default the statement is written to the binary log and replicates, and NO_WRITE_TO_BINLOG or LOCAL suppresses that; if the source table was corrupted, repair changes are not propagated to replicas, so check replicas separately; and if the server exits during a repair, run REPAIR TABLE again immediately after restart, before anything else touches the table. If the result is not OK for a MyISAM table, the manual says to try myisamchk --safe-recover.

Step 2: myisamchk (server stopped)

myisamchk must not run while another program uses the table. The manual's most effective advice is to shut the server down, or to lock the tables, and it recommends CHECK TABLE where possible (myisamchk, 8.4). With the server stopped:

myisamchk --backup --recover /path/to/datadir/dbname/tbl_name.MYI

--recover "can fix almost any problem except unique keys that are not unique", and the manual says to try it first. Use --safe-recover only if --recover reports the table cannot be recovered. If --recover fails, "the data file remains intact". --backup saves the .MYD file as file_name-time.BAK, and --quick modifies only the index file (myisamchk Repair Options). Avoid --extend-check unless you are desperate.

Step 3: Aria tables on MariaDB

REPAIR TABLE works for Archive, Aria, CSV and MyISAM tables, and Aria tables are repaired by default with the effect of aria_chk --recover; syntax before MariaDB 11.5 has no FORCE option (REPAIR TABLE, MariaDB KB). aria_chk checks with -c and repairs with -r or -o, backs up the data file with -B, and "should not be used when MariaDB is running" (aria_chk).

What repair can lose

Repair can drop rows it cannot read. With automatic recovery set to FORCE, the server writes a warning of the form Found 344 of 354 rows when repairing ./test/g00pages, which means 10 rows were lost. Record the row count before and after, and compare with the last good backup.

Step 4: move the table to InnoDB

ALTER TABLE tbl_name ENGINE=InnoDB;

The manual says InnoDB tables need more disk space than MyISAM, that you need enough room for both the old and new table, and that an ALTER TABLE which runs out of space rolls back, which can take hours (Converting Tables from MyISAM to InnoDB). The alternative is to create an empty InnoDB clone and INSERT INTO ... SELECT into it, so you can test both side by side. Repair the table first, then convert.

Example (illustrative): after a power cut on SQL02, a MyISAM table shop.sessions returns error 1194. The administrator stops the application, copies the .MYD and .MYI files, and runs CHECK TABLE, which reports an error. REPAIR TABLE ... QUICK returns OK; a follow-up CHECK TABLE ... EXTENDED also returns OK. The row count is 9,872 against 9,880 on the last backup, so 8 recent rows are re-created from the application log. The table is then converted to InnoDB. The numbers are invented.

Confirm the fix

  1. CHECK TABLE tbl_name EXTENDED; ends with OK. When a check finds the table okay the header counter is reset to zero.
  2. Compare row counts and key totals with the last good backup and the copy you took.
  3. Run the application's own checks.
  4. Restore your most recent backup of this table onto a scratch server and run CHECK TABLE there. If it fails, your backup method is copying live files.

What to watch afterwards

  • Set myisam_recover_options with the BACKUP value if you must keep MyISAM tables. The manual says recovery then writes tbl_name-datetime.BAK files and that you should have a cron script that moves them to backup media. It also says that recovery that cannot recover all rows logs Couldn't repair table unless FORCE is set.
  • Watch the error log for further restarted mysqld lines and for the warning about unclosed tables.
  • Back up MyISAM data with FLUSH TABLES ... WITH READ LOCK, a server stop, or a logical dump, never a bare file copy of a live directory. See the related article on filesystem freeze and thaw for VM snapshots.

Risks and limits

  • No statement or command here has been run or lab-tested.
  • Repair can lose rows. The manual states data loss is possible under some circumstances.
  • MariaDB behaviour for aria_recover_options and REPAIR TABLE is from its Knowledge Base, which shows no dates; check your exact version.
  • USE_FRM and --safe-recover have particular consequences described in the manual; use them only on a copy.
  • The manual's discussion of myisam_recover_options does not describe every value (DEFAULT, QUICK, OFF); check the option reference for your version.

Sources