Messed up big time

Ok, so I messed up.

On asktom, you have the ability to be notified by email when an update to your question is done. That is, when someone posts a “review” or I do a “followup”.

Well, over 5 years, lots of emails have changed – so when a question from 2 years ago is touched, the odds are very high that the email will bounce. Guess who it bounces to. That would be…. Me.

Ok, so I’m writing the second edition yesterday morning (and hating most every minute of it so far… The second edition is less fun than the first, harder to update than write from scratch). So, any diversion is acceptable. The diversion this morning – one too many bounced emails. I must do something about them.

I decide “I know enough, heck, I wrote it”, so I proceed to just update the repository of questions and answers – setting the notify flag to false for any question over 6 months olds (yes, that would be most of them).

Before I write the following paragraph, I want to make something perfectly clear, this is about me, it is not about anyone else. It contains no hidden meanings. It is something I will say a dozen times a week in writing, not as often as I’ll mention BIND VARIABLES perhaps, but close. So repeat: this is not about anyone but me. None of these articles have been about anything but what I wanted to say – on my own time, after hours, from my home, not on Oracle time. Anyone that thinks this is some of of dig at them is charging windmills like Don Quixote.

What I did was the functional equivalent of updating the data dictionary. As that is a big huge mistake, this was a BIG mistake, I knew it wasn't 'smart' – but it seemed “so easy”. I bypassed my own API’s and just did it. Huge mistake.

How many people noticed that I actually updated over 8,000 articles yesterday at 10am? All of them – same timestamp….

Problem was, I did not notice. In fact, it wasn’t until later in the afternoon that I received this email:

To: thomas.kyte@oracle.com
Subject: A very, very busy day ;o)

... all "Ask Tom" entries got updated on 26 Apr 2005 10am ...

(yes, that would be the Bozo the clown emoticon ;o) Max -- thanks for that ;)

Well, if you look for silver linings – this was, yes, yet another diversion from writing! However, it was quite the bummer since I got the email about 3pm – some 5 hours after the deed, and my DBA had the undo retention set a bit low (it is now much higher thank you very much). So, we restored to another machine, rolled forward to 10am, created a scratch table as select the primary key/timestamp from this table, exported that – ftp’ed it over to the real server, imported it, did the update and – hey, we are back in business.

Only it was a stupid thing. Because I knew too much and forgot even more. Forgot about that trigger (but remembered to disable it during the “fix it” update). I hate triggers -- I'm on a campaign to eliminate them where possible.

I’ll use the API next time, actually, I’m going to modify the code soon so as to not send updates to people after six months have gone by. It’ll really reduce the amount of bounces I get (I get so many, I don’t even look at them anymore – which means the improperly addressed email from me gets tossed too, which is bad)

Moral of the story: updating tables directly -- bad. Really bad. Even when you think you know what you are doing.