The Problem
You’re likely in the middle of a routine task when a query suddenly fails. Instead of data, you get a cryptic message: ERROR 145. This error is a classic headache for anyone still running the MyISAM storage engine.
Unlike InnoDB, which became the default in MySQL 5.5.5 and features a robust crash recovery system, MyISAM is fragile. It is prone to corruption if the MySQL process is interrupted while writing to the disk. If your server recently hit 100% disk usage or suffered a hard reboot, the index file likely didn't close properly, leaving the table "marked as crashed."
Quick Fix (TL;DR)
Got access to a MySQL terminal or phpMyAdmin? Run this command right away to fix about 95% of these cases:
REPAIR TABLE tablename;
If the standard repair fails, try the more intensive version:
REPAIR TABLE tablename EXTENDED;
Why MyISAM Tables Crash
Understanding the root cause helps prevent this from becoming a weekly chore. MyISAM stores data across three files: .frm (schema), .MYD (data), and .MYI (indexes). The error occurs when the header of the .MYI file is out of sync with the actual data. Common culprits include:
- Unclean Shutdowns: A power outage or a developer running
kill -9on the MySQL process. - Disk Capacity: The server ran out of space while updating a 5GB table, leaving the index incomplete.
- Hardware Fatigue: Failing SSDs or bad RAM sectors can corrupt bits on the disk.
- Legacy File Limits: Reaching 2GB or 4GB file size limits on older 32-bit filesystems.
Method 1: Repairing via SQL (Safe and Easy)
Always try this method first. It works while the MySQL server is running, and the engine handles all the tricky file locking for you.
1. Standard Repair
Log into your MySQL console and target your database:
USE your_database_name;
REPAIR TABLE tablename;
Look for the output row where Msg_type is "status". If Msg_text says "OK", you’re back in business.
2. Extended Repair
Sometimes the index is too messy for a quick fix. MyISAM can recreate the indexes row by row instead. It takes longer but is much more thorough:
REPAIR TABLE tablename EXTENDED;
3. Repair with .frm file
If your .MYI file is missing or totally destroyed, you can tell MySQL to rebuild everything using the table definition stored in the .frm file:
REPAIR TABLE tablename USE_FRM;
Method 2: Repairing via Command Line (myisamchk)
Occasionally, the corruption is so severe that the MySQL service won't even start, or it crashes whenever you query the broken table. In these scenarios, use the myisamchk utility from the Linux shell.
WARNING: You must stop the MySQL service before using myisamchk. Running this tool on a live, active table can lead to permanent data loss.
# Stop the service first
sudo systemctl stop mysql
Navigate to your data directory, which is usually found at /var/lib/mysql/[db_name]/:
cd /var/lib/mysql/your_db_name/
myisamchk -r tablename.MYI
The -r flag stands for "recover." If that doesn't do the trick, try the "safe recovery" flag, which is slower but more cautious:
myisamchk -o tablename.MYI
Once finished, bring the database back online:
sudo systemctl start mysql
How to Verify the Fix
Check the table health after any repair to ensure no lingering issues exist:
CHECK TABLE tablename;
If the status is "OK", the table is healthy. Run a SELECT COUNT(*) to make sure your row count matches your expectations before resuming production traffic.
How to Prevent Future Crashes
While MyISAM was the standard for years, it’s now considered legacy technology for most use cases. Stop the cycle of repairs with these three steps:
1. Migrate to InnoDB
InnoDB is ACID-compliant. It uses a redo log to recover automatically from crashes without manual intervention. Switch your table engine with a single command:
ALTER TABLE tablename ENGINE=InnoDB;
2. Monitor Disk Usage
Most MyISAM crashes happen because the disk hit 100% capacity. Set up alerts (like Monit or Prometheus) to notify you when disk space exceeds 80%.
3. Graceful Shutdowns
Never pull the plug on a database server. Always use systemctl stop mysql or mysqladmin shutdown to give MySQL time to flush its buffers and close index headers correctly.