That Time I Deleted a Database

I’ve been hobby programming for several years that has spilled over into making applications for staff at school. One of those tools is a website to manage PD events, signups, and documentation. This week, I accidentally deleted that database.

I don’t want to downplay how bad this was, but it also isn’t on the scale of losing student or financial data. But, it was roughly eight months of events, signups, and more importantly, participation records. Several things went wrong, all of which I should have caught at some point:

  1. My database migrations (history of changes to structure) didn’t match between my computer and the server.
  2. I didn’t double check the data in the database dump I had made before re-importing it.
  3. I had an artificial deadline in my head and I didn’t slow down in the process of the changes.

We’re constantly trying to follow best practices when it comes to accessibility. The change I was introducing allows staff to request accommodations on their registration so the presenter knows to do one thing or another. In addition to our general presenter guidelines, this helps make sure everyone’s needs are met in a non-obstructive way.

I learned some things…

First, instead of trying to force the migration history to reconcile, I should have slowed down and fixed the root issue. Those migrations files are critical to making sure everything moves around in a way that can be reversed and repaired. Instead of reconciling the differences, I made some quick changes to which steps were related to one another and that contributed to the problem in the first place.

Second, I assumed the database dump from last night was good enough. I didn’t check to see that it actually had good data in it. As it turns out, the dump was blank – and that means that when I reloaded it back into the database, I effectively erased everything. Since that was my only backup, there was no chance to roll it back to a previous state. So, now we’ll be doing nightly backups as well as dumps right before migrations to make sure we have at least one good copy. I’ll also be checking the file directly to make sure it holds information, period.

Third, I wanted to get the update in place. It didn’t take too long to make and the code itself wasn’t too complex. I wanted to say it was done and be able to move on. Instead, I ended up giving myself about 10 more hours of work to piece information back together and then work out ways to safely integrate everything back together. Deadlines are always flexible, especially when you’re setting them yourself.

I did have one stroke of luck in this whole ordeal: when a session is created, a Google calendar event is automatically created. When someone registers for that event, they are also automatically added to that event as a guest. Since this only affected the site database and not the Google Calendar events, I was able to use some quick-and-dirty Google Apps scripts to restore users, events, and their registrations. The only thing I couldn’t restore was attendance data.

Scripts

The first task was to get all the events on the calendar within a date range. I like using the Calendar v3 API because it gives me access to more properties on each Event that we can throw into a spreadsheet.

Once I had this sheet, I was able to do things like repopulate all users, extracting their data from the attendees string and using the Admin SDK to look up their name and location:

In this case, I got lucky that most of what I needed was available in the Calendar. Knowing what’s available in Apps Script made this a partial loss rather than a total loss.


The featured image is Eraser Shavings + Erased Text by mujalifah is licensed under CC BY-NC.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

css.php