Repairing Crashed Database Tables in  WordPress

WordPress is a database heavy application, especially for sites that use too many plugins (in my opinion). I’ve come across the following database error while working with WordPress numerous times:

Table `wp_*.wp_posts` is marked as crashed and should be repaired

It’s easy to fix when you know what typically causes it, and how to repair your database tables. Here’s an overview based on what I’ve found from dealing with it myself numerous times.

Common Causes

Out of Disk Space

The most common cause is an exhausted server, or more specifically: you’re probably out of disk space on the server running MySQL. MySQL typically limits the space available for database storage to the space on your disk, but it depends on the my.cnf configuration settings you have in place. Even if you have a lot of disk space available on your server, MySQL may be at it’s limit depending on your configuration. To check if this is the problem you can use the following handy MySQL query to show the amount of free space available on your MySQL server.

SELECT ROUND(SUM(DATA_FREE)/1073741824,2) "Available Space (gb)" FROM  INFORMATION_SCHEMA.PARTITIONS

If you see a number at or close to 0 then this is most likely the cause. You’ll want to increase the storage available to MySQL, and once that’s done you’ll have to repair the corrupt (“crashed”) database tables using the commands below.

Database Table Limits

Database tables can be set with size limitations, although this is less likely these days. It was a common problem back in 2015, but today the size limits under MySQL 8 are quite large and would be difficult to reach in a WordPress database. It can be set lower specifically though, so if you’re not out of disk space then I’d recommend checking your table limits to make sure they aren’t at or close to their capacity limits.

SELECT
  TABLE_NAME,
  ENGINE,
  VERSION,
  ROW_FORMAT,
  TABLE_ROWS,
  AVG_ROW_LENGTH,
  DATA_LENGTH,
  MAX_DATA_LENGTH,
  INDEX_LENGTH,
  DATA_FREE,
  AUTO_INCREMENT,
  CREATE_TIME,
  UPDATE_TIME,
  CHECK_TIME,
  TABLE_COLLATION,
  CHECKSUM,
  CREATE_OPTIONS,
  TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'wp_yoursite'

Fixing Your Tables

To fix the error you’ll need to repair the table that was reported with the issue. You can do this by directly running a SQL query on the table, in a database client like PHPMyAdmin, or with PHP code. If you do have size limitations you’ll want to fix your disk space issues before running any of these fixes. Otherwise you’ll likely see the error pop back up right after you thought you solved it.

It is critically important that you backup your database before performing any of the following suggestions. You’re fully responsible for undetaking this yourself, I can’t be held responsible if you break anything.

Repair Using SQL

To repair your database table with a SQL command use this:

REPAIR TABLE wp_posts;

Repair Tables in PHPMyAdmin

To repair a table in PHPMyAdmin you’ll need to:

1. Click into your WordPress database to view a list of all tables
2. Select the table you want to repair
3. Scroll down to the bottom of the tabular rows shown and choose the “Repair” option from the dropdown

Repair Using PHP Code

If you’d like to use code in your theme or plugin to do this, then leverage $wpdb to run the query. Here’s a handy function you can drop into your theme’s functions.php file temporarily to make the process easy.

Once this code is in place you can safely repair any table in your database by visiting a URL with the following query: ?repair-database={table}, where table is the table you want to repair. For example, visiting a URL on your site with ?repair-database=wp_posts as the query would repair the wp_posts table in your database.

/**
 * Repair WordPress Database Tables
 */
function kevinlearynet_repair_database_tables()
{
  // Only allow for admin users
  if (! isset($_GET['repair-database'])) {
    return;
  }
  if (! is_user_logged_in()) {
    return;
  }
  if (! current_user_can('manage_options')) {
    return;
  }

  global $wpdb;
  $table = esc_attr($_GET['repair-database']);

  // Verify table exists
  $exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
  if ($exists !== $table) {
    wp_die("Database table does not exist: `{$table}`");
  }

  // Repair it and report results
  $result = $wpdb->query("REPAIR TABLE {$table}");
  if (1 === $result) {
    wp_die("Successfully repaired database table: `{$table}`");
  } else {
    wp_die("Failed to repair database table: `{$table}`:<br><br><code>{$wpdb->last_error}</code>");
  }
}
add_action('init', 'kevinlearynet_repair_database_tables');

Conclusion

That should solve the issue for you. If you still see the Table is marked as crashed and should be repaired database error after correctly repairing your database table using the steps above then you’ll need to dig into the contents of that table themselves. There must be a corrupted value inside of the table somewhere, I’ve seen this happen a few times with charcter encoding issues. Usually this occurs with sites that contain non-uniform UTF-8 characters in them.