Moor: Using Moor inside of a Push Helper

[Sorunome]

Heya,

We switched our backend to use moor as backend (sqflite w/ encrypted_moor) and saw great performance increases inside of our app! There is, however, a problem: We also need to access the database from within our push helper, and, as the push helper code could run multiple times at once (many push notifications after short timespans) or could run while the normal app is running, this leads to a lot of "Database Locked" issues when trying to read or write.

The question here is if there is a recommended way to use moor inside of a push helper (Would running moor in an isolate fix this?) and if anyone has experience with this issue.

Thank you for any answers!

[simolus3]

"Database Locked" issues when trying to read or write

It sounds like WAL mode might help here, but I've never tried that in an app.

Would running moor in an isolate fix this?

encrypted_moor uses platform channels, which AFAIK are only available on the main isolate. Does your push helper run on the same isolate as your UI? If so, you could just share the same moor database object to avoid them blocking each other.

You could also make the main isolate use MoorIsolate.inCurrent and have the push helper connect to that.

[Sorunome]

Thank you for the quick reply! How do you switch the database to WAL mode? What I figured out seems kinda hacky, as if there should be a simpler way:

  @override
  Future<void> beforeOpen(QueryExecutor executor, OpeningDetails details) async {
    await super.beforeOpen(executor, details);
    print('running beforeOpen...');
    if (executor.dialect == SqlDialect.sqlite) {
      print('changing mode to WAL....');
      customSelect('PRAGMA journal_mode=WAL').get().then((ret) {
        print('done switching mode to WAL');
        print(ret.first.data.toString());
      });
    }
  }

[simolus3]

I wouldn't recommend to override the beforeOpen database method. Instead, you can use the beforeOpen callback on a MigrationStrategy:

@override
MigrationStrategy get migration {
  return MigrationStrategy(beforeOpen: (_) async {
    if (executor.dialect == SqlDialect.sqlite) {
      await customStatement('PRAGMA journal_mode=WAL');
    }
  });
}

[Sorunome]

We are still trying to figure this out and trying different approaches. One issue that keeps plopping up now and then is:

06-17 15:42:58.712 14184 14328 I flutter : Caught error: NoSuchMethodError: The method 'rawInsert' was called on null.
06-17 15:42:58.712 14184 14328 I flutter : Receiver: null
06-17 15:42:58.712 14184 14328 I flutter : Tried calling: rawInsert()

Which is surprising, as that would mean the database inside the EncryptedExecutor is null? And we ensure that the database is open every time the app is resumed? Do you have any hints / pointers as to what might be going on here?

[simolus3]

Yeah, that should only happen if the executor isn't open yet. If you only use apis from QueryEngine it should ensure that for you. If you use the raw QueryExecutor directly, it's best to wrap that in doWhenOpened. Can you post a stacktrace of where that happened?

[Sorunome]

Yeah, we don't use the QueryExecutor directly, we only use the generated database object. Will get a stacktrace, thanks again for the help

[Sorunome]

