How to fix a WordPress site when the database corrupts on you.

Phew!

... and as soon as I say this, the database powering this site corrupts on me - charming!

For one awful moment I thought I had lost everything (I hadn't set up a regular mysql backup task for this blog), but thankfully, bringing this blog back from the dead was surprisingly straight forward.

Here's what happened, and what I did to fix it.

Yesterday when writing a post about how there's a mobile missing tariff that should exist but doesn't, when I posted, WordPress would accept my post; those pretty git spinners just kept spinning on the text page.

I didn't think too much of it, at the time, as I was heading out to meet James to work on some top sekrit project, based around node.js, mongodb, and doing something strange yet hopefully useful with wifi, so I just saved it locally on my mac, and headed out on my bike.

This evening though, I tried again, and I had the same error. "Ah," I thought, "maybe I just need to restart the database on this box - it's been going for ages anyway". So I ssh'd into the server running the system, and called the usual CentOS restart command

  service mysql restart

This took nearly two minutes to stop, then spat out this response when restarting:

[chris@stemcaa2 ~]# service mysql restart
Shutting down MySQL........................................[  OK  ].......
Starting MySQL.........................................................................................................................................................................................................................................../sbin/service: line 66: 27651 Terminated              env -i LANG="$LANG" PATH="$PATH" TERM="$TERM" "${SERVICEDIR}/${SERVICE}" ${OPTIONS}

Okay, slight panic now - lets check the disk, to see if there's space for the database to write, with df -h

 
[chris@stemcaa2 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             9.9G  9.4G     0 100% /
tmpfs                 129M     0  129M   0% /dev/shm
/usr/tmpDSK           485M   11M  449M   3% /tmp

Ah, that's not good. Disk space is like oxygen for servers, and without it, things break quickly.

The quickest way to make some breathing space fast is to clear the yum cache, like so:

yum clean headers packages

Okay, lets try restart again:

[chris@stemcaa2 ~]# service mysql restart
Shutting down MySQL.                                       [  OK  ]
Starting MySQL.                                            [  OK  ]

Sweet! It worked! But hang on... now wordpress isn't showing any posts! This really isn't good.

So lets check the database;

[root@stemcaa2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.90-community MySQL Community Edition (GPL)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| antia_wordpress    | 
| chris_wordpress    | 
| cphulkd            | 
| deadonim_wordpress | 
| dov_wordpress      | 
| eximstats          | 
| horde              | 
| leechprotect       | 
| modsec             | 
| mysql              | 
| roundcube          | 
+--------------------+
12 rows in set (0.16 sec)

Okay the database is there. What about the tables though?

 
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_chris_wordpress |
+---------------------------+
| wp_ak_twitter             | 
| wp_commentmeta            | 
| wp_comments               | 
| wp_links                  | 
| wp_options                | 
| wp_postmeta               | 
| wp_posts                  | 
| wp_term_relationships     | 
| wp_term_taxonomy          | 
| wp_terms                  | 
| wp_usermeta               | 
| wp_users                  | 
+---------------------------+
12 rows in set (0.00 sec)

Hmm.. they're there too. Maybe the tables?

 
mysql> select * from wp_posts
    -> ;
ERROR 145 (HY000): Table './chris_wordpress/wp_posts' is marked as crashed and should be repaired

Uh-oh. I think this is going to be ugly. And yet...

The magic fix

...a quick check here on google gleaned the fix - MySQL's got a tool for just this occasion, and you call it like mysqlcheck broken_database from the command line:

[root@stemcaa2 ~]# mysqlcheck chris_wordpress
chris_wordpress.wp_ak_twitter                      OK
chris_wordpress.wp_commentmeta                     OK
chris_wordpress.wp_comments                        OK
chris_wordpress.wp_links                           OK
chris_wordpress.wp_options                         OK
chris_wordpress.wp_postmeta                        OK
chris_wordpress.wp_posts
warning  : Table is marked as crashed
error    : Size of datafile is: 745472         Should be: 745692
error    : Corrupt
chris_wordpress.wp_term_relationships              OK
chris_wordpress.wp_term_taxonomy                   OK
chris_wordpress.wp_terms                           OK
chris_wordpress.wp_usermeta                        OK
chris_wordpress.wp_users                           OK

The fix was trivial from here - just pass in an auto-repair flag:

 
[root@stemcaa2 ~]# mysqlcheck chris_wordpress --auto-repair
chris_wordpress.wp_ak_twitter                      OK
chris_wordpress.wp_commentmeta                     OK
chris_wordpress.wp_comments                        OK
chris_wordpress.wp_links                           OK
chris_wordpress.wp_options                         OK
chris_wordpress.wp_postmeta                        OK
chris_wordpress.wp_posts
warning  : Table is marked as crashed
error    : Size of datafile is: 745472         Should be: 745692
error    : Corrupt
chris_wordpress.wp_term_relationships              OK
chris_wordpress.wp_term_taxonomy                   OK
chris_wordpress.wp_terms                           OK
chris_wordpress.wp_usermeta                        OK
chris_wordpress.wp_users                           OK
 
Repairing tables
chris_wordpress.wp_posts
info     : Found block that points outside data file at 742932
status   : OK

And then all was well again! Why can't technology always be this easy to fix? A huge, huge heartfelt thanks goes to Felipe Cruz for adding such simple instructions on his site explaining how to use that insanely handy mysqlcheck command.

7 thoughts on “How to fix a WordPress site when the database corrupts on you.

  1. Hey, I just had something similar happen to me. My host’s toolbox had a “repair” operation that fixed it with no hassle – but I’m suspicious that this was all caused by some hacker trying to do an injection attack. Do you have any indication that your db and server are working correctly after this incident?

    1. To be honest, I’m pretty sure this was more down to me leaving some site logs to grow too large on another site than any clever hackers getting into this site.

      Once I saw where the disk space was going on this box, it was pretty easy to see the culprit…

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>