Hopping mad
posted in Computers, Work |(Technical stuff follows. Feel free to ignore.)
Oooooo!
So there we are at work…our transportation center has an online reservation system that was written by a Nice Young Intern. It was written back in 2002 and has worked okie doke since. (Aside from the fact–which I discovered recently–that it has been running on the development server all this time, rather than the production server. To those of my readers to whom that is an arcane distinction, let me just say: production servers have paging systems. If the production server goes down in the wee hours of the night, some poor ITS minion is paged and required to dash in to the office to Figure Out The Problem Right Now! This does not happen with development servers. If a development server goes down, the priority to get it going again is low on the totem pole. Also, DBAs feel quite happy to Do Things to development servers, without worrying that they’re going to break something.)
Recently, we’ve been getting complaints from our TC that the users of the online reservation system have been getting errors.
I investigate. Luckily (or not so much ‘luckily’ as ‘almost inevitably’, as it turns out), I immediately get an error page.
(Let’s set aside the fact that there’s no error checking, so we don’t have a “nice” error page telling our users that oops, there’s a problem, and please try again later??)
The error page says “unique constraint violated’. What the heck? Why would that happen?
Interestingly enough, the DBAs had just updated the development server shortly before the TC folks really pushed us about this error. So I went down that path for a while. But a DBA, when emailed, provided a clue–he said that we’re trying to insert new rows with a duplicate primary key. (A ‘primary key’ is a number that uniquely identifies a row in the table. For instance, if you’re doing a credit card transaction, the primary key might be, say, your transaction number. No-one else is going to have that transaction number…or no-one else will have that transaction number on that date, so the primary key would be trans number plus date.)
I noodle around.
I investigate.
I discover, much to my absolute and utter horror…
The Nice Young Intern had set up the reservation numbers as the primary key. This is okay.
The NYI had an automatic number generating doodad set up in the Oracle database to generate those reservation numbers. This is okay.
The NYI had not used the default maximum limit for the reservation number–which would be some gawd-awful number like 999,999,999,999.
No.
The NYI created the reservation number system to have a maximum number of 9999. This is not okay.
So…once the TC had gotten reservation number 9999, what happens?
The automatic numbering system starts all over again, at number 1.
Back when the system was being developed and tested, there were loads of jumps in the numbering system. NYI would try a reservation, it wouldn’t work, he’d back out, that reservation number would be discarded…But as the system went online and real people started using it, the gaps in the numbering system would become fewer and fewer in number.
The first few hundred numbers worked okay. Users would get an error once every great while, when the system tried to save a reservation that had a reservation number already used in the system. But now…now…the gaps in the numbering system are few and far between. Thus, as I said above, it was almost inevitable that the test reservation I made would not go through.
Why the fuck would someone create a system keyed on an automatic number that rolls over when it hits 9999?! This is like our own little tiny version of the Y2K problem.
And I’m hopping mad about it.
Luckily, it’s an easy fix.
Grumble, grumble, grumble, bitch, moan, complain.