06-18 19:49:37.630 17135 17239 I flutter : [Error] Failed to send message: NoSuchMethodError: The method 'rawInsert' was called on null.
06-18 19:49:37.630 17135 17239 I flutter : Receiver: null
06-18 19:49:37.630 17135 17239 I flutter : Tried calling: rawInsert()
06-18 19:49:37.631 17135 17239 I flutter : #0      <optimized out> (<optimized out>:135)
06-18 19:49:37.631 17135 17239 I flutter : #1      _ExecutorWithQueryDelegate.runInsert.<anonymous closure> (package:moor/src/runtime/executor/helpers/engines.dart:69)
06-18 19:49:37.631 17135 17239 I flutter : #2      _ExecutorWithQueryDelegate._synchronized (package:moor/src/runtime/executor/helpers/engines.dart:25)
06-18 19:49:37.631 17135 17239 I flutter : #3      _ExecutorWithQueryDelegate.runInsert (package:moor/src/runtime/executor/helpers/engines.dart:67)
06-18 19:49:37.631 17135 17239 I flutter : #4      QueryEngine.customInsert.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:245)
06-18 19:49:37.631 17135 17239 I flutter : #5      QueryEngine._customWrite.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:266)
06-18 19:49:37.631 17135 17239 I flutter : #6      QueryEngine.doWhenOpened.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:98)
06-18 19:49:37.631 17135 17239 I flutter : #7      _rootRunUnary (dart:async/zone.dart:1192)
06-18 19:49:37.631 17135 17239 I flutter : #8      _CustomZone.runUnary (dart:async/zone.dart:1085)
06-18 19:49:37.631 17135 17239 I flutter : #9      _FutureListener.handleValue (dart:async/future_impl.dart:141)
06-18 19:49:37.631 17135 17239 I flutter : #10     Future._propagateToListeners.handleValueCallback (dart:async/future_impl.dart:682)
06-18 19:49:37.631 17135 17239 I flutter : #11     Future._propagateToListeners (dart:async/future_impl.dart:711)
06-18 19:49:37.631 17135 17239 I flutter : #12     Future._completeWithValue (dart:async/future_impl.dart:526)
06-18 19:49:37.631 17135 17239 I flutter : #13     _AsyncAwaitCompleter.complete (dart:async-patch/async_patch.dart:36)
06-18 19:49:37.631 17135 17239 I flutter : #14     _completeOnAsyncReturn (dart:async-patch/async_patch.dart:298)
06-18 19:49:37.631 17135 17239 I flutter : #15     _TransactionExecutor.ensureOpen (package:moor/src/runtime/executor/helpers/engines.dart:0)
06-18 19:49:37.631 17135 17239 I flutter : <asynchronous suspension>
06-18 19:49:37.631 17135 17239 I flutter :

[simolus3]

So it looks like this is happening in a transaction. That would indicate that delegate.db is not null here, but transaction somehow is.

https://github.com/simolus3/moor/blob/b5cc9b974d34d6227d50a0b1b21c88e91c628736/extras/encryption/lib/encrypted_moor.dart#L96-L99

Judging by the <optimized out> it looks like this happened in release mode? I've added an assert(transaction != null) for further diagnosis, but if it only happens on release builds that might not be too useful.

[Sorunome]

Unfortunately I have a bad test environment due to having no google services until my test phone arrives (should be tomorrow), so i had get our CI to generate (release) builds to get someone else to test it, thus the release builds. ^^" Will gladly look more into it more once the test device arrives, do you have any specifics I should look into?

[simolus3]

You could temporarily switch to the latest develop version for encrypted_moor:

  encrypted_moor:
    git:
      url: https://github.com/simolus3/moor.git
      path: extras/encryption
      ref: develop

