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

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

• android
• kotlin
• sqlite
• android-room
• roomdb

• Do you have have something like Crashlytics or ACRA? to link the corruption to crashes on the devices? Do you have any log reporting system? that you can increase the logging on Database operations. Have you code a code analysis to look for issues mentioned in that URL? – Andrew Commented Jan 18 at 13:38
• Crashlytics did not log any crashes or events whatsoever. I don't have a logging system in place. I did do a code analysis and my application does not do any operations specified in either of those links – batofgotham006 Commented Jan 19 at 11:47
• Have you profiled your memory use? Does your app use a lot of memory? Can you link occurrences to low memory devices? The reason for asking this is a lot of devices run the Low Memory Killer Daemon and this is absolutely brutal when it kill -9 a process, there will be no crash logs, no flushing of buffers and cleaning closing files, no running things like onDestroy, etc. The process is just stopped. This could corrupt the database file if it happens during a write operation. If you are watching logcat there will be some logs from it's process. – Andrew Commented Jan 19 at 12:00

1 Answer 1

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.

• SQLite is comprehensively tested (see https://www.sqlite./testing.html), so it is unlikely that you have stumbled across a bug where SQLite itself corrupts the WAL file.

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./wal.html (and perhaps consider what you could do in regards to checkpointing)