We Screw Up So You Don't Have to...

Three Basic Things You Can Do To Avoid Big SQL Mistakes

We screw up so you don't have to...

My first job was at a local grocery store, cleaning the meat department after their bloody day of cutting, grinding, and packaging was complete.  Not for the squimish, certainly, but nothing too difficult.  After a couple of days on the job, I had everything down to a science and would usually be out the door within a couple of hours.

Part of that daily process involved cleaning out the deep fryer used by the in-store deli.  You emptied the grease into a machine that filtered out all the day's crap, cleaned the fryer from top to bottom, then pumped the filtered grease back in.  To get the grease out, you'd unscrew a bolt on the bottom just like you might when changing the oil in your car.  All pretty straight-forward.

When they trained me, they warned me that some day I'd forget to put that plug back into the bottom of the fryer before pumping the grease in.  I laughed.  Who would be so stupid?  I'm a freakin' Mensan, I think I can remember unplug, clean, plug, fill.  

Since you're smart enough to read Nerdliness.com, I'm guessing you're smart enough to figure out what happened next.  Couple weeks later, I forgot to put that damn plug back in before flipping on the pump.  Turned my back on the machine to continue working on something else for a few minutes before I noticed that several gallons of grease were covering the meat department floor.  Not good times.

Which, of course, leads us to...  databases.  Stick with me, it'll make sense in a minute.

If you work with databases long enough, you're going to screw something up.  And it's not going to be a small something, oh no.  It'll be big.  Like you'll accidentally DROP a table as the accounting people are shoring up the books at the end of the fiscal year.  Or maybe you'll forget to complete that WHERE clause and DELETE all the rows in your table rather than just those you meant to specify.  Sorry, pal.  

Couple things that'll help you get through it.  First and foremost, of course, is have a good backup, know where it is, and know how to restore it.  Ok, maybe that's three things, but you get the point.  Even though our normal backup routine generally runs pretty well, I like to run another one right before I do anything that could screw things up badly.  Doesn't have to be the entire database, just the data you're working with is fine.  Just have a way to get back to Point A.

Second thing, and this is really the crux of this post, is to know the company.  We nerds, geeks, and techs have a bad habit of becoming isolated from our coworkers.  We sit in our cubes and our server rooms and try to convince ourselves that we don't have anything to do with accounting, or marketing, or shipping.  They do their thing, we do ours.  Unfortunately, that can lead to problems like the one I was working on today...

I spent most of my day debugging a stored procedure that blew up, erasing 10s of thousands of dollars of inventory from a database because I didn't follow my own advice.  It didn't freak out because of a coding problem, no sir.  It worked exactly as I planned.  Unfortunately, I made a bad assumption based on my limited interaction with other people in the company, so my plan was flawed.

See, the warehouse stores Items in Bins, and each Item and Bin has its own unique ID in the database.  Items can be located in different physical Bins throughout the warehouse.  For instance, a certain quantity of Item A might be in Bin 1 located near the area where the people packing boxes work, while overstock of that same Item might be held back in Bin 2 at the rear of the warehouse.  Makes sense, right?  Kinda like how the grocery store keeps some product on the shelf and some in the back.  Same idea.

Well, what I didn't realize was that an individual Bin can contain more than one type of Item.  In other words, that overstock Bin 2 might also have some of Item B in it in addition to Item A.  I assumed, though, that there was a one-to-one relationship between the Bins and Items, that any individual Bin would only contain one specific Item.  See how this gets bad real fast?  

Let's say my stored procedure was supposed to subtract 10 units from the stockpile of Item A in Bin 2.  Since I thought there was a one-to-one relationship, I wrote an UPDATE query that looked something like so:

UPDATE bin 
SET quantity = quantity - 10 
WHERE bin_id = 2
 

Uh oh...  That definitely did what I wanted:  it subtracts 10 units from Item A.  Unfortunately, it also subtracts 10 from Item B, too.  Now, expand this idea and consider that this same type of query ran on a table 1200+ times.  Bad juju.

While it wiped out thousands of units of inventory from the database, the increments were just small enough to slip by until, days later, one of the warehouse people realized that he had hundreds of units a product on the shelf that the system told him was out of stock.  By that time, though, so many transactions had run against these tables that fixing the numbers was extremely difficult.  Everything's back together now, but only after several people (myself included) spent hours pouring through records and physically counting inventory.

And here's the worst part.  If I knew the business flow better, I would have known about the Bin thing.  I was one phone call and 17 keystrokes later ("AND item_id = 'A'") from saving hours of time and hundreds of pulled-out hairs.  

Finally, here's you bonus third thing-you-can-do-to-put-off-the-inevitable-screw-ups-as-long-as-possible:  get someone else to test your stuff for you.  I tested my stored procedure on a dev database before I rolled it out live, but I knew what I was expecting to happen.  When I ran the stupid thing, I only looked at the Items I thought she be changed.  When I noticed they looked good, I thought everything was fine.  I didn't think to look at the other records in the table...  A quick SELECT query comparing my before and after results would have quickly shown that something was jacked, but I had my blinders on.  Don't do that.  Get someone else to check your work.