MariaDB / MySQL: Table is marked as crashed and should be repaired (ERROR 1194 / 145)

What the message looks like

ERROR 1194 (HY000): Table 'wp_options' is marked as crashed and should be repaired
ERROR 145 (HY000): Table './shop/orders' is marked as crashed and should be repaired

Common causes

• The server was killed or crashed during a write: OOM killer, power loss, forced stop.
• The disk ran full while the table was written.
• The table uses MyISAM or Aria, which have no crash recovery like InnoDB.
• Table files were copied while the server was running.
• Failing disk or file system errors.

Check

1. Which engine does the table use? (replace db and table)
   mysql -e "SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'shop' AND table_name = 'orders'"Copy
2. Check the table (read lock while it runs)
   mysqlcheck --check shop ordersCopy
3. Is there free space on the data disk?
   df -h /var/lib/mysqlCopy
4. Why did the server stop? Crash or kill in the log
   journalctl -u mariadb -u mysql --since '1 day ago' --no-pager | grep -iE 'crashed|killed|out of memory|aborted' | tail -n 20Copy

Fix

• Make a copy first. If the table can still be read, mysqldump shop orders > orders.sql. Otherwise copy the table files while the server is stopped.
• Repair: mysqlcheck --repair shop orders or in SQL REPAIR TABLE shop.orders;. A repair can drop rows that are damaged, so compare the row count with your backup.
• If it does not work, stop the server and run myisamchk (MyISAM) or aria_chk (Aria) on the files. Make a copy of the files before that.
• Restore from a backup if the repair loses too much.
• Fix the cause (memory, disk, power) and consider converting to InnoDB: ALTER TABLE shop.orders ENGINE=InnoDB;. Test it first, as it changes locking and table size.

FAQ

Why do only some tables crash?

Only MyISAM and Aria tables are open for writing in a way that can break at a crash. InnoDB uses a log to recover.

Will REPAIR TABLE lose data?

It tries to keep everything, but it can discard unreadable rows. That is why a copy comes first.