Encountering "too many SQL variables" with ^5.1.0 only on Android (iOS works)

[jrista]

On one of my current projects, I recently began running into an error when displaying a particular screen in an Ionic 4/Angular 8 app that uses Cordova (not Capacitor.) We use Cordova-sqlite-storage as it seems to be the only persistent storage option for phones...when we store data in local storage, indexed db, etc. both iOS (in particular) and Android seem to fairly aggressively purge those stores of data. Our users will often use our app once, maybe twice, a month, each month, so it is not an every-day-use kind of app.

We have certain sets of data, mostly with several hundred items, but in some cases certain "categories" will have over a thousand. We are using something called RxDb for our in-app database, which is built on PouchDB, so direct control over the queries is not within reach. There is no real alternative to RxDb at the moment either, which we use for simpler integration with highly reactive NgRx and RxJs code. RxDb is generating queries with IN clauses, and if a category has over 999 items, the query was failing with the error too many SQL variables.

We did some research, found that we were still on ^4.0.0 of the cordova-sqlite-storage plugin, and then found some issues indicating that SQLite 3.32.x fixes this problem by increasing the SQLITE_MAX_VARIABLE_NUMBER setting from 999 to 32766. We also discovered that ^5.1.0 of the cordova-sqlite-storage plugin uses SQLite 3.32.3 (or greater).

We upgraded the plugin, and tested on iOS, and things work great. However when we test on Android, it seems that the 999 limit is still in effect, despite the plugin version being ^5.1.0. I am not sure how that is happening...as far as I can tell the sqlite version being used is indeed 3.32.3, based on the header comment inside of the sqlite3.c file that is embedded in the cordova-sqlite-storage-dependencies module under node_modules. Further checking verified that SQLITE_MAX_VARIABLE_NUMBER is indeed set to 32766, and not 999.

Is there some way that the cordova-sqlite-storage plugin is building a different version of sqlite for Android? Or is it possible that it may be overriding the SQLITE_MAX_VARIABLE_NUMBER only for Android? This has become a deal breaker issue, as the majority of our users are on Android, and it would be great to find a solution soon. Thank you!

[brodycj]

My response coming in the next couple of days, thanks for reporting.

[jrista]

@brodybits Thanks! We dug a little deeper...is it possible that Android overrides your own choice for sqlite with its own internal version?

[brodycj]

This plugin uses a custom NDK build on Android by default but may use built-in SQLite depending on the options used in the sqlitePlugin.openDatabase call. This information is already documented.

I would like to get this tested and documented at some point. Priority is given to commercial support customers, please contact sales@storesafe.io if interested.

[jrista]

Hmm, ok. I've been looking around, and I am not sure if the way we are using SQLite (through an adapter for RxDb, which in turn is really using PouchDB) gives us enough control to manage the SQLite connection.

It does appear as though we are still limited by the 999 variable limit. Is there a way to check which SQLite....instance (?) you are connecting with via this plugin? I may just try connecting directly to SQLite with the plugin, and see if it is connecting using the right SQLite version.

[brodycj]

If you have the SQLite plugin database object, you can try SELECT SQLITE_VERSION().

[jacobg]

I just came across this same issue using the latest cordova-sqlite-evcore-extbuild-free plugin on sqlite 3.37.2. A bulk insert with 4001 sql variables is failing with error too many SQL variables (code 1 SQLITE_ERROR[1]).

[jacobg]

SELECT SQLITE_VERSION() returns: {SQLITE_VERSION(): '3.28.0'}

[jacobg]

I had the wrong configurations:

-    androidLockWorkaround: 1,
-    androidDatabaseProvider: 'system'
+    androidDatabaseProvider: 'default'

After updating, it returns: SQLITE_VERSION(): "3.37.2", and the too many SQL variables error goes away.