I just had the most fun time ever with MySQL backing an instance of WordPress. I’m writing up what happened and how I “fixed” it – note the quotes – just so others are aware of it. Maybe someone has insight into how it happened or has a better idea of how to fix it for the future.
The Errors
The timeline for the error stretches back months, to an ill-advised VPS update on my part back in August or September, I think.
I use RamNode as a hosting provider; I chose Fedora as my OS (because it’s Fedora, and I like Fedora.) However, it’s limited to Fedora 20. I wanted to run something more current (since Fedora 20’s a little bit outdated) so I went through the update process — which broke everything catastrophically.
RamNode justified itself, by providing me with a system image backup (which isn’t, by the way, their responsibility – they just came through anyway. I highly recommend RamNode.) So I rebuilt the image. I copied all of the WordPress files back into place, and did the same for the MySQL database directories.
The truth is: it was MariaDB, not MySQL. All of the commands are the same and the database files are binary-compatible; I prefer MariaDB for social reasons. By the end of the adventure, though, I was using the community version of MySQL instead of MariaDB just in case it was something about the MariaDB release. Voodoo debugging on my part. 🙁
I got a few log sequence number errors there, but removing the old log files cleared that up, or so I figured. At the very least, I didn’t see any problems.
The “Fix”
Now let’s zoom up to November. My wife told me her site was performing very poorly, and since her stuff’s pretty important to me (it’s hers!) I took a look at the logs to try to figure out what was happening to the database.
It turns out that the log sequence problem was back, with a vengeance. Now every database interaction was firing off dozens of log sequence errors, resulting in the database being killed and restarted. No wonder her site was performing poorly.
I removed the log files again (to try to get the log sequence reset, because ignorance) and that didn’t fix anything; I tried to do a mysqldump
and it couldn’t even read the data. I’d get errors trying to make a backup.
This was not good.
I then took a copy of the backup (made via tar cvzf
) to a virtual machine (through VirtualBox, on a Fedora image). Here’s the odd thing: the machine in VirtualBox had no problem reading the database. Record counts were fine, all of the data was there.
I didn’t alter the files at all – but took the opportunity to dump the data (via mysqldump
again, this time getting a valid SQL dump.)
I went back to the VPS, and uninstalled the database, cleared the directories, and then restarted the database (i.e., exactly what I’d done with my virtual image). (This is when I switched from MariaDB to MySQL, incidentally.)
… and what happened, you ask? Well, the same thing – lots of journal sequence errors. Here’s a sample:
151115 15:42:11 InnoDB: Error: page 1749 log sequence number 632633543 InnoDB: is in the future! Current system log sequence number 510316477. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: for more information.
This was concerning. I was about to have to open up the MySQL files and alter them directly (which I’d been trying to avoid.) But I wanted to try one more thing, since I’d done the data dump on my virtual image…
So I shut down the database, and removed the data again. I restarted the database server, and created the database and database user.
Then I fed the SQL into the mysql
client, and ran some simple queries to see record counts and some data. No errors.
Then I restarted WordPress … and lo and behold, everything seems to be okay.
The moral of the story is, of course, to make regular backups and watch your servers… I’m hardly a MySQL admin, and clearly I need to get better about all of this.