How to repair a crashed WordPress database table

A WordPress database may experience failure and corruption under various circumstances. One of common problems with it is "crashed database." When that happens, you will find the following error in WordPress error log:

WordPress database error Table './my_home/wp_posts' is marked as crashed and last (automatic?) repair failed for query SELECT post_date_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page', 'attachment') ORDER BY post_date_gmt DESC LIMIT 1 made by require('wp-blog-header.php'), wp, WP->main, WP->send_headers, get_lastpostmodified, get_lastpostdate, _get_last_post_time

The symptom of such table crash varies. Depending on which table was crashed, your WordPress blog may malfunction differently. If a crashed table is 'wp_posts' table which contains a list of posts, you will not see any post in your blog. Xmodulo experienced such database crash over the weekend:

The WordPress blog of Xmodulo is powered by MySQL at the moment, so in this tutorial, I will focus on MySQL.

Why a MySQL Table Crash?

There may be different reasons, but typically when mysqld or the host where mysqld is running is forcibly shut down in the middle of a database update, a database crash can be triggered. In the rest of the tutorial, I will show how to repair a crashed WordPress MySQL database table.

Method One

If you are using a shared web hosting, typically you will have access to Cpanel, which allows you to repair a database through Cpanel web interface.

Log in to Cpanel, and click into "MySQL Databases" under "Databases" section.

On MySQL Databases" page, you will find "Repair DB" option under "Modify Databases" section.

Choose the database which contains a crashed table, and click "Repair DB" button. If repair is successful, you will see the status of every table as "OK".

my_database.wp_oiopub_tracker_visits OK
my_database.wp_options OK
my_database.wp_postmeta OK
my_database.wp_posts
error : Keyblock size at page 13001728 is not correct. Block length: 532 key length: 2
Error : Incorrect key file for table './my_database/wp_posts.MYI'; try to repair it
error : Corrupt
---------
After repair:
----
my_database.wp_oiopub_tracker_visits OK
my_database.wp_options OK
my_database.wp_postmeta OK
my_database.wp_posts OK
my_database.wp_sharebar OK
my_database.wp_term_relationships OK

Method Two

If you have root access on the host (e.g., VPS) where a MySQL sever is running, you can try command-line interface to fix the crashed database table.

For a MyISAM-type database, you can fix a crashed table with a command-line utility called myisamchk. This utility comes with MySQL server installation.

First, stop MySQL server.

$ sudo service mysql stop (Debian/Ubuntu)
$ sudo service mysqld stop (CentOS/RHEL)
$ sudo systemctl stop mysqld.service (Fedora)

Then use this command to fix a particular table.

$ cd /var/lib/
$ sudo myisamchk -r -v -f mysql/<database_name>/<table_name>

Finally, start MySQL server again.

Do you have any experience with a crashed database, or any other problem with a WordPress database? Do you regularly back up your WordPress database? Tell us what was your approach to fix the problem and maintain the database.

Subscribe to Xmodulo

Do you want to receive Linux FAQs, detailed tutorials and tips published at Xmodulo? Enter your email address below, and we will deliver our Linux posts straight to your email box, for free. Delivery powered by Google Feedburner.

The following two tabs change content below.
Dan Nanni is the founder and also a regular contributor of Xmodulo.com. He is a Linux/FOSS enthusiast who loves to get his hands dirty with his Linux box. He likes to procrastinate when he is supposed to be busy and productive. When he is otherwise free, he likes to watch movies and shop for the coolest gadgets.

One thought on “How to repair a crashed WordPress database table

  1. Better "Method 2" (IMHO): don't stop your MySQL server and instead of myisamchk use:

    $ mysqlcheck -u user_name -p --auto-repair database_name table_name

    If you don't need a different user to connect to MySQL leave out the "-u user_name" part
    If you don't need a password to connect to MySQL, leave out the "-p"

    BTW, a crashed table cannot be backed up, so make sure to monitor your logs and don't trust an automated backup!

    Cheers.

Leave a comment

Your email address will not be published. Required fields are marked *

Current ye@r *