Disk I/O error when VACUUMing on android?

[tallavi]

Hi,

I'm not sure where the problem lie, but since this is the top level infrastructure I'm using, I'll try to post it here.

I have a project that runs on both iOS and Android. On iOS - no problem. On Android, same code, I'm getting 'disk I/O error' when performing 'VACUUM'. This is a consistent issue, happening on a specific database file in multiple devices.

What makes this file special perhaps is the usage of blob column, with some of the rows containing about 100k. The entire DB is just 10MB, and it's not corrupt. I'm able to read and write to it. It is opened exclusively with a single connection, and I'm not in the middle of a transaction. It happens even if the vacuum is the first query to execute (as long as there is data to vacuum, otherwise it's ok).

Any idea of what else can I check to try to isolate the problem and figure out the source? Or perhaps a way to work around it?

Thanks!

[tallavi]

Found the root issue myself. Apparently the way SQLite decides where the put the temp files is not correctly supported in Android and it gets to a folder where he can't write files?

I tried changing the folder by setting an environment variable, but it didn't work.

What DID work is building sqlite with SQLITE_TEMP_STORE=3, which writes the temp files to memory instead. Hope it won't cause out of memory on larger files.

I know Android is not a OS directly supported by SQLiteCPP, but I'm keeping the issue open in case you DO want to update a FAQ or suggest something else or anything.

[SRombauts]

Thanks a lot @tallavi, sorry I didn't answer your question; I even didn't noticed it!

Also, I should probably add explicit support for Android, at least at a second-class citizen, with an How-To like you suggest.

Cheers!

[SRombauts]

I could even start an emulator in Travic CI to run some automated test

[tallavi]

That sounds nice 👍

I'm genuinely curious about why I wasn't able to use the environment variable SQLITE_TMPDIR to set a correct folder (app's cache folder) for temp files as guided here, and had to resort to disable temp files altogether. I tried looking on sqlite3.c but couldn't find the answer.

[nicklasaven]

I stumbled into the same problem and this issue led me to a solution. It seems like sqlite concludes with using the current working directory for temp files. I could manipulate that by hardcoding a path to a writable directory in the first position of azDir in function unixTempFileDir in the sqlite code which made things work.

But that is of course not a working solution. The problem by using the working directory as sqlite attemps to do is that Android sets the working directory to / for all NDK-apps. So all I had to do to solve my situation was to change the working directory to the same directory as where aI have the sqlite database. In C: #include <unistd.h> and in the function before opening the db: chdir(the_dir);

This solved my situation

[ErikBjare]

Just wanted to let you guys know that this issue was an amazing help for me in https://github.com/ActivityWatch/aw-android/issues/13.

Setting SQLITE_TMPDIR turned out to be all that was needed, too bad it took me days of debugging. Anyway, thanks a lot :heart: