CODEWITHSUNDEEP
androidkotlinsqliteandroid-roomroomdb
batofgotham006
batofgotham006

Reputation: 1

Partial data loss in android room sqlite database

I have an android application that runs on a slightly customized version of Android 10. The application persists data to Room db.

Recently, backend server has logged 40 cases of partial data loss where both newly inserted rows and updates done to existing rows have been deleted from the database.

My assumption is that since SQLite initially writes data to a WAL file, corruption of this file is resulting in loss of data that is not yet persisted to the original db.

I have tested this out by intentionally corrupting the WAL file by writing garbage data to it and sure enough, all the data that hasn't been checkpointed is lost.

Now, how do I identify what is corrupting the WAL file?

Links I've referenced while debugging this: How To Corrupt An SQLite Database File Debugging file corruption on iOS

Upvotes: 0

Views: 112

Answers (1)

MikeT
MikeT

Reputation: 57053

Now, how do I identify what is corrupting the WAL file?

As per the linked causes of corruption. It is likely due to misuse of the database file(s) rather than an issue with SQLite itself.

Perhaps the most frequent is a backup/restore or copy process that does not consider the WAL file as being part of the database. i.e. just backing up and thus restoring just the database file itself, thus either leaving a then invalid WAL file or an empty WAL file which can result in the data store in the WAL file being lost.

Perhaps try using the setJournalMode to set the mode to TRUNCATE. This should resolve the issue due to the different logging methodology.

  1. with WAL changes to the database are stored in the WAL file and applied by COMMITs which copy the changes (not necessarily all) from the WAL file to the database file. As such the loss of the WAL file, in whole or even partially can lose changes that have not been committed (effectively a rollback, full or partial, of the changes).
  2. in JOURNAL mode changes are applied to the database file, they are also recorded in the journal file to allow the changes to be backed out. Loss of the journal file does not result in the loss of data.

I have tested this out by intentionally corrupting the WAL file by writing garbage data to it and sure enough, all the data that hasn't been checkpointed is lost. Recently, backend server has logged 40 cases of partial data loss where both newly inserted rows and updates done to existing rows have been deleted from the database.

As explained above, yes corruption of the WAL can lead to missing data; BUT it is not the only way that data can be apparently lost. For example IGNOREd conflicts (e.g./most likely UNIQUE conflicts) could result in the apparent disparity.

Are you excluding the possibility that rows may exist in the embedded database but not in the backend?

I don't have a logging system in place

Perhaps you should consider doing so (on a temporary basis). Perhaps via a TRIGGER or TRIGGERS. Although Room doesn't cater for TRIGGERs via annotation they can still be added using either or both the onCreate and onOpen callbacks, furthermore you can also have tables, such as a table for logging, that Room is unaware off (when Room checks the intended v actual schema it bases this on the @Entity annotations to build the intended an only checks that the actual schema includes).

  • perhaps consider using a similar TRIGGER for the BEFORE action as well as a TRIGGER for the AFTER action.

  • With such a logging system in place losses that are not due to WAL file corruption may come to light.

You may wish to refer to https://www.sqlite.org/wal.html (and perhaps consider what you could do in regards to checkpointing)

Upvotes: 0

Related Questions