Open knaeckeKami opened 3 years ago
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.
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
?
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.
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.
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):
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:
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.