Record deleted - no user action, no DELETE in server log

Classic Servoy
1

Very strange occurance.

Had an ORDER record, was looking at it with another employee. It was 3 weeks old. Another employee also viewed it.

5 min later, the record is gone - no trace of it in the system. Direct queries to the database show no record exists. Backups from 2 days previous show the record is there.

We have a paper printout from Servoy, showing the record ID - ID no longer exists. All child records are gone, too (orderitems). There are NO database cascading deletes set, so servoy must have done this yet it shows no history of performing a delete.

Checked servoy performance log immedietly and no DELETE actions are noted for the entire day.

Checked the servoy server log, and no errors show (a few connection erros from 3-4 hrs previous).

This happened once several months ago and we chalked it up to user error - as it had been a week before the discovery was made, and so couldn’t follow up on logs and such as well.

Anyone EVER seen something like this?? Where else can we look to track down the source of this?

The form itself has only one delete function ( but will not delete if the record is locked - this one was locked -and prompts to delete as well). This method replaces the default, so there should be no other way to delete this record. No relations have cascading deletes set.

Besides, the server log should show a delete in the history…and it doesn’t.

Any help appreciated. This is a big concern, as we can’t have records just disappearing! :slight_smile:

But this is only the 2nd time in 1.5 yrs, and just don’t know where the fault might lie.

Thanks for any insight into how to troubleshoot this further.

PS attempted a few USER initiated deletes and other actions (deleting related records, etc to check for any missed cascading deletes.) on a dev machine, and all attempts resulted in a clear indication of the delete in logs and log_server.

NCM
FSCI

FYI:
Version 3.1.6-build 410
Java version 1.6.0_01-b06 (Windows XP)
MySQL with updated driver (works fine 99.9999% of the time)

10,000 orders, 2 of them have disappeared in the same manner. We’ve had 4 new people trained in that time, so user error should be higher if that’s what it was.

2

Have you checked your database transaction log? Not sure how that works with mysql but I assume you are running a transactional mysql? With ianywhere it would be very simple to figure out in the transaction log when the delete was run.

3

We’re using innoDB tables (transaction able) but we don’t code for or perform transactions in servoy itself.

Does servoy send SQL as transactions? Even if transactions aren’t coded into the methods?

NCM
FSI

4

In a transactional database everything is a transaction. This is to avoid data corruption: lets assume your power fails in the middle of an update: using the transaction log the server can figure out where it was when it crashed and redo/undo the transaction. With a log to sql conversion tool you should be able to see what happened when.

5

Found the delete. It has no time stamp, but two mod and create dates of transactions directly before and after are shown:

2007-08-21 10:37:21 - someone performed an edit on an unrelated record

delete from fs_order where fs_order.fs_orderid = 26470

2007-08-21 10:43:03 - someone created a new record in another table

Immedietly following the delete, it of course deletes related orderitems, related returns, related payment data (due to relationship deletes in servoy relations)

I looked at other legitimate deletes, and they have the same pattern EXCEPT you see the user create it first, then immediately delete - ie, inadvertant create.

In this instance, the create was done 2 weeks prior. There is no update (in the same or previous 2 days) merely a delete.

So, it looks as if the delete came from servoy (since the related deletes were done as well) but there was no DELETE sql for anything the whole day in the servoy server logs (performance log, etc) or log_server tracking.

Where does servoy pull the performance log data from? Shouldn’t this show the delete? It does when I delete from a dev machine, but obviously don’t want to test on the live server.

I’m also going to check the two users machines and see if I can glean anything from the java logs.

Anywhere else to look for clues? Or to verify that is WAS user error and not a spontaneous delete?

NCM
FSI

6

My recommendation would be to immediately turn on Servoy auditing. That way you can see if a user is doing this and the sequence of events. Also I’d add some logging to any place where a user can delete to ensure when this happens. I

7

Thanks. Will do, uploading a new version now with a few more checks and custom logging - another table is updated with timestamp and user if anything is deleted.

Previous checks (ie, a custom record locking scheme) are still in place.

I’m still confused as to why, if it was user initiated, it doesn’t show up in the performance log. No deletes show at all.

Any reason why there would not be an entry for delete type in the performance log?

Thanks for taking the time to help out.

NCM
FSCI

8

Deletes are not written in the performance log. They are written to the tracking table if auditing is on and also to the servoy log-file if tracing is on. I’d investigate the following:

  • Where can users delete data and is that area fully audited
  • Are there any triggers setup in the database that might be deleting data?
  • Do other applications have access to that database? If so they might be involved?
  • Any cascade deletes setup in the app or in the database that could cause it?
9

Deletes DO show in the performance log for developer.

At least in 3.1.6 (we haven’t migrated to 3.5 yet).

I guess that’s why the confusion.

If, in developer, I delete any record, I see an entry in the performance log of type DELETE.

On the server, there are no DELETE types shown in the performance log.

So, developer and server are not consistent in this regard - and led me to believe that the delete was somehow in error.

I’ve tightened up the delete method for critical items, added audit tracking to relevant tables, and no longer look for DELETE’s in the performance log.

As this is an isolated incident, I’m chalking it up to user error and will monitor for future occurances.

Thanks for the assistance!

NCM
FSI