This article uses Project Open 3.5.0.0.1 for the example code. The technique described here has also been tested against 3.4.x.
Project Open is an enterprise-grade project and help desk management system. It’s web-based. One would think that out of the box, it would be completely multi-user aware.
Surprise!
It’s not!
Before we understand this (easily correctable) insanity, please indulge me as I describe for the younger generation just what “multi-user aware” means. In ancient days, before Local Area Networks (LANs) were popular, we used tools like dBASE III+ to build database systems on operating systems like IBM PC DOS 2.1. When LANs came along, we naturally wanted to share these applications using massively advanced programs like the IBM PC LAN Program and Novell Netware 3.11. Unfortunately, many of our dBASE III+ applications would crash and burn if two people tried to hit the same data at the same time — or, heaven forbid, try to update the same data at the same time. In other words, these applications were not “multi-user aware.” Modern Relational Database Management systems (RDBMS) like MySQL or Oracle don’t have these problems.
You’d think that in the year 2011, web-based applications, which are born in an environment that’s naturally multi-user, would be able to handle such things as two people adding a ticket at the same time. Alas, Project Open, out of the box, fails at that task. If you’re curious, you can easily repeat the problem.
Open two browsers like Safari and Chrome. In Safari, log in as user A. In Chrome, log in as user B. With user B, start to enter a ticket, but stop on the new ticket screen (in other words, don’t submit it). With user A, start and finish entering it. Switch back to user B and finish entering the ticket.
Gasp in astonishment when you get an error like this:
Unable to create ticket:
Ticket Nr '4124' already exists.
I was aghast. But there it was — as plain as day.
The problem is this module:
/web/projop/packages/intranet-helpdesk/tcl/intranet-helpdesk-procs.tcl
If you look in the code, you’ll find this block:
# return [db_nextval im_ticket_seq]
set last_ticket_nr [db_string last_pnr "
select max(project_nr::integer)
from im_projects
where project_type_id = [im_project_type_ticket]
and project_nr ~ '^\[0-9\]+$'
" -default 0]
# Make sure the counter is not behind the current value
while {[db_string lv "select im_ticket_seq.last_value"] < $last_ticket_nr} {
set ttt [db_string update "select nextval('im_ticket_seq')"]
}
return [expr $last_ticket_nr + 1]
Notice the last line. It returns, as the new ticket number, the current number plus one. But it doesn’t update the database with that new number! That leaves it open to cause the problem I describe above.
But even more inexplicable is the commented out line near the top: the line that calls the stored procedure db_nextval im_ticket_seq. That stored procedure does update the database. In fact, it’s the fix! Here’s a fixed version of the code:
return [db_nextval im_ticket_seq]
#
# set last_ticket_nr [db_string last_pnr "
# select max(project_nr::integer)
# from im_projects
# where project_type_id = [im_project_type_ticket]
# and project_nr ~ '^\[0-9\]+$'
# " -default 0]
#
# # Make sure the counter is not behind the current value
# while {[db_string lv "select im_ticket_seq.last_value"] < $last_ticket_nr} {
# set ttt [db_string update "select nextval('im_ticket_seq')"]
# }
# return [expr $last_ticket_nr + 1]
If you’re feeling astounded and are wondering if you’re reading the code right, let me put your mind at ease: you are. All I did to “fix” the problem was uncomment the working code and comment out the multi-user unaware code. Once I made that fix, all I had to do was stop and restart Project Open, the problem went away.
I have no idea why both versions of Project Open that I’ve worked with (3.4.x and 3.5.0.0.1) shipped with the same broken code. I’m sure there’s a good reason that I’ve just not yet discovered. But instead of spending too much time wondering about it, I just thanked my lucky stars that the fix was so easy and went about making sure the rest of the system worked.
I’m glad I found this before a massive roll out. That would have been really embarrassing!
Note: I recently had occasion to look at the CVS (source code repository) for Project Open 4.x, and it looks like the developers have fixed this issue in that version. It looks like 3.5.x may be the last version that has this issue.
