Chronicle of a (damn stupid!) bug

February 23 2009

mom said “test everything that could possibly break”. now, how to define “everything”? well, the story goes like this…

last week, me and Antonio were pairing trying to find out what was causing a very strange behaviour in our codebase. a batch process, loading data from a file and populating an “ATM transactions” table, should then put duplicate records on a “duplicate ATM transactions” table. so far, so good, we worked on stories like this zillion times.

when our customer’s proxy finally run the batch on a PREPRODUCTION environment, something went wrong. she then collected a log file, from wich we started investingating where the problem could be. what a big surprise reading “unique constraint XYZ violated” for “duplicates” table! could be? we put no contraints at all on that table! even more, XYZ contrainst was declared on “transactions” table, not duplicates!

at first sight, there was nothing wrong in the codebase; we then tried to reproduce the bug on our DEVELOPMENT environment too, with no luck. so, we decided to log more debug infos, such as “executing INSERT INTO transactions” and “executing INSERT INTO duplicates”. test all, commit, deploy and go!

after another run, this time on TEST environment, bug was there again, but at least we got more log to read. can you see? yeah, debug info was “executing INSERT INTO duplicates”, followed by the silly “contraint violated” exception! gosh! how could that be?

support from customer’s internal technical staff didn’t help much, we just find out that there were “something” different from DEVELOPMENT and TEST/PRE environments. very strange. anyway, we compared genearted DDL for tables on both environments, but, again, nothing helpful.

then light came, thanks to our new team-mate Roberto, our little Oracle guru!

on our daily standup, we let the team know we were with no ideas, so Roberto proposed himself for a pairing session with me (the team is working on few projects, on which we turn pairs on a weekly basis). after reviewing together (useless) DDL scripts, he then started showing me some magic with Oracle “reflection” stuff: queries on metadata, such as tables, contraints, and so on.

i was talking with a team mate, who just asked me something, when i turned back on the desk, and found Roberto with a smily face saying “Jacopo, i know what’s wrong, but you’ve got to wait for our 5 minutes break!”. arghh! couldn’t really wait!

you know, the problem was in the only part we didn’t test: customer’s internal stored procedure invocations, for creating database object alias!

we use incremental SQL scripts to recreate from scratch the database structure: we just skip stored procedures for grants and alias, because they cannot be run in DEVELOPMENT environment. we delay that feedback to a manual run (a.k.a. a demo!) in TEST and PREPRODUCTION environments. and that’s exaclty what we had: feedback on our broken SQL scripts. one of the alias was wrong, duplicates table was pointing to transactions table! it was probably caused by a copy and paste from another script (and that’s the saddest part).

so, how to define “everything”? simple, everything!


One Response to “Chronicle of a (damn stupid!) bug”

  1. Luca Minudel Says:

    I appreciate your story, it is very instructive to me.

    You reacted in team in a smart and effective way.

    > “test everything that could possibly break”

    even this will not prevent errors to happens again in the future and is not the most effective way to invest your efforts.

    But you still can count on your reaction and invest in that direction:

    > because they cannot be run in DEVELOPMENT environment

    strive to make the DEVELOPMENT/TEST environment more similar to the production one

    > we delay that feedback to a manual run

    anticipates the feedback loop some more

    >debug info was “executing INSERT INTO duplicates”, followed by the silly “contraint violated”

    improve ways to extract information from feedback and be happy to add a regressive test

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: