A stroll through a crashed MySQL database

Saturday became the day to install a new ceiling fan in our dining room.  It was quite an adventure, and ended up not going so well because we apparently bought the wrong type of fan.  We needed a flush mounted fan, but the one we bought can only be mounted with a down rod.  Frustrating to say the least.

What’s this have to do with a crashed MySQL database?  Well…

To wire up the fan I needed to cut the power to the upstairs, since that was the only place we could tap in to power.  Well, one of the many stupid stupid stupid things the guy [JACKASS] who owned the house before us did was make sure that the breaker panel in the basement was as inaccurately labeled as possible.  There were no breakers labeled anything close to upstairs so I had to play the flip the breaker and see what shuts off game.  During this excitting game I managed to flip the breaker my home server runs on.  Power was restored a minute later and the server booted back up, no problem… or so I thought.  I must have killed power to the server the exact second this ping logging script was doing it’s thing.

I wrote a small script last summer that runs once a minute.  During it’s run it does 2 simple things.  Pings my ISP’s gateway 1 time, and then records a time stamp and that ping time in to a database.  I wrote it to monitor some major bandwidth issues my shitty ISP (Newwave Communications) was having at the time.  While they eventually fixed the issue after 6 months of bitching, I keep the script running because it can be useful some times diagnosing network slow down issues.

When I checked the script’s graphing page later that evening it was obvious the script hadn’t ran since the power outage and subsequent reboot.  Well, I had a problem where it stopped running before, and it was due to an upgraded PHP issue.  For some reason, I got locked in the idea that this was the same issue cropping up, so I spent a good 10 minutes trying to figure out what wasn’t running.  The weird thing was it looked like it was running right.  I put some diagnostic screen echo’s in the script and verified it was connecting to the database.  So I was confused as to why it was getting a good connection that the insert query wasn’t inserting right.

Well, this script is so simple (20 lines of code), that I had neglected to put an “on fail show error” case on the insert statement.  Here is where I made my mistake… I had one on the connect statement, and at the time I had made the assumption that if I was connected to the db service, that I could insert data fine. I wrote in the check and immediately got an error showing my index was a duplicate.  Well, it took me a few minutes to diagnose this glitch too, because the key was not in the database, and I could not insert that key, nor could I delete that key.  So I ran a check on the table and BAM, there it was.  The database got flagged as crashed on the check.  After some quick googling I ran the appropriate MySQL command to repair the table (easily enough, REPAIR TABLE `table_name`) after a few seconds it gave me a success.  I fired up the ping script again and she was alive!

So I learned a good lesson here… always, always, always put the “on fail show error” clause on MySQL queries that would warrant such a case.  Just because you have a good database connection, doesn’t mean a simple quick insert will work right.  Had I done that originally, I probably would’ve saved my self about 15 minutes of investigating time.