One of the intriguing things about newborns is their propensity
to attack themselves. After a few weeks their little hands begin to take on a
life of their own: flexing, probing, grabbing. Since their stubby little arms
are so short, there isn't much territory to explore for those little hands, and
they invariably end up by the head, ears, face, etc.
There's nothing quite like seeing an infant's little hands grab an ear. It's
like watching body parts from two, unconnected, people. The little hand finds
the ear, and then GRABS on to it. The child, who has been minding its own
business, realizes that something isn't right. The pain that their little hand
inflicts upon them causes them to tense up. The sad part is that where their
hand was, previously, acting as a seperate entity, it too now tenses up. Within
a matter of a second, a happy, cuddly little newborn goes from a world of
innocence to one where they are being attacked with unmerciful cruelty. (And
trying to get them to let go can be a chore too.)
Sadly, newborns aren't the only people susceptible to such self-inflicted
unmerciful cruelty. DBAs occasionally attack themselves in the same way.
Background: Once upon a time in the land of IT there lived a
huge, hideous, beast of a website. Like Frankenstien, it had been cobbled
together from various 'parts,' both living and dead. It had, however, been
cursed with a fair amount of success -- to the point where a complete
reinvention would have been out of the question from a business standpoint. So,
this frankenweb was therefore given yearly sacrifices of developers. Entire
teams would be built up around this monster, only to be gobbled up every few
years. Only rarely would someone escape, by running, screaming out of the
building (at which point they would be stuck with massive bills from their
shrink as they tried to cope with the futility that was theirs while they tried
to subdue the beast).
As it turns out, a modification needed to be made to the site one day.
Modifications were par for the course; they could be sewn directly on to the
outer hide of this frankenweb, further increasing technical debt, but giving
business an empty, and pointless, feeling of accomplishment.
There was, for the longest time, no suitable dev/testing/integration
environment, and as it happened, a sproc needed to be changed out in production.
A time window was selected, when the site was only making about $40k/hour, and
the change was to go in to place.
Never one to just wing it, I had done my best to create a suitable test
environment at the last minute, and had tested the functionality. It all worked
fine in my test environment (that wasn't an exact match of the production
environment.)
As the production DBA it was my responsibililty to push the change to the
live site's database. Four or Five coworkers sat at the ready (at their
consolses) as I made the change -- and they quickly confirmed my worst fear:
"The site is broken, I can't add things to the cart." said one, and then
another...
My fingers had just found the soft lobe of my right
ear...
Damn. Well. No biggie. Stay Calm.
There was no way I was going to let this
hinder me -- I was better than this... I had a rollback script. I fired it
against the DB/Site. It executed perfectly.
"It's still down. I still can't add to the cart." reported a
coworker.
"I've got problems too. No go." said another.
Sure enough. No dice. Functionality was broken.
I tensed up. The pain in my earlobe was excruciating. Someone was
attacking me with unmerciful cruelty....
What followed were a few minutes of stark raving horror. About 12 minutes en
toto. Eventually I was able to calm down and think (after I got my
pacifier). The only thing that made sense was that when I had done my
rollback script I had intentionally done a DROP and a CREATE on the sproc in
question. I wanted to make sure that there was no way that older logic
'remained' and tainted the works.
Now the problem with doing a DROP/CREATE is that the old sproc goes away. All
of it. Including permissions. I KNEW this. (When you do an ALTER, the sprocs
objectID persists, and any perms on it remain.) I also 'knew' that it wasn't an
issue -- because as frankenweb as the site was, I knew that the whole thing
sadly ran as the sa.
Only, the only thing that made sense was that this was some sort of a
permissions error. A few quick checks later, and I was able to confirm, to my
continued horror -- although it quickly spread to relief, that the piece of
functionality in question actually DID execute as a user other than sa.
Analysis of the problem afterward showed that: the initial change would
have worked, the webservers should have been reset was all. Doing the rollback
is what HOSED me.
Moral of the story:
1) Babies are
cool.
2) Test in a REAL environment. Don't settle for
unmerciful cruelty. (My second priority, after getting the site to run as non
sysadmin, was to work on creating a perfect test/integration environment -- I
never got there completely, but got close enough for comfort before I ran
shrieking from the building.)
3) Rollback scripts are a MUST. Only
make sure you script the permissions, or JUST use ALTERs (unless you
want to cry like a baby when you attack yourself unmercifully.)