Wednesday, July 30, 2008


If you can read the title then you know what I'm talking about.

We use an application which allows many machines to log in with the same user. This application automatically saves changes on a database table and "locks" stuff in a boolean column called locked from the same table (ie, no native locks are actually used). I bet you can imagine the potential for disaster this combination allows.

So, I've been pretty much the last hour very busy trying to manually restore the state of a corrupted database. I see you, my non existent reader, immediately asking things like:

a) Why didn't you restore a backup, moron?
b) Do you know squat about ACID?
c) Do you, in general, have a clue at all, asshole?

a.- Because backups are done nightly and the lost work was from this morning. (This reminds me
I should set up some transaction log backups so as to lose at most 15 or 30 minutes, have to buy more hard drives!)
b.- I have no control over the application, sadly. I must report these bugs and hope they fix them. Proprietary world hell...
c.- I have to!

Well, current disaster is: some values disappear as the application loads values from the DB at startup, does changes locally in memory, then saves upon exit or at regular intervals. Two users sharing credentials doing this and only the last to exit will get its values saved.

Of course I'm telling people not to use the same credentials, but we all know how we are so that's not a safe solution.

I really hope there is some reason besides 'users should not share credentials' for all this nonsense.

For my future reference, I must use:

"SELECT * FROM scInfoTbl WHERE father NOT IN (SELECT cId FROM scInfoTbl)"

to find which cs are orphans. Orphans kill the application, as it is not ready to handle
an orphan c (crashes with a NPE).

As you can deduce, scInfoTbl defines a tree of c, with the father column pointing to the parent leaf (or root) of the tree. Of course a leaf cannot be an orphan, so why check it? Grr...

No comments: