Initial D World - Discussion Board / Forums
   
Welcome Guest ( Log In | Register )Resend Validation Email

DJ Panel ( Server Stats )   Song History   Initial D World Chat Room (IRC)   Broadband Stream
RADIO BROADCAST » streaming at 96kbps with 7 unique listeners, playing Mood Music

   

 

Views: 3,622  ·  Replies: 2 
> Routine Maintenance Gone Wrong, Details explained inside
Perry
  Posted: Aug 18 2007, 12:59 AM


Like an eagle!
Group Icon

Group: ADMINISTRATOR
Posts: 7,657
Member No.: 1
Joined: Sep 15th 2002
Location: San Leandro, California





Around 4:00AM (GMT -08:00) August 17th, 2007, I was doing some database maintenance. Repairing, moving, updating tables left and right. Not sure if I was too tired at the time, I made a terrible mistake by mistyping the syntax of a MySQL UPDATE command. Immediately, all topics went missing because one of the columns had been nulled. For the first time, I felt panic for a couple of minutes, fanatically trying to find out what went wrong.

I found the problem and immediately wrote a script to recover most of the tables from an old backup I made awhile back. The 400 topics that could not be recovered automatically had to be manually recovered. Basically, I went through all those 400 topics and assigned the correct value to the aforementioned column in them individually.

It wasn't an easy task because sometimes the topic title and topic description can be so vague that you just have no idea what section they belong to. Thus, I have to go in to each topic manually and skim through a few posts to get an idea what the topic is about. As a result, some topics might not get assigned to their original section. If you see a topic that doesn't belong to the correct section, please contact the staff and ask them to move it to the correct location. Thank you very much for your cooperations and your patience. And I apologize for the inconvenience you may have experienced on the forums during these times.


The moral of the story here is, always make backup copies of your data and don't mess with SQL when you are tired. You could make a very small mistake that end up in catastrophic results.


P.S. - If you have missed what happened, here is a screenshot for reference.

user posted image
Proud Contributor of the Music Section Revival Project
Perry
Posted: Aug 22 2007, 05:45 PM


Like an eagle!
Group Icon

Group: ADMINISTRATOR
Posts: 7,657
Member No.: 1
Joined: Sep 15th 2002
Location: San Leandro, California





To minimize errors made by human, I've wrote several database routine maintenance scripts to handle the task. I've been testing the scripts lately and so far it's proven to be error-free. These scripts are extremely efficient in what they do. A normal routine database maintenance takes about 45 to 50 minutes, by hand. The scripts can do the same task in just under one minute. Because of this, I've scheduled the scripts to run itself every morning when the least amount of users are on.

In other words, there won't be any more downtime when there is a scheduled maintenance, as the database tables are locked automatically (to prevent any new data inserts) during the one-minute maintenance, as instructed by the scripts. If you are experiencing slow-down on the forums around 03:30am Pacific Time (GMT -08:00), it's most likely due to the scheduled maintenance and the slow-down will go away within one minute.
Proud Contributor of the Music Section Revival Project
Möbius
Posted: Aug 23 2007, 05:36 PM


IDW Prime Member
**********

Group: Advanced Members
Posts: 33,507
Member No.: 3,524
Joined: Oct 2nd 2004
Location: Update Profile





Just want to put in a bit of a clarification. Remember how the regular maintenance required a lot of time, and the need for a temp forum ( because of the long downtime ) ?

That's a thing of the past now.

Many thanks to Perry. smile.gif