At the moment the only change is the assert statement when starting a transaction. If that assertion fails, there's likely something wrong with the underlying sqlite library. That would be worth looking into. If it doesn't and you get the error, you could check the class where that happens (e.g. check if it's really _SqfliteTransactionExecutor).

[Sorunome]

Alright, here is a stacktrace from a develop build using the master branch for encrypted moor.

I/flutter (32543): +++++++++++++++++
I/flutter (32543): Sending text: Meow
I/flutter (32543): [Error] Failed to send message: NoSuchMethodError: The method 'rawInsert' was called on null.
I/flutter (32543): Receiver: null
I/flutter (32543): Tried calling: rawInsert("INSERT OR REPLACE INTO events (client_id, event_id, room_id, sort_order, origin_server_ts, sender, type, unsigned, content, prev_content, state_key, status) VALUES (:client_id, :event_id, :room_id, :sort_order, :origin_server_ts, :sender, :type, :unsigned, :content, :prev_content, :state_key, :status)", Instance(length:12) of '_GrowableList')
I/flutter (32543): #0      Object.noSuchMethod (dart:core-patch/object_patch.dart:53:5)
I/flutter (32543): #1      _SqfliteExecutor.runInsert (package:encrypted_moor/encrypted_moor.dart:135:15)
I/flutter (32543): #2      _ExecutorWithQueryDelegate.runInsert.<anonymous closure> (package:moor/src/runtime/executor/helpers/engines.dart:69:19)
I/flutter (32543): #3      _ExecutorWithQueryDelegate._synchronized (package:moor/src/runtime/executor/helpers/engines.dart:25:26)
I/flutter (32543): #4      _ExecutorWithQueryDelegate.runInsert (package:moor/src/runtime/executor/helpers/engines.dart:67:12)
I/flutter (32543): #5      QueryEngine.customInsert.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:245:25)
I/flutter (32543): #6      QueryEngine._customWrite.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:266:48)
I/flutter (32543): #7      QueryEngine.doWhenOpened.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:98:64)
I/flutter (32543): #8      _rootRunUnary (dart:async/zone.dart:1192:38)
I/flutter (32543): #9      _CustomZone.runUnary (dart:async/zone.dart:1085:19)
I/flutter (32543): #10     _FutureListener.handleValue (dart:async/future_impl.dart:141:18)
I/flutter (32543): #11     Future._propagateToListeners.handleValueCallback (dart:async/future_impl.dart:682:45)
I/flutter (32543): #12     Future._propagateToListeners (dart:async/future_impl.dart:711:32)
I/flutter (32543): #13     Future._completeWithValue (dart:async/future_impl.dart:526:5)
I/flutter (32543): #14     _AsyncAwaitCompleter.complete (dart:async-patch/async_patch.dart:36:15)
I/flutter (32543): #15     _completeOnAsyncReturn (dart:async-patch/async_patch.dart:298:13)
I/flutter (32543): #16     _TransactionExecutor.ensureOpen (package:moor/src/runtime/executor/helpers/engines.dart)
I/flutter (32543): <asynchronous suspension>
I/flutter (32543): #17     QueryEngine.doWhenOpened (package:moor/src/runtime/api/query_engine.dart:98:21)
I/flutter (32543): #18     QueryEngine._customWrite (package:moor/src/runtime/api/query_engine.dart:266:22)
I/flutter (32543): #19     QueryEngine.customInsert (package:moor/src/runtime/api/query_engine.dart:239:12)
I/flutter (32543): #20     _$Database.storeEvent (package:famedlysdk/src/database/database.g.dart:5274:12)
I/flutter (32543): #21     Database.storeEventUpdate (package:famedlysdk/src/database/database.dart:325:15)
I/flutter (32543): #22     Room.sendEvent.<anonymous closure> (package:famedlysdk/src/room.dart:704:29)
I/flutter (32543): #23     QueryEngine.transaction.<anonymous closure>.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:360:38)
I/flutter (32543): #24     _rootRun (dart:async/zone.dart:1184:13)
I/flutter (32543): #25     _CustomZone.run (dart:async/zone.dart:1077:19)
I/flutter (32543): #26     _runZoned (dart:async/zone.dart:1619:10)
I/flutter (32543): #27     runZoned (dart:async/zone.dart:1539:10)
I/flutter (32543): #28     QueryEngine._runEngineZoned (package:moor/src/runtime/api/query_engine.dart:426:12)
I/flutter (32543): #29     QueryEngine.transaction.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:357:14)
I/flutter (32543): #30     QueryEngine.doWhenOpened.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:98:64)
I/flutter (32543): #31     _rootRunUnary (dart:async/zone.dart:1192:38)
I/flutter (32543): #32     _CustomZone.runUnary (dart:async/zone.dart:1085:19)
I/flutter (32543): #33     _FutureListener.handleValue (dart:async/future_impl.dart:141:18)
I/flutter (32543): #34     Future._propagateToListeners.handleValueCallback (dart:async/future_impl.dart:682:45)
I/flutter (32543): #35     Future._propagateToListeners (dart:async/future_impl.dart:711:32)
I/flutter (32543): #36     Future._completeWithValue (dart:async/future_impl.dart:526:5)
I/flutter (32543): #37     _AsyncAwaitCompleter.complete (dart:async-patch/async_patch.dart:36:15)
I/flutter (32543): #38     _completeOnAsyncReturn (dart:async-patch/async_patch.dart:298:13)
I/flutter (32543): #39     BasicLock.synchronized (package:synchronized/src/basic_lock.dart)
I/flutter (32543): <asynchronous suspension>
I/flutter (32543): #40     DelegatedDatabase.ensureOpen (package:moor/src/runtime/executor/helpers/engines.dart:238:25)
I/flutter (32543): #41     QueryEngine.doWhenOpened (package:moor/src/runtime/api/query_engine.dart:98:21)
I/flutter (32543): #42     QueryEngine.transaction (package:moor/src/runtime/api/query_engine.dart:353:27)
I/flutter (32543): #43     Room.sendEvent (package:famedlysdk/src/room.dart:703:28)
I/flutter (32543): #44     Room.sendTextEvent (package:famedlysdk/src/room.dart:448:12)
I/flutter (32543): #45     ChatRoomState._onSendText (package:famedly/views/chat_room.dart:267:10)
I/flutter (32543): #46     InputFieldState.submit (package:famedly/components/chat_room/input_field.dart:112:24)
I/flutter (32543): #47     InputFieldState._buildTextInput.<anonymous closure> (package:famedly/components/chat_room/input_field.dart:226:17)
I/flutter (32543): #48     _InkResponseState._handleTap (package:flutter/src/material/ink_well.dart:779:19)
I/flutter (32543): #49     _InkResponseState.build.<anonymous closure> (package:flutter/src/material/ink_well.dart:862:36)
I/flutter (32543): #50     GestureRecognizer.invokeCallback (package:flutter/src/gestures/recognizer.dart:182:24)
I/flutter (32543): #51     TapGestureRecognizer.handleTapUp (package:flutter/src/gestures/tap.dart:504:11)
I/flutter (32543): #52     BaseTapGestureRecognizer._checkUp (package:flutter/src/gestures/tap.dart:282:5)
I/flutter (32543): #53     BaseTapGestureRecognizer.handlePrimaryPointer (package:flutter/src/gestures/tap.dart:217:7)
I/flutter (32543): #54     PrimaryPointerGestureRecognizer.handleEvent (package:flutter/src/gestures/recognizer.dart:475:9)
I/flutter (32543): #55     PointerRouter._dispatch (package:flutter/src/gestures/pointer_router.dart:76:12)
I/flutter (32543): #56     PointerRouter._dispatchEventToRoutes.<anonymous closure> (package:flutter/src/gestures/pointer_router.dart:122:9)
I/flutter (32543): #57     _LinkedHashMapMixin.forEach (dart:collection-patch/compact_hash.dart:379:8)
I/flutter (32543): #58     PointerRouter._dispatchEventToRoutes (package:flutter/src/gestures/pointer_router.dart:120:18)
I/flutter (32543): #59     PointerRouter.route (package:flutter/src/gestures/pointer_router.dart:106:7)
I/flutter (32543): #60     GestureBinding.handleEvent (package:flutter/src/gestures/binding.dart:218:19)
I/flutter (32543): #61     GestureBinding.dispatchEvent (package:flutter/src/gestures/binding.dart:198:22)
I/flutter (32543): #62     GestureBinding._handlePointerEvent (package:flutter/src/gestures/binding.dart:156:7)
I/flutter (32543): #63     GestureBinding._flushPointerEventQueue (package:flutter/src/gestures/binding.dart:102:7)
I/flutter (32543): #64     GestureBinding._handlePointerDataPacket (package:flutter/src/gestures/binding.dart:86:7)
I/flutter (32543): #65     _rootRunUnary (dart:async/zone.dart:1196:13)
I/flutter (32543): #66     _CustomZone.runUnary (dart:async/zone.dart:1085:19)
I/flutter (32543): #67     _CustomZone.runUnaryGuarded (dart:async/zone.dart:987:7)
I/flutter (32543): #68     _invoke1 (dart:ui/hooks.dart:275:10)
I/flutter (32543): #69     _dispatchPointerDataPacket (dart:ui/hooks.dart:184:5)

However, using the develop branch for encrypted moor, that bug seems to have vanished, if doing a debug build. A production build still gives the error:

I/flutter ( 2654): [Error] Failed to send message: NoSuchMethodError: The method 'rawInsert' was called on null.
I/flutter ( 2654): Receiver: null
I/flutter ( 2654): Tried calling: rawInsert()
I/flutter ( 2654): #0      _SqfliteExecutor.runInsert (package:encrypted_moor/encrypted_moor.dart:140)
I/flutter ( 2654): #1      _ExecutorWithQueryDelegate.runInsert.<anonymous closure> (package:moor/src/runtime/executor/helpers/engines.dart:69)
I/flutter ( 2654): #2      _ExecutorWithQueryDelegate._synchronized (package:moor/src/runtime/executor/helpers/engines.dart:25)
I/flutter ( 2654): #3      _ExecutorWithQueryDelegate.runInsert (package:moor/src/runtime/executor/helpers/engines.dart:67)
I/flutter ( 2654): #4      QueryEngine.customInsert.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:245)
I/flutter ( 2654): #5      QueryEngine._customWrite.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:266)
I/flutter ( 2654): #6      QueryEngine.doWhenOpened.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart:98)
I/flutter ( 2654): #7      _rootRunUnary (dart:async/zone.dart:1192)
I/flutter ( 2654): #8      _CustomZone.runUnary (dart:async/zone.dart:1085)
I/flutter ( 2654): #9      _FutureListener.handleValue (dart:async/future_impl.dart:141)
I/flutter ( 2654): #10     Future._propagateToListeners.handleValueCallback (dart:async/future_impl.dart:682)
I/flutter ( 2654): #11     Future._propagateToListeners (dart:async/future_impl.dart:711)
I/flutter ( 2654): #12     Future._completeWithValue (dart:async/future_impl.dart:526)
I/flutter ( 2654): #13     _AsyncAwaitCompleter.complete (dart:async-patch/async_patch.dart:36)
I/flutter ( 2654): #14     _completeOnAsyncReturn (dart:async-patch/async_patch.dart:298)
I/flutter ( 2654): #15     _TransactionExecutor.ensureOpen (package:moor/src/runtime/executor/helpers/engines.dart:0)

That is still ont he rawInsert line, and the assert is def. present.

EDIT: By adding extra debug logging i verified that startTransaction of _SqfliteTransactionDelegate is indeed being called.

EDIT2: it appears that the catchError() block inside of startTransaction is actually called, with the error being Exception: artificial exception to rollback the transaction. The stacktrace is somehow empty?

EDIT3: It appears that once of the errors is DatabaseException(error code 1: cannot start a transaction within a transaction) sql 'BEGIN IMMEDIATE' args []} - which is odd, as we shouldn't have that at all. Maybe if the push helper does not correctly close a transaction and then something weird happens? Are there some global objects underneath the hood which could keep transactions open? Is there a way to make sure all existing transactions are closed?

[Sorunome]

After more debugging it appears to be an issue with the push helper and the main thread are both running at the same time: While they don't have shared variables, and thus you can't re-use the same database object between them, if one is inside a transaction the other one will also think it was inside a transaction. How they manage to share that information is beyond me, but this seems to be the core issue here.

As such, it might actually be more a problem with firebase messaging needing a way to communicate with the master isolate, than an issue with moor. Thanks a lot for the debugging help, though!