transition to cordova-sqlite-evmax-eu-build-free

[brodycj]

As discussed by email, we want to transition away from this private fork towards a new evmax pluign version: https://github.com/brodybits/cordova-sqlite-evmax-eu-build-free

Here are the most important changes:

• evplus enhancements to support 90MB result data with (ugly) workaround, as was required by multiple customers
• evmax enhancements to support INSERTion of 200MB data over 1 million rows (another ugly workaround), as required by another customer
• support a new, non-standard BLOBFROMBASE64 function as required for a customer working with MBTiles data
• evmax workaround to support slash (/) character with Capacitor, as required by a another, new customer

Here are the changes between this plugin version and cordova-sqlite-evmax-eu-build-free:

• https://github.com/brodybits/cordova-sqlite-evmax-eu-build-free/compare/evplus-eu-support-2022..master

with the following changes in the Android NDK library:

• https://github.com/brodybits/android-sqlite-evmax-ndk-driver-free/compare/evcore-eu-support-2022..evmax-eu-support-2023

TODO:

• [x] missing updates from SQLite 3.41.2 with some bug fixes now included by 3.43.0 ref:
  • https://sqlite.org/releaselog/3_41_2.html
  • https://sqlite.org/src/timeline?from=version-3.41.1&to=version-3.41.2
• [x] bug fixes expected to be released in SQLite 3.41.3 now included by 3.43.0 ref:
  • https://sqlite.org/src/timeline?from=version-3.41.2&to=branch-3.41
• [x] _update with recent SQLite 3.43.0 release ref: https://sqlite.org/releaselog/3_43_0.html_
• [x] _enable math functions ref: https://www.sqlite.org/lang_mathfunc.html_
• [ ] acceptance testing

[brodycj]

I have just updated https://github.com/brodybits/cordova-sqlite-evmax-eu-build-free with SQLite 3.43.0 & math functions enabled, with 0.0.1-pre02 tag

[kryops]

Hi @brodybits ,

iOS is looking good, however on Android I noticed random freezes that occur after a few seconds or minutes of clicking through the app.

I tried a bit of debugging with medium success; here's my findings:

• It seems like either the native part does not send a response for one of the queries, or the JavaScript part does not handle it correctly. The txLock in the JavaScript code stays in progress and just queues all subsequent queries:

• The Java logic seems to be done (i.e. not stuck there). I made a thread dump, and it sits waiting at the line dbq = q.take();:

"pool-10-thread-2@23553" prio=5 tid=0x12d nid=NA waiting
  java.lang.Thread.State: WAITING
      at sun.misc.Unsafe.park(Unsafe.java:-1)
      at java.util.concurrent.locks.LockSupport.park(LockSupport.java:190)
      at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2067)
      at java.util.concurrent.LinkedBlockingQueue.take(LinkedBlockingQueue.java:442)
      at io.sqlc.SQLitePlugin$DBRunner.run(SQLitePlugin.java:541)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
      at java.lang.Thread.run(Thread.java:920)

• LogCat does not contain anything suspicious

Is there anything more that we can do on our end to locate the root cause?

Thanks for looking into it!

[brodycj]

My understanding is that you observe this form of instability with the new evmax plugin version but NOT with this existing mobileX fork.

It would help me if you could test the stability with another plugin fork, which has no EU character support: https://github.com/storesafe/cordova-plugin-sqlite-evplus-ext-common-free

I am now wondering if there could be an issue my evmax workaround solution for 200 MB input data and something and your mobileX application.

[kryops]

That's correct, in the existing mobileX fork we did not observe this issue.

I just tested with https://github.com/storesafe/cordova-plugin-sqlite-evplus-ext-common-free#0.9.1-rc01, and I could reproduce the issue there as well.

[brodycj]

That indicates to me that there is an issue somewhere between your application and the evplus enhancements in cordova-plugin-sqlite-evplus-ext-common-free. At this point I am suspecting an issue in the plugin, NOT in your application code. (Note that this plugin version never got the enhancements from cordova-plugin-sqlite-evplus-ext-common-free despite its "evplus" plugin name.)

It may help if you can give me an idea what kind of data it is getting stuck on. Here is my email address in case you don't have it on hand: chris.brody+brodybits@gmail.com

[kryops]

I tried to dig a little deeper: It seems that this affects queries with quite large result sets.

I observed two separate error cases in SQLitePluginTransaction.prototype.run_batch_flatjson: 1) The result does not end with a "endrows" item, so the loop runs infinitely and causes an out of memory because it keeps adding empty rows to the rows array

(I set a conditional breakpoint for ri > 10000)

2) The mycb callback is called twice for the same query, but the result array only seems to be a slice of what we expect, and it does not start with "okrows". Thus the whole result set is skipped and the callback in the user code is never called:

I did not have much luck yet debugging further down in SQLitePluginTransaction.prototype.run_batch_flat_with_dbid_part2.

I also tried to build a minimal Cordova app with a reproduction, but had no luck either. Maybe we will end up sending you our actual app via e-mail so you can use it for debugging 😅

[brodycj]

@kryops thanks for all of your efforts!

It seems to me like the result data has quite a number of columns with null, integer, and short string values. I will guess there may be some longer string values in the result columns as well.

I would like to do some testing on my own with a large number of columns with varying column data types & sizes and see how my evplus & evmax plugin versions hold up.

I would like to discuss some other options by private email with @DanielSWolf as well.

[brodycj]

