IO error on Android when sqlite uses the temp store

[francescotescari]

Describe the bug When using op-sqlite on Android version 12 or lower, SQLite returns IO error (code 10) for large queries/transactions. This is likely caused by sqlite trying to write a temporary store file. It seems to be the same issue as https://github.com/ospfranco/react-native-quick-sqlite/issues/80 : the workaround described there solves the issue. In our case, several users were affected by this issue and it was hard to figure out the cause from an opaque [op-sqlite] SQLite error code: 10, description: disk I/O error. happening to some users and not to others (depending on their DB size), for the same queries.

Versions:

• OS and version: Android 12 and lower. We don't see it happening on 13 and above
• op-sqlite version: 3.0.7. Have you tried the latest version? No
• RN version: 0.71.14
• New Architecture: No

Reproducible example In our case, a transaction with ALTER TABLE ... DROP COLUMN on a large table (60k+ rows and 20 columns) was triggering the issue. Reproducible on the Android 12 emulator.

Proposed fix Set the SQLITE_TEMP_STORE=2 flag in the Android CMakeLists.txt (currently done only if sql cipher is enabled). IMHO there is no value in keeping the temp store on files knowing that it will fail (on a sufficiently large amount of devices) as soon as sqlite needs that. Nor to expect users to configure each time the SQLite connection to mitigate it, when it can be done at compile time by the library, and when not documented. We patched the library this way to fix the issue.

[ospfranco]

You can pass compilation flags yourself in the object in the package.json:

{
    // your normal package.json
    // ...
    "op-sqlite": {
      "performanceMode": "2",
      "sqliteFlags": "-DSQLITE_TEMP_STORE=2", // Add it here
      "fts5": true,
    }
}

Let me know if this works!

[francescotescari]

Thanks, we will try that as soon as we update to a recent version 🙏

The point of the issue was to bring up the fact that by default configuration, the bundled sqlite doesn't seem to work on all Android versions and maybe it would be a better option if the default configuration of op-sqlite would do so instead, by defaulting to SQLITE_TEMP_STORE=2 (seems like Android uses it as well). Or at least mention this in the op-sqlite documentation. Your choice of course. I'll close the issue, for us it's solved, hopefully it will help someone else getting to the cause of the IO error faster than we did.

[ospfranco]

It's a good point. A trade-off in any case as smaller and memory constrained devices might run out of RAM but if enough people want it I guess it makes sense to set it as a default. Will leave it as-is for now and watch for feedback in the coming months. Thanks!