We have a small CGI program that we use for updating a small MySQL database. It's very low traffic, with just one person posting about one update per week. Recently, we began adding some enhancements to it -- and suddenly we lost the ability to edit existing records.

The behavior we're encountering is that, after updating a record, the program is unable to read that record back until the program exits and is restarted. When a Class::DBI object issues an "update" (i.e. $obj->update), the object is destroyed as a means to force you to then retrieve the object back from the database (thus ensuring that the object in memory accurately reflects what is in the database).

So we do something like this:

$@ = '';
eval {
    $order = TblOrders->find_or_create(
                order_id => $order_values->{order_id},
    );

    [... update columns here ...]

    $order->update; # THIS WORKS
};
confess "Could not update order: $@\n" if ($@);

# THIS NEXT BIT FAILS
$order = TblOrders->retrieve($order_values->{order_id})
    or confess "Could not read back order with "
    . "order ID = $order_values->{order_id}\n";
[download]

Simple. Except that when we do that, $order doesn't get retrieved. Even if we explicitly hardcode the key into the retrieve statement, no record is returned. If, on the other hand, we hardcode a different key into the retrieve statement, then a record is returned. It isn't the record that was updated, naturally, but it shows that the retrieve statement works.

What this seems to indicate is that perhaps the record is being locked when we update it and isn't unlocked until the program exits. (We can view the updated record without any problem after the program exits.) Just to see if it was somehow a timing problem, we placed a 5 second sleep after the update. No change in behavior.

sub store_order {
    my ($self) = @_;

    my $order_values = $self->prepare_data_for_database("orders");

    my $order;

    if ($order_values->{order_id}) { # Replace an existing order
        $@ = '';
        eval {
            $order = EN::OrdersDB::Orders->find_or_create(
                order_id => $order_values->{order_id},
            );

            # Update the fields of the object
            foreach my $field (keys %$order_values) {
                $order->$field($order_values->{$field});
            }
            $order->update; # Update the database
        };
        confess "Could not update order: $@\n" if ($@);

        # THIS NEXT BIT FAILS
        $order = EN::OrdersDB::Orders->retrieve($order_values->{order_
+id})
            or confess "Could not read back order with "
                       . "order ID = $order_values->{order_id}\n";
    }
    else { # Insert a new order
        [ ... code to insert new order ... ]
    }
    return $order;
}
[download]

I'm stumped and extremely frustrated, to say the least. I've spent the entire day trying to fix this bug, without any success. If anyone can help, I'd really appreciate it.

(Oh, and lest anyone shudder in terror that such a poorly written program is being used to take credit card orders of the Internet, don't worry -- the "orders" are court orders being stored in a database for easier access; it has nothing to do with money. Luckily.)