Android- SQLITE_IOERR_GETTEMPPATH 6410 on very complex queries

[knaeckeKami]

I'm running into an issue that occurs with complex queries on big-ish (~50mb) databases on Android using the ffi/sqlite3 library.

When running the query, I get an error code 6410 (SQLITE_IOERR_GETTEMPPATH):

SqliteException(6410): disk I/O error, disk I/O error (code 6410)
/flutter (17699): #0      throwException (package:sqlite3/src/impl/exception.dart:32:3)
I/flutter (17699): #1      PreparedStatementImpl.select (package:sqlite3/src/impl/statement.dart:59:7)
I/flutter (17699): #2      _VmDelegate.runSelect (package:moor/src/ffi/vm_database.dart:128:25)
I/flutter (17699): #3      _ExecutorWithQueryDelegate.runSelect.<anonymous closure> (package:moor/src/runtime/executor/helpers/engines.dart:41:19)
I/flutter (17699): #4      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:32:26)
I/flutter (17699): #5      _ExecutorWithQueryDelegate._synchronized (package:moor/src/runtime/executor/helpers/engines.dart:22:26)
I/flutter (17699): #6      _ExecutorWithQueryDelegate.runSelect (package:moor/src/runtime/executor/helpers/engines.dart:39:26)
I/flutter (17699): #7      _MoorServer._runQuery (package:moor/src/runtime/isolate/server.dart:89:25)
I/flutter (17699): <asynchronous suspension>
I/flutter (17699): #8      _MoorServer._handleRequest (package:moor/src/runtime/isolate/server.dart:57:14)
I/flutter (17699): #9      IsolateCommunication.setRequestHandler.<anonymous closure> (package:moor/src/runtime/isolate/communication.dart:163:31)
I/flutter (17699): #17     _runPendingImmediateCallback (dart:isolate-patch/isolate_patch.dart:118:13)

From what I researched, this seems to happen that the intermediate results for SQLite were too big to handle them in-memory, but SQLite could not find a temporary directory to store the result.

Luckily, I found a fix/workaround:
According to https://stackoverflow.com/questions/44766917/sql-logic-error-only-when-querying-on-android ,
this can be fixed by setting a pragma:

if(Platform.isAndroid){
   final cachebase =  (await getTemporaryDirectory()).path;
   db.customStatement("PRAGMA temp_store_directory = '$cachebase';");
}

I'm a little bit concerned though, since this pragma is deprecated according to https://www.sqlite.org/pragma.html#pragma_temp_store_directory

I'm just wondering if we can do anything to have this 'just work' for other users?
Where do you get the sqlite library, that is shipped with sqlite3_flutter_libs, from? Do you compile it yourself? If yes, then maybe it is possible to set a compiler flag so that this does not happen.

[simolus3]

Do you compile it yourself? If yes, then maybe it is possible to set a compiler flag so that this does not happen.

Yes, we use a "custom" (some compile-time options set) sqlite, the definition is here.
I added a compile-time option to use an in-memory store for temporary operations because of #667. That fixed a problem with non-small queries, but apparently it's not enough for big databases.

this pragma is deprecated

Fortunately, the underlying sqlite3_temp_directory variable isn't deprecated and we can expose it from package:sqlite3. The documentation warns about using it, but since the default behavior is broken on Android we're pretty much forced to set that variable.

I've just published sqlite3: 0.1.7, in which you can set the temporary directory used by sqlite:

import 'package:sqlite3/sqlite3.dart';

// Do this once, before opening a database
Future<void> init() async {
  if (Platform.isAndroid) {
    final cachebase =  (await getTemporaryDirectory()).path;
    sqlite3.tempDirectory = cachebase;
  }
}

You might need this one as well:

VmDatabase(
  setup: (database) {
    if (Platform.isAndroid) database.execute('PRAGMA temp_store = FILE');
  }
);

It's hard to make this an "it just works" solution since getTemporaryDirectory() is Flutter-only. I could make it a utility function in sqlite3_flutter_libs if this improves the situation.

[simolus3]

Wait, we already use SQLITE_OMIT_DEPRECATED, so my understanding is that the temp_store_directory pragma shouldn't be available in the first place. Are you using sqlite3_flutter_libs to include libsqlite3.so?

[knaeckeKami]

Are you using sqlite3_flutter_libs to include libsqlite3.so?

yes, I'm using

  moor: ^3.3.1
  sqlite3_flutter_libs: ^0.2.0

Maybe the wrong sqlite 3 library gets loaded for some reason? Or the SQLITE_OMIT_DEPRECATED does not actually do what the documentation says?

I get the following sqlite3 library version and set pragmas:

I/flutter (19119): Moor: Sent select sqlite_version(); with args []
I/flutter (19119): dbOpen version: {sqlite_version(): 3.32.3}
I/flutter (19119): Moor: Sent SELECT * FROM pragma_compile_options; with args []
I/flutter (19119): dbOpen - pragma {compile_options: COMPILER=clang-9.0.8}
I/flutter (19119): dbOpen - pragma {compile_options: ENABLE_FTS5}
I/flutter (19119): dbOpen - pragma {compile_options: ENABLE_JSON1}
I/flutter (19119): dbOpen - pragma {compile_options: ENABLE_RTREE}
I/flutter (19119): dbOpen - pragma {compile_options: HAVE_ISNAN}
I/flutter (19119): dbOpen - pragma {compile_options: MAX_EXPR_DEPTH=0}
I/flutter (19119): dbOpen - pragma {compile_options: OMIT_AUTHORIZATION}
I/flutter (19119): dbOpen - pragma {compile_options: OMIT_DECLTYPE}
I/flutter (19119): dbOpen - pragma {compile_options: OMIT_DEPRECATED}
I/flutter (19119): dbOpen - pragma {compile_options: OMIT_GET_TABLE}
I/flutter (19119): dbOpen - pragma {compile_options: OMIT_LOAD_EXTENSION}
I/flutter (19119): dbOpen - pragma {compile_options: OMIT_PROGRESS_CALLBACK}
I/flutter (19119): dbOpen - pragma {compile_options: OMIT_SHARED_CACHE}
I/flutter (19119): dbOpen - pragma {compile_options: OMIT_TCL_VARIABLE}
I/flutter (19119): dbOpen - pragma {compile_options: OMIT_TRACE}
I/flutter (19119): dbOpen - pragma {compile_options: THREADSAFE=2}
I/flutter (19119): dbOpen - pragma {compile_options: UNTESTABLE}
I/flutter (19119): dbOpen - pragma {compile_options: USE_ALLOCA}

Actually, it seems that SQLITE_OMIT_DEPRECATED does not guarantee to drop support:

This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED.

[knaeckeKami]

Using sqlite 0.1.7 and using

bool _hasInitializedSqlite = false;

// Do this once, before opening a database
// see https://github.com/simolus3/moor/issues/876
Future<void> _ensureSqlite3Initialized() async {
  if(_hasInitializedSqlite){
    return;
  }
  //TODO prevent duplicate execution using synchronized?
  if (Platform.isAndroid) {
    final cachebase =  (await getTemporaryDirectory()).path;
    sqliteLib.sqlite3.tempDirectory = cachebase;
  }
  _hasInitializedSqlite = true;
}

Fixes this for me.