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.
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?
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…
Very nicely explained. Thanks!
Same exact issue, except after a repair our permalinks seem to be hosed…
hitting save in the permalinks section fixed it
Thank you, thank you, thanks!!! This helped me out tremendously.
Pingback: TBFH Blog. A blogsite for Nick Fennell. Anything and Everything. | TBFH Blog