SQL Table Failure

[kwpsd]

Servoy 7.4.6
Smart Client

A couple of weeks ago, one of our customers encountered a table failure that we are unable to explain.

Our solution manipulates a table of research Protocols with each record having a unique Protocol Number (the Protocol Numbers are not PKs). While the customer was editing a Protocol record (but, not the Protocol Number), the solution locked up, thus forcing the customer to restart the solution. After restarting, the customer noticed that all Protocol records now had the same Protocol Number as the record that was being edited (verified using MS SQL SMS). No other column data was changed in the table...just the Protocol Number column. There were no errors in the log file.

The customer's Servoy application server connects to the customer's remote MS SQL cluster (i.e. Servoy and the SQL cluster are on different computers) via the MS JDBC driver, version 4. The customer's IT staff ran tests (ping, latency, load, etc.) between the server on which Servoy resides and the SQL cluster and found no anomalies. Fortunately, the SQL cluster is backed up every 15 minutes, so getting things 'fixed' was simply a matter of restoring to the latest back up.

We have repeatedly tested the solution code in our office and are unable to reproduce the problem. We also searched for code that would change the Protocol Number in bulk but found none. We use databaseManager.getFoundSetUpdater() function in other modules of the solution but not in the Protocol module nor do those existing function calls access the Protocol data.

What could possibly cause something like this to happen? Any ideas? Looking for answers...

[ROCLASI]

Hi Kim,

Hard to say what caused this. I guess the first question is what caused the lockup.

Assuming this is a database you control then the whole resulting issue could have been prevented by adding a unique constraint on that Protocol Number column. This way the database would enforce this rule whatever an application would try to insert/update. It will simply give database errors.
I know I sound like a broken record to some but I strongly believe that (business) rules about your data should be on the database layer and not (only) in the application layer. This also includes Foreign-Key constraints. Applying these rules have saved my bacon more than once and also exposed bugs in Servoy and/or my own code.

Not a direct answer to your question but perhaps it will help you in the future.

[patrick]

Servoy application server shows every SQL statement sent to a DB on the performance data as well. So if your solution has done this, it should show up in performance data (unless the server was restarted). Did you check there? Not that it would help to find out why this happened, but it could give you a hint towards whether the solution is to blame or whether it was "something else" that caused this.

[kwpsd]

Thank you both for responding and the ideas.

@Hi, Robert:

... I strongly believe that (business) rules about your data should be on the database layer and not (only) in the application layer.

Interesting idea. What is the best way to trap for the database errors when they occur? And, do you act on the errors or just log them?

@Patrick: I am aware of the SQL statement logging. However, in the moment of panic to get things restored (25+ users complaining), Servoy was restarted before thinking to look at the SQL statement logs. It won't happen again!

The customer's IT department has been stress-testing our solution for the past two weeks and are unable to reproduce what happened. We are waiting to see if the problem recurs (if ever).

[ROCLASI]

Hi Kim,

Interesting idea. What is the best way to trap for the database errors when they occur? And, do you act on the errors or just log them?

Database errors are just another exception type in Servoy so you can simply use a try/catch to capture these exceptions.
And since most (all?) database exceptions mean that your insert/update/delete failed you should rollback/revert any changes.
My code tend to look like this:

Code: Select all

try {
    // Start a database managed transaction
    databaseManager.startTransaction();


    // any inserts/updates/deletes
   
   
    // try to save any changes
    if ( !databaseManager.saveData() ) {
        throw new Error("Can't save record(s)");
        // NOTE: you could get all the records that couldn't save and output the exception to know what caused the failure to save
        // See databaseManager.getFailedRecords(foundset) for this
    }

    // try to commit any changes (some database managed constraints could kick in now)
    if ( !databaseManager.commitTransaction() ) {
        throw new Error("Can't commit data");
    }

} catch(e) {
    // Log the error
    application.output(e, LOGGINGLEVEL.ERROR);
    // revert any changes (in Servoy's cache)
    databaseManager.revertEditedRecords();
    // rollback the database managed transaction
    databaseManager.rollbackTransaction();
}

Hope this helps.