Intermittent (26) file is not a database - error with Room 2.8.4 and concurrent query executors

[EliasTechnik]

When using SQLCipher with Room 2.8.4 on Android, concurrent query execution appears to intermittently fail with:

I    Database keying operation returned:0
E    (26) file is not a database in "SELECT COUNT(*) FROM sqlite_schema;"
W    JNI critical lock held for 74.312ms on Thread[27,tid=8247,Runnable,Thread*=0xb4000076c91d1590,peer=0x22d91f8,"pool-5-thread-2"]

This occurs even though the database keying operation succeeds and the database itself remains readable afterward. The failure appears to be triggered by multiple concurrent query threads accessing the database.

A minimal reproducible demo project is available here
On my device, the intermittent fail was only noticeable on tables with more than 40k rows.

Steps to reproduce:

• Launch the demo app
• Press Insert 10k rows multiple times
• Once the database reaches ~40k rows or larger, errors begin appearing intermittently

Based of my testing, there were two things which fixed the error independent of each other:

• using Executors.newSingleThreadExecutor() as queryExecutor
• setting JournalMode to JournalMode.TRUNCATE (Not a real solution as it disables WAL)

Both solutions can be tried with the demo app by modifying data/AppDatabase.kt.

Edit: In my production app, both settings are needed to eliminate the error. This might be because the app does more work async, but I am currently unsure.

I have to add that I use some custom database settings which I have to keep for compatibility with an existing production database:

PRAGMA cipher_default_page_size = 8192; 
PRAGMA cipher_use_hmac = OFF; 
PRAGMA cipher_memory_security = OFF;

My questions:

• Is SQLCipher expected to support concurrent connections in this configuration?
• Are there recommended settings or connection constraints when using SQLCipher with Room?
• Am I missing or doing something wrong in my setup, which is the cause of this behaviour?

Using Executors.newSingleThreadExecutor() as queryExecutor wight work as an solution, but it would affect throughput and latency characteristics in some cases.

Thanks for taking a look.

[EliasTechnik]

The behaviour is also present in v4.14.0 of sqlcipher-android.

[developernotes]

Hi @EliasTechnik,

Thank you for providing a sample to review. We will look into this and share our feedback.