@kryops it may be helpful if you can give me a rough idea of how many columns may be in the result data and now many of the columns may have string length greater than 10 characters or so.

I will work on a test case to see how my evplus & evmax plugin versions work with such a large number of result columns. I will also follow up with @DanielSWolf by email, maybe later today.

[kryops]

@brodybits here are example rows from the two queries that seem to be the ones affected most:

committed: true
description: null
id: "12353"
installationDisplayId: null
installationId: null
installationShortText: null
locationDisplayId: "10224"
locationId: "14"
locationShortText: "Klinikum der Universität München"
notificationDisplayId: null
notificationId: null
notificationShortText: null
position: 1
workingState: "NOT_DONE"

assignmentId: "1251"
assignmentType: "SM01"
assignmentWorkflowStatus: "SEEN"
installationDisplayId: "10018863"
installationId: "5515"
installationMaterialNo: null
installationType: "M"
locationDisplayId: "10223"
locationId: "7"
notificationDate: "2017-04-28T12:21:53.000Z"
notificationId: "625"
notificationPriority: "2"
notificationStatus: "MAUF MIAR"
notificationType: "S2"
objectListItemDisplayId: "10018863"
objectListItemId: "12355"
orderActivityType: "001"
orderId: "774"
orderType: "SM02"

• ...ShortText columns can have up to 40 characters
• description can be longer
• ...DisplayId are usually 10 characters or shorter

[brodycj]

Also rough estimate of how many result rows from each of the queries?

Thanks!

[kryops]

in this particular case it was 154 rows for both queries

[brodycj]

Thanks. By design the result is split up and sent in parts, looks like something is not working right. I may need a few weeks to try these out.

[brodycj]

I found a possible reproduction case,

UPDATED: This reproduces an issue with error handling in case of not enough SQLite statement parameters which ends up with no response back to the JavaScript side - will discuss further in comments coming up below

(based on a reproduction I made for https://github.com/storesafe/cordova-sqlite-storage-help/issues/70 from the past):

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

          var test_rows = 700
          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, d2, d3, d4, d5, d6, d7, d8, d9, d10, d11, d12, d13, d14, d15)', [], function(ignored1, ignored2) {

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

                obj.id = id;

                for (var i=0; i<test_props; ++i) {
                  obj['prop'+i] = 'value-'+(1000+i)
                }

                var insert_data = JSON.stringify(obj)

                tx.executeSql('INSERT INTO test_table VALUES (?,?,?,?,?,?,?,?)', [insert_data, null, 8881, null, 8882, null, 8883, 8884])
              }

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

                tx.executeSql('SELECT * FROM test_table')
                tx.executeSql('SELECT * FROM test_table')
                tx.executeSql('SELECT * FROM test_table')
                tx.executeSql('SELECT * FROM test_table')
                tx.executeSql('SELECT * FROM test_table')
                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);

The new evmax plugin version freezes with this case; both cordova-sqlite-evcore-extbuild-free and the existing mobileX evplus fork end up with an internal batch error.

I would absolutely hope to support this case. I may need some time to investigate further and find a solution. Thanks.

[brodycj]

The previous reproduction I made was incorrect due to not enough INSERT parameters, and it led to the frozen execution in the new evmax plugin version. I would like to resolve that, but I am not (yet) convinced that my previous reproduction covers the issue with your application.

I have made another test case that can successfully insert many records that I think should reflect the kind of data that your application inserts into the database, then tried selecting multiple times within the same transaction. In this case I am able to SELECT & ignore the data 3 times, uncomment the 4th time and it crashes on my emulator. (I have also added a commented-out line that reproduces the issue I found with not enough INSERT parameters.)

        it(suiteName + 'INSERT JSON TEXT string many rows with hundred properties each with extra columns & SELECT the data 6x', function(done) {
          var db = openDatabase('INSERT-JSON-TEXT-string-many-rows-with-hundred-properties-extra-columns-and-select-data-6x.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, d2, d3, d4, d5, d6, d7, d8, d9, d10, d11, d12, d13, d14, d15)', [], function(ignored1, ignored2) {

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

                obj.id = id;

                for (var i=0; i<test_props; ++i) {
                  obj['prop'+i] = 'value-'+(1000+i)
                }

                var insert_data = JSON.stringify(obj)

                // INCORRECT INSERT - MISSING 2 ARGUMENTS - NO RESPONSE TO JAVASCRIPT SIDE:
                // tx.executeSql('INSERT INTO test_table VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)', [insert_data, 'abcdefghijklmnopqrst', null, 7771, 'abcdef', null, 71, 'abcdefghijklmnopqrst', null, 7771, 'abcdef', null, 71])
                // CORRECT INSERT:
                tx.executeSql('INSERT INTO test_table VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', [insert_data, 'abcdefghijklmnopqrst', null, 7771, 'abcdef', null, 71, 'abcdefghijklmnopqrst', null, 7771, 'abcdef', null, 71, 'abcdef', null])
              }

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

                // SELECT & IGNORE:
                tx.executeSql('SELECT * FROM test_table')

                // SELECT & IGNORE - AGAIN & AGAIN:
                tx.executeSql('SELECT * FROM test_table')
                tx.executeSql('SELECT * FROM test_table')

                // UNCOMMENT FOR ONE MORE TIME CRASHES IN MY EMULATOR:
                // tx.executeSql('SELECT * FROM test_table')

                // SELECT & EXAMINE (just a little):
                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 would really love to get all of these cases working, still need to investigate further before promising any timelines.