Android crashing on select statement with large result set

[ekrapfl]

I am attempting to run a select statement like so (using executeSql and no transaction):

SELECT data FROM someTable

The data column is a JSON column that contains a fairly wide JSON object for each row (call it about 150 properties on each object)

When I have about 6000 records in the table, this will crash on Android, pretty consistently. The error log that I get is as follows:

12-05 15:51:45.575 12325 12325 E AndroidRuntime: FATAL EXCEPTION: main
12-05 15:51:45.575 12325 12325 E AndroidRuntime: Process: <AppIdentifier> PID: 12325
12-05 15:51:45.575 12325 12325 E AndroidRuntime: java.lang.OutOfMemoryError: Failed to allocate a 69003792 byte allocation with 25165824 free bytes and 58MB until OOM, target footprint 165258720, growth limit 201326592
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at java.lang.StringFactory.newStringFromChars(StringFactory.java:260)
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at java.lang.StringBuilder.toString(StringBuilder.java:413)
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at org.apache.cordova.NativeToJsMessageQueue.popAndEncodeAsJs(NativeToJsMessageQueue.java:204)
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at org.apache.cordova.NativeToJsMessageQueue$EvalBridgeMode$1.run(NativeToJsMessageQueue.java:366)
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at android.os.Handler.handleCallback(Handler.java:883)
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at android.os.Handler.dispatchMessage(Handler.java:100)
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at android.os.Looper.loop(Looper.java:214)
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at android.app.ActivityThread.main(ActivityThread.java:7356)
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at java.lang.reflect.Method.invoke(Native Method)
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
12-05 15:51:45.575 12325 12325 E AndroidRuntime: 	at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)

The number of records that causes the crash will vary based on how wide the records are (fewer properties on the JSON object will allow for more records to be selected), and it will also vary if I do other memory intensive things within the app before having the select statement run.

I have tried this using both cordova-sqlite-ext and cordova-sqlite-evcore-extbuild-free with the same results. I see no significant improvement in the cordova-sqlite-evcore-extbuild-free version.

I certainly could (and probably should) add an upper limit to the SELECT, but I am concerned with the relatively low amount of records I am selecting to cause this crash. Any guidance would be appreciated.

Eben

Possibly related to #55

[brodycj]

Thanks @ekrapfl for reporting. I can now reproduce the crash with the following test case (also with 150 properties & 5000 rows):

        it(suiteName + 'INSERT JSON TEXT string many rows with hundred properties each & SELECT the data', function(done) {
          var db = openDatabase('INSERT-JSON-TEXT-string-many-rows-with-hundred-properties.db')

          var test_rows = 7000
          var test_props = 100

          db.transaction(function(tx) {
            tx.executeSql('DROP TABLE IF EXISTS test_table');
            tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (data)', [], function(ignored1, ignored2) {

              for (var id=0; id<test_rows; ++id) {
                var obj = {}

                obj.id = id;

                for (var i=0; i<test_props; ++i) {
                  // [TBD] NO CRASH REPRODUCED WITH THIS CASE:
                  // obj['prop'+i] = 'value-'+(1000+i)
                  // XXX CRASH REPRODUCED IN THIS CASE (value with 50 chars):
                  obj['prop'+i] = 'value-aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa-'+(1000+i)
                }

                var insert_data = JSON.stringify(obj)

                tx.executeSql('INSERT INTO test_table VALUES (?)', [insert_data])
              }

              tx.executeSql('INSERT INTO test_table VALUES (?)', ['{extra:true}'], function(tx_ignored, rs1) {
                expect(rs1).toBeDefined();
                expect(rs1.rowsAffected).toBe(1);

                tx.executeSql('SELECT data FROM test_table', [], function(tx_ignored, rs2) {
                  expect(rs2).toBeDefined();
                  expect(rs2.rows).toBeDefined();
                  // expect(rs2.rows.length).toBe(???);

                  var row = rs2.rows.item(0);
                  expect(row.data).toBeDefined() // [TBD (...)]

                  done();

                });
              });
            });
          }, function(error) {
            // NOT EXPECTED:
            expect(false).toBe(true);
            expect(error.message).toBe('---');
            // Close (plugin only) & finish:
            (isWebSql) ? done() : db.close(done, done);
          });
        }, MYTIMEOUT);

I will need some time to investigate this one. At this point I am thinking to look for a solution on the evcore (brodysoft/cordova-sqlite-evcore-extbuild-free) plugin version.

[brodycj]

An update for the user community:

First, I would like to make it extra clear that this issue may be related to #55 but is definitely not the same thing.

#55 is a memory issue related to JSON decoding and encoding, which we know is resolved by cordova-sqlite-evcore-extbuild-free. In contrast, this issue happens after the JSON-encoded result data is prepared, when it is handled by the NativeToJsMessageQueue in the Cordova framework.

This issue is triggered by result data in the order of 35 MB. When the plugin sends the result data, the Cordova framework does a limited amount of string processing before injecting the data into the JavaScript running in the WebView. We can see that this memory issue is triggered within the Java StringFactory class.

One interesting factor is that Java uses UTF-16 encoding for string encoding and processing. We can see the consequence of the 16-bit (2-byte) encoding in the memory error message, with 2X memory needed to store and process the JSON-encoded result data.

I can think of a couple possible workaround solutions that could be done within the plugin:

• plugin executes a JavaScript function call on the application WebView that injects the result data into the plugin JavaScript for further processing
• update the Android implementation to send the result data to the JavaScript side in multiple parts, which is already supported by the Cordova plugin API

I would favor the second workaround solution since it would be more future proof and more likely to work together with a custom WebView plugin.

I would like to keep under consideration whether to implement this kind of solution in the evplus plugin version (cordova-plugin-sqlite-evplus-ext-common-free) or a new "evmax" plugin version.

For expedited delivery of the workaround solution please contact sales@xpbrew.consulting for more information.

[brodycj]

At this point I am leaning towards including the workaround solution in the evplus plugin version.

While adding the new "evmax" plugin version would give me some additional customer segmentation, I would like to avoid the added maintenance burden for now.

---

EDIT: I am taking this back for now, not yet ready to make a firm decision on which plugin version.

EDIT 2: This decision should be firm now.

[brodycj]

Workaround solution has been implemented and tested in the following plugin version: https://github.com/brodysoft/cordova-plugin-sqlite-evplus-ext-common-free