Records disappear

[millmaine]

We have been having an occasional issue where records just disappear. It's been going on for almost a year now and we simply can't see what might be causing it. It is not common and seems to happen at random. Once every 2 weeks or so.

Our database has 33 tables. The disappearing records all come from the same table. It is hosted on a cloud based server with 17 clients in 2 cities (same time zone) accessing it.

Steps we have taken so far are:

• Disabled "delete record" from the Menu Set
• Implemented a dedicated "delete record" script that notifies the administrator when it is run so that the administrator can confirm with the user that the deletion was intentional.
• Checked that records can not be deleted via a relationship
• Tracked the timing of discovery that records have disappeared. This is difficult because it may be weeks or months before someone needs to refer to it, so doesn't really tell us when the record disappeared.

When the record is deleted it appears that related records are NOT deleted.

Help?
 

[ggt667]

If this was me I would disable delete records in the privilege sets, and implement a mark for deletion field, number or timestamp.

[David Jondreau]

Some thoughts...

Using privileges is a worthy idea.

I always like to ask, "How do you know what you think you know?" How do you know records were actually "deleted"? Could some data have been changed so those records still exist but aren't showing up in a find? Could a user or script have written over the data, so the record you're looking for is actually another record now?

Also, why wait months? Do a weekly check. Or even daily. You could, for example, import all that table's records into a new table each week as a "back up". Then the following week, find all the records that had been created as of the week before and do an import into the back up table, matching on the pk, and "add new". All the new records would be ones that had been deleted and you can compare that list to the the admin approved list.

I can think of a couple ways to delete a record without triggering a cascade delete. Primarily if the user doesn't have delete privileges on the child, but does on the adult. Also, if you change data used in the relationship, breaking the link, then delete it.

Finally, you do have a UUID field, yes? And Created On, Created By, Modified On, and Modified By fields? And I would recommend a Get ( RecordID ) field as well. It could be helpful in identifying the problem.

[millmaine]

Thanks for these thoughts. Privileges was one of the first things we looked at but will review again. Yes we do have UUIDs, so we're confident that data is not being overwritten — the records are actually no longer there. However we don't have ALL of the other fields suggested. We'll implement those. The daily check method sounds like a great way to try to identify the circumstances. 

[David Jondreau]

Just one little point...missing UUIDs don't actually determine if the "record" is still there or not, after all, the UUID field can get overwritten! Get (RecordID) helps with that.
 I suppose you could use an calculation field to hold the UUID. I've never tried that though.

[kjunker]

Was there any resolution to this problem?  We're experiencing the same issue.  The users do not have the ability to delete.  There are no cascading deletions.  There are no script triggers that delete anything.  The field we search on is uneditable.  Yet a record that existed in the 3PM backup was gone at 4PM.

[millmaine]

We appear to have resolved the issue. I thought that perhaps records were not being committed properly and went through all scripts to ensure that where a "Commit Records/Requests" script step occurs that the options to "Skip data entry validation", "Perform without dialog" and "Override ESS locking conflicts" were all checked. Since then we have not lost any records. It probably comes down to less-than-perfect database construction and scripting in the first place but this at least seems to have worked for now.