Root cause of bug #193: loss of data on certain Android versions?

[brodycj]

I received the following question in December 2014 which was eventually reported in #193: https://groups.google.com/forum/#!topic/Cordova-SQLitePlugin/oq8wMDSH4M4

For some reason, it would lose data if the app does not close the database before it stops.

I introduced a crude androidLockWorkaround option that would simply close and reopen the database between transactions. In addition, some other versions such as cordova-sqlite-ext and Cordova-sqlcipher-adapter use custom-built database implementations that would not suffer from this issue.

Unfortunately I have not been able to reproduce this issue myself, which I would need to justify raising a bug on AOSP (Android project).

I recently heard that this is especially bad on Android 5.1. I have no idea if this would still be an issue on Android 6.

If anyone in the community can help track down the cause of this issue and perhaps file a bug report on the Android platform I would be very grateful.

IMPORTANT (UPDATE): This should ONLY be an issue in case the androidDatabaseImplementation: 2 is used. If anyone sees a similar issue without using androidDatabaseImplementation: 2 please report this in a new issue along with a reproduction program.

[JamalANasier]

I will gladly help if i know what i have to do buddy, but i just post something and for you know so far the app is running smoothly without loosing any data (I never close the database). Tested it on Ipad Ios 9.3.2 and Android 4.4. and sometimes on emulator Nexus 9 with API 23

Will contact you if there is something comes up as you mention in here (hopefully not lol)

[Tommonz]

Hi,

First of all, thanks for all of you hard work on this plugin. One of my clients has experienced this issue twice on different devices. Unfortunately I can't tell you anything about the versions of android etc or what caused it so I'm not much help : (. It seems to be that the data appears to be there and then after some event happens the recently inserted data is lost which makes me wonder about "hot journals" as seen in 1.3 at this link https://www.sqlite.org/howtocorrupt.html . ie the Journal is lost but other data remains. I wonder if in some situations the COMMIT doesn't get passed to the database? I know this is all overly simplistic and probably doesn't help you at all. This issue posses a huge issue for our product so hopefully someone can help you resolve it.

[brodycj]

One of my clients has experienced this issue twice on different devices.

Are you using the androidDatabaseImplementation: 2 setting? If so, can you mention why you need to use this setting? If not, then can you report it in a new issue along with some reproduction code?

I just added a note to the original description that this should only be an issue when using the androidDatabaseImplementation: 2 setting.

It seems to be that the data appears to be there and then after some event happens the recently inserted data is lost which makes me wonder about "hot journals" as seen in 1.3 at this link https://www.sqlite.org/howtocorrupt.html . ie the Journal is lost but other data remains. I wonder if in some situations the COMMIT doesn't get passed to the database?

According to the referenced section the journal contains rollback data. The changes are committed by simply deleting the the rollback journal, unless certain versions of the Android platform change the SQLite journal mode somehow.

[brodycj]

The is speculation but I put it here in case it can help us find the root cause.

Assuming the SQLite journal mode is not changed from the default, I can think of the following possible theories:

1. the changes are not flushed until the database is closed
2. the EXCLUSIVE lock is not released until the database is closed and the rollback journal is not cleaned up until the database is closed or reopened

I would be very surprised if theory 1 would be true. A quick look at the SQLite code related to sqlite3_close()/sqlite3_close_v2() does not reveal anything special to flush any outstanding changes. A quick look through the AOSP SQLite database code does not reveal this either.

I came up with theory 2 based on an observation at https://groups.google.com/d/msg/Cordova-SQLitePlugin/oq8wMDSH4M4/LLyAPjSXyzIJ, and it seems to be consistent with the workaround solution.

Some references:

• https://www.sqlite.org/atomiccommit.html
• http://sqlite.1065341.n5.nabble.com/capturing-and-testing-a-hot-journal-td76628.html
• https://android.googlesource.com/platform/frameworks/base/+/master/core/java/android/database/sqlite/SQLiteDatabase.java
• https://android.googlesource.com/platform/frameworks/base/+/master/core/java/android/database/sqlite/SQLiteClosable.java
• https://www.sqlite.org/lockingv3.html
• https://www.sqlite.org/pragma.html#pragma_journal_mode
• https://www.sqlite.org/wal.html

[Tommonz]

Hi,
Thanks for your reply.

Are you using the androidDatabaseImplementation: 2 setting?

No I'm not. And unfortunately I can't reproduce this issue at all. I have a client that has about 30 tablets in the field and in the space of a year it has occurred twice.
I can provide you some more information however in case it is helpful.

1. This occurred using the previous SQL Lite plugin that Telerik supported before Brodysoft. I'm not sure if the Brodysoft one is derived from this or not.
2. In one instance this issue occurred after the user encountered a bug (my fault) and then restarted the tablet that he was using. I'm not sure if in the other instance the user restarted or not.

Can I ask a further question, do you have confirmation that this error no longer occurs when androidLockWorkaround: 1 is used?

Does this work around close the database after each transaction?

Thanks,
Tom

[MikeDimmickMnetics]

We're seeing some data loss due to apparent database rollbacks on some Zebra MC40 devices, which is our main type of device in the field. I've not heard of the issue occurring on any other devices, but most of our customers are using the MC40.

We store some sequence number information in local storage as well as in one of our SQLite databases, which is becoming inconsistent. This sequence number is the latest transaction ID that has been reported as committed on the server, and comes from an AUTOINCREMENT column in SQLite, so I was very surprised to find the database's sequence number (in the sqlite_sequence table) being much older than that in local storage.

It seems to be happening on both Android 4.1.1 Jellybean and on Android 4.4.4 KitKat - mostly we're getting reports from the field that some data was never sent to the server, and some data on the server does not appear on the device. We don't yet have repeatable reproduction steps. When my colleague reproduced the issue, he mentioned that the app had crashed and then been restarted. So far I have not been able to reproduce it with the 'Don't keep activities' developer mode option.

Versions, options:

• cordova 6.1.1
• cordova-android 5.1.1
• Cordova-sqlite-storage 1.2.2
• Target SDK 23 (with Symbol Technologies, Inc EMDK add-on version 3.1)
• Using the Android SQLiteDatabase API - looking at the plugin code, the androidDatabaseImplementation option does nothing on plugin version 1.2.2.

[brodycj]

@MikeDimmickMnetics please upgrade to a more recent of cordova-sqlite-storage. Some versions including cordova-sqlite-storage 1.2.2 would ignore the androidDatabaseImplementation option.