There is no real point to this article - it’s not one of my usually strong lectures and doesn’t feature a key take-away or call to action.
It’s just something hilarious that happened many years ago that I recently remembered.
I think it was 2002 and I’d joined a small company in the UK as a software developer. (This was before new companies were called startups and before joining new companies was cool). The company had designed a telecoms product and offered businesses unlimited calls for a low(ish) monthly cost.
At the time it was a revolutionary idea and we gained many customers quickly. We’d developed the billing system, we ran business development, we had a tiny call centre, everything.
Classic ASP
Our tech stack was largely Microsoft based. We had a classic ASP website and we ran on SQL Server. Everything kinda ran well, but what we lacked was any sort of contigency. We’d grown so quickly that no-one had had the time to think about disaster recovery or database backups or even source control.
Unit and integration testing didn’t exist, exception handling was minimal and problems were fixed as we found them - in production.
You can see where this is going.
The Billing Run
The single most important monthly event for us was the Billing Run. This essentially consisted of running a job that would calculate every customer’s bill and generate a document that would by electronically sent to an off-site printing location. From there the bills would be packaged into branded stationery and mailed to the customers. Once a bill was generated, it would be saved into a online location so that we had a PDF copy of what was created for reference.
The Billing Run was always a stressful time. Because we were largely hands-off after the job hard started running, we would always worry that bills would be accurate and that we were correctly calculating everything. (Deep down we knew something would always go wrong because we’d rushed everything).
Bob
My co-worker at the time was a wonderful chap. After we met we became close friends and for this reason he won’t mind me saying that despite being a software developer, software development was not his strong suit. I’ll do him the honor of protecting his identity and call him Bob. (His name was actually Bob).
Bob had the remarkable ability to… let’s say, break things. I’m not sure he actually meant to wreak such havoc but he had a wonderful track record of deleting things, losing things, dropping databases and tables, being late for work, forgetting to go home from work, forgetting where the office was. The list is quite endless.
UPDATE dbo.customers...
One day we had a request come down from the call center. A customer’s name had a typo and it needed fixing. The customer was upset. The operator in the call center couldn’t change the name because of a bug in the system. Naturally.
We were all fairly nervous because we were right in the middle of the Billing Run. Generally changing anything mid Billing Run as considered a bad idea. We were even scared to answer the phone during the Billing Run.
Anyway Bob took the call and heard the story about how Michael Jonson was missing an ‘H’, and was subsequenly excited to realize he could probably fix this with the minimum impact. A simple database update. It wasn’t like he had to make a code change and redeploy something, after all.
Anyone familiar with the majority of SQL tools these days knows that while you can be authoring large scripts or procedures, it’s also possible to highlight a portion of script and execute it in isolation.
Such functionality was Bob’s downfall that day, for Bob authored a statement such as:
UPDATE dbo.customers SET firstName=‘Michael’, lastName=‘Johnson’
WHERE id = 187222;
(Savvy administators will be wondering why our tables were in the dbo schema. My response is: Good point.
Secondly I need to point out that 187222 is an arbitrary number and does not, in the slightest, indicate the number of customers we had).
Bob then proceeded to take his mouse and highlight the statement and press F5, immediately executing the statement. Only he didn’t highlight both lines, just the first.
At this point, everyone in our production customer table had the same name.
These days my responses to mistakes are more professional, but at the time I burst out laughing. It was so… Bob.
A few short minutes later we got a call from the off-site printing location informing us that everyone’s bill suddenly had the same name. We’d need to rerun the Billing Run.
Obviously we didn’t have a very recent backup of the production database and Bob spent the next few hours tracking it down, restoring it, and correcting all the customer’s names.
By the time we could rerun the Billing Run, the billing period was slightly off which meant we’d need to include a letter in everyone’s billing telling them what had happened.
There are many lessons to be learned here. Bob didn’t get to learn any of them, he was fired immediately. That’s a joke of course, you can’t fire anyone in the UK. But things have moved on and thankfully development methodologies and general DevOps have many safeguards in place.
Still, I’ll always think of Michael Johnson when I press F5.
Top comments (3)
I did exactly the same one time, where I used to be "careful" by writing everything as SELECTs first before changing them to UPDATEs, making extensive use of the command-line history. I ended up writing
UPDATE users SET first_name='Martin'; WHERE user_id=12345. Clever me and my semicolons. It replicated 10,000+ changes to 300+ other sites and took down the whole system in short order. I tell that story in interviews when they ask for nightmare stories.Wow, that's one heck of a story!
Never use autocommit with SQL interpreters. After the UPDATE you probably will see the number of rows updated. If it's not 1 rollback, otherwise commit.
Always keep a historic table for each important entity of your system.