Mobile: failed decryption and sync

[t0dd]

Operating system

Android

Joplin version

3.1.8

Sync target

Joplin Cloud

What issue do you have?

I have Joplin running on three computers (Fedora Linux) and one phone (Android).
Zero real incidences for a very long time. Years.
They sync to Joplin Cloud. Encryption is enabled.

I just added another Android. It will be my "focus device".

Mobile versions for both phones is 3.1.8
Desktop versions for all three desktops is 3.1.24

PROBLEM:

• I CANNOT get the new phone to sync without error: Last error: [object Object]
• A number of folders remain encrypted.

CONFIGURATIONS ATTEMPTED:

• Shut done all instances except the problem phone so that there can't be a conflict during sync
• Uninstalled then reinstalled Joplin on mobile over and over and over again. Resyncing from scratch every time.
• I have tried both entering the one master password and saving and also the master password for each key.

ADDITIONAL INFO:

• My Joplin repo is very old and has 4 encryption keys maintained, but 3 of which are disabled.
  • I have only ONE master password. I plug that in twice where indicated. The one and only key that is enabled uses that same master password.
  • The old keys do have different passwords, I also tried plugging their passwords in individually as well. And still get the same result.
  • NOTE: the mobile app does not distinguish between these keys which is a bug IMHO
    (filed: disabled keys on mobile are not differientiated · Issue #11486 · laurent22/joplin · GitHub)
  • And the logs keep talking about a row too big to fit into CursorWindow:

12-07T18:33:07: RevisionService: maintenance:
{"message":"On query {\"sql\":\"SELECT * FROM revisions
WHERE item_updated_time < ?
ORDER BY item_updated_time
DESC\",\"params\":[1725838387440]}: Row too big to fit into CursorWindow requiredPos=920, totalRows=921","code":0}

• How do I …
  • (a) troubleshoot what is going on here (see log file),
  • and (b) is there a preferred way to start fresh, remove all of these keys and re-encrypt? And is that even recommended?
  • or (c) should I wait until v3.2+ is released to do something like a full refresh since it will be moving to a new encryption mechanism?

Note. You often ask for areas of improvement. Here's one: make encryption easy and fix the mobile client in that regard so that both desktop and mobile UIs are more similar. Also, the mobile app can sometimes be so sluggish it seems like it has stopped utterly, when it hasn't. But there is no "I'm thinking" feedback. Like, for example, when I save an encryption configuration. Or even just opening the notebook list sometimes.

I haven't had the heartache that others have had with mobile syncing, but I am seeing it now.

Thanks for any help offered. Note that this is a sizeable but not huge Joplin install: 5000 objects or so and a couple hundred MB.

Attached are the logs for two different installation sessions. The screenshot shows the error that both produce.

Screenshots

mobile-log3.log.txt (1.61 MB)

Log file

mobile-log3.log.txt (1.61 MB)
mobile-log4.log.txt (2.2 MB)

[personalizedrefriger]

mobile-log4.log.txt

12-07T19:52:17: Synchronizer: Error: Cannot acquire exclusive lock because there is an active sync lock for client: 2 #7ebca78259324b938e573b3fdad2a0e6
Code: hasSyncLock
Error: Cannot acquire exclusive lock because there is an active sync lock for client: 2

If the sync issue isn't caused by "Row too big to fit into CursorWindow requiredPos=920", it might be related to sync locks.

Sync locks have been removed in the latest pre-release and can be disabled in the latest stable version of Joplin from configuration > sync > advanced.

@t0dd

DESC\",\"params\":[1725838387440]}: Row too big to fit into CursorWindow requiredPos=920, totalRows=921","code":0}

The error message references RevisionService. Based on this, setting the note history setting to a large number may help. This setting can be found in Configuration > Note history > Keep note history for.

Note: This looks similar to this very old issue. A workaround was implemented that should increase the CursorWindow size to 50 MB. Based on this, either:

• The old revisions are cumulatively larger than 50 MB.
• The workaround isn't increasing the CursorWindow size.

[t0dd]

@personalizedrefriger

The error message references RevisionService. Based on this, setting the note history setting to a large number may help.

Ok. I turned off that lock. And then maxed out revision history setting … got the same result. (didn't download the log file — I will rerun that test sometime soon)

Then I simply disabled the revision history (and tuned off the sync lock). Sorta the same result, but maybe not.

New attachment:
mobile-log5.log.txt (132.5 KB)

I will rerun that other test with the maxed out revision history when I get another moment.

[t0dd]

So, in preparation for another test I opened Joplin on one of the desktops and on the working mobile phone. And … Joplin is now "Updated remote items" for all of the notes. I have been waiting hours for that to complete. But what triggered a resync of everything? Gah!

What I think I am going to do is

• shut everything down on all of the clients and obliterate the mobile installs
• then re-encrypt everything from the main client
  • let it sync until it is done
• turn everyone else back on and ... see what happens
• once they are sync, shut everyone back down and reinstall on the problematic mobile device

... and then maybe it will all work again. Dunno.

Otherwise, I may just nuke it all and start fresh from a backup. PITA but, if it simplifies things, then great.

[t0dd]

I'm at a loss. I, …

• uninstalled the mobile application on the "problem phone"
• synced all the working installations of Joplin (desktops and 1 mobile phone, android)
• shut them all down
• opened primary instance of Joplin (desktop, Linux)
• turned off sync locks on all
• re-encrypted everything
  Options > Synchronization > Encryption > Advanced > Re-encrypt Data
• resynced all working installations of Joplin
• shut all of them down

And then I tested the problem phone in two ways …

• TEST 1: revision history disabled
  • reinstalled Joplin on 2nd mobile device
  • deleted Welcome folder and emptied Trash
  • turned off sync locks
  • disabled revision history
  • disabled auto-sync
  • authorized Joplin Cloud
  • (password for 1 key requested): set master password for key
  • synced
  • results: mobile-log6.log.txt (1.7 MB)
  • result on screen:

    Created local items: 5647.
    Created remote items: 8.
    Fetched items: 7816/8016.
    Completed [date and time here] (2510s)
    Last error: [object Object]
    

 

• TEST 2: revision history maxed out
  • reinstalled Joplin on 2nd mobile device
  • deleted Welcome folder and emptied Trash
  • turned off sync locks
  • set revision history to maximum: 730
  • disabled auto-sync
  • authorized Joplin Cloud
  • (password for 1 key requested): set master password for key
  • synced
  • results: mobile-log7.log.txt (1.7 MB)
  • result on screen: precisely the same minus time and elapsed seconds

And so, I'm at a loss. I find it interesting that even though revision history is disabled, this message appears in the log:

12-10T09:23:02: RevisionService: maintenance: {"message":"On query {\"sql\":\"SELECT * FROM revisions WHERE item_updated_time < ? ORDER BY item_updated_time DESC\",\"params\":[1670768582153]}: Row too big to fit into CursorWindow requiredPos=920, totalRows=921","code":0}

But there is also this in there …

12-10T09:19:09: Synchronizer: {"message":"On query {\"sql\":\"SELECT * FROM revisions WHERE id IN ('b5e1f8d983a8498180a19e1742594a14','0e64010321fa49689ce45c3ae8de0140','e250428e5ff14cf8adcac9b1b35a0e27','82a293e669fe41629e214941159fd17c','503166a9dc6f4f7f838b2eccb9901171','456d16271fc44ccb8547f07a993f9784','0361f7ead3fe4f559ca4c48b69e95a65','69711bee5fca4d52a20df5f4f820581f','76571b8df76246bf99392d3d2b4f3567','6d0d01b12fe94daeb617358e4b4c9bc9','228230e302e34d62bae343cd9f7827a5','b261d6b1ae884ee5a2c512ed88f71259','341503566f044a9ba1a6deed8c314992', [ … a zillion IDs … ], ,'87fb8b9b43af4b26ad3dcc130ceec014','39e72f50b7584dde8f79224bb4e2431c')\",\"params\":[]}: Row too big to fit into CursorWindow requiredPos=138, totalRows=139","code":0}

[laurent]

If you are starting over, I think you should make sure you disable history on the desktop app, then make sure all revisions are deleted, and the deletions are synced to the server. Then when you start over with the mobile app, the large revisions should be gone.

[personalizedrefriger]

Before re-syncing everything (which could take a long time), here are a two other things to consider trying:

1. Use the web version of the mobile app on the problematic device.
   • This version of Joplin uses a different library for database access, which might not experience the "Row too big to fit into CursorWindow" issue.
2. Delete old revisions from a desktop app. (Only do this if you don't use note history.) To do this:
   • Double-check that you don't rely on note history.
   • In settings > note history, set "keep note history for" to 1 day.
   • Restart Joplin.
   • Wait 30 seconds.
   • Sync.

[t0dd]

@laurent

disable history on the desktop app

Yeah. I was trying to avoid that since I do actually sometimes use revision history. But … this is so incredibly annoying that I will live without for now.

I will try that first. I will let you know how it goes.

But. If I understand you correctly, the revision settings in each instance of the application determines that instance's view of the past, right? And that whoever has the longest setting will sync all of that to Joplin Cloud. I.e., everyone syncs all that that revision history regardless of the individual setting. Do I understand that correctly?

UPDATE: To answer my own question: From Note history | Joplin

Revision settings are global
Since all the revisions are synced across all devices, it means these settings are kind of global. So for example, if on one device you set it to keep revisions for 30 days, and on another to 100 days, the revisions older than 30 days will be deleted, and then this deletion will be synced. So in practice it means revisions are kept for whatever is the minimum number of days as set on any of the devices. In that particular case, the 100 days setting will be essentially ignored, and only the 30 days one will apply.

But that means I have to wait a full day before continuing this testing. Wee!

I have set one desktop to 1 day and disabled it everywhere else. And now I wait the day for the history to clear out.

[laurent]

Yes this is unfortunate, it's a bug we never managed to replicate so that makes it very hard to fix.

I've always hoped the bug would disappear eventually as mobile phones become more powerful but it seems it still happens. What phone model do you have?

[t0dd]

@laurent

What phone model do you have?

The phone is a Samsung S9. An older, but still reasonably powerful, 2018 phone.

Qualcomm Snapdragon 845 SoC
4 GB of RAM
64 GB of internal storage
Android ... 10! (ewwww)

I inherited it from my father (now deceased). I think he would be pleased that it is causing me heartache.

[t0dd]

@laurent

make sure all revisions are deleted, and the deletions are synced to the server

How precisely do we "make sure all revisions are deleted and the deletions are synced to the server." I set it to one day … over a day ago … and the disk usage in Joplin Cloud has not changed. (Previously, it was set at 90 days, I believe.) Is there something I am not doing? Or is there a better way to examine this?

Mind you, the diffs of my revisions possibly could have been less than 1MB difference (the level of detail shown by the cloud service) but I somehow doubt it. Anyway. Just wondering if there is an easier way to determine that revisions have been deleted and synced.

UPDATE: 2024-12-11 8:37pm UTC

• It's been over a day since I set main installation to 1 day revision history
• I reinstalled Joplin on the problem phone
• turned off sync locks and disabled revision history
• connected it to Joplin Cloud
• set the encryption
• Stalls at precisely the same spot with precisely the same error and Fetched items: 7816/8016

[t0dd]

I removed larger markdown notes as discussed here: "Row too big to fit into CursorWindow" errors when syncing from Joplin app on Android - #14 by t0dd

And I turned off OCR stuff. And then I waited a full day so that any revisions disappeared.

The largest markdown file that remains is 246MB. The largest HTML file is 1.5MB. The largest "resource" is a 20MB PDF. Those should all be managed just fine, right?

I got FURTHER this time. But still errors out eventually. Interestingly, so many Notebooks simply disappear though some of the notes that go in those Notebooks do exist. I can't really compare the inventories though, and so it is hard to tell. I don't see the "Encrypted" label anywhere. So, presumably everything it has access to is decrypted.

Here's a graphic showing my top-level trees. On the left is for my desktop and on the right is my phone. There are notebooks missing underneath these layers as well.

Here's my new log file. Note, I had to sync twice. Once that didn't error out but Notebooks were missing. The second time I synced, I got the usual "Laste error: [object Object]"

Log file:
mobile-log8.log.txt (1.8 MB)

[bobbyv]

I think i have exactly this same problem. I recently moved from Apple phone to Android Pixel 7 Pro. For me i like Joplin a lot. I'd like to continue using it. Having this OBJECT error on the phone is a killer, If it can't be resolved, I'll need to find another option. Sure seems like it's been "duplicatable" for some year now. Anyway, I'm not a programmer and i don't think i should have to become one to fix this error. the biggest problem i see is that i don't know if I'm losing notes or not.
So what can i provide (if anything) to help you solve this issue?? I did go through and remove all the pictures that didn't port over from iPhone NOTES, but it did not remove the error. From the info provided so far i don't see how to find what's causing the error.
Thanks.

[t0dd]

"Duplicatable" means the folks empowered to fix it have failed to duplicate it. It doesn't mean it isn't occurring in the wild.

[tirili]

I also encounter this problem. See here Joplin App Windows/Android/iPhone/Linux - Startover, fetch all from Server to resolve conflicts - #4 by tirili

[bobbyv]

I understand/understood, thus was i offering my services since i can duplicate it with ease every time.

[mrjo118]

From my experience attempting to remove note history, you need to change the note history to 1 day, then wait at least 24 hours, then do a sync on the same device, in order for the note history to be removed on the sync target. See Sync fails on Android mobile with error [object Object] · Issue #11571 · laurent22/joplin · GitHub which also details how to check how many revision items exist on the device. I can see from your new log that you have over 1000 revisions on your Android device, based on the cursor position in the error message.

EDIT: The service which deletes note history on the local device selects the note history into a cursor prior to deleting, so will probably fail if you do the note history deletion via the Android device. However, if you correctly delete the note revisions via Joplin desktop, then uninstall, reinstall and re-sync your Android device, providing any oversized notes / resources have been deleted already it should resolved your issue.

Therefore, check whether the 1000+ revision items still exist or not on Joplin desktop on the sync status page. If they still exist, then try using the steps in my linked comment instead, and see of that resolves the sync issue on Android.

[mrjo118]

I have had a read through all the issue reports relating to the CursorWindow errors and done some code analysis.

Here are the facts: The "Row too big to fit into CursorWindow" error will occur when the size of a single record returned in a select query is greater than the cursor window size which is defined for Android devices specifically. The default size set by Android is around 2mb for most platforms. There is a workaround implemented in the Joplin code to override this value and set it to 50mb.

I have created a note in Joplin desktop which is approx 4.8mb in size and exported it as a jex file (big.jex). Using Android emulator on Android 15, I am unable to reproduce the error by importing big.jex, with or without encryption enabled (note that when using encryption, the size of the sync object is almost 200% of the size of the note). However, if I remove the workaround implemented in MainApplication.kt and build the code locally, I am able to reproduce the error by simply importing big.jex (no sync setup is required).

This proves that the workaround implemented in MainApplication.kt does work, which is why the Joplin team have not been able to reproduce the error. Looking at the user feedback, in one example it was reported (in a version of Joplin which had this workaround implemented) that the error occurred selecting from revisions, despite not having any individual note which is 50mb or greater in size that could create a revision so large.

I think the most likely cause of the error is due to the logic to override the window size to 50mb not working on some devices. It does not appear to be related to the version of Android, or how powerful the phone is, as it has been reported on old and new phones including high end ones. Therefore I am guessing producing the issue is dependent on the bundled version of sqlite which comes with the Android device. This is supposed to be dependent on the version of Android, however the link here android.database.sqlite  |  Android Developers says "Some device manufacturers include different versions of SQLite on their devices".
My guess is that if the manufacturer bundles a non standard version of sqlite, it may not use the CursorWindow class, therefore rendering the workaround in Joplin useless.

In the issue reports regarding the error, users have experienced this issue when selecting from 1 of 3 tables: notes, resources and revisions. Therefore, in the event that the user had a device where the workaround does not work correctly, for each of these tables, it would be possible to produce the error in the following scenarios:
-Notes: Having any note which is greater than 2mb (~1mb if using encryption) would cause the error
-Resources: The resource is not stored in the database, however the OCR text is. Therefore if using OCR, then if the OCR text is greater than 2mb (~1mb if using encryption), this would cause the error
-Revisions: If at any point you have a note which is greater than 2mb (~1mb if using encryption) in size, if the large content is pasted all in one go, or if the revision service 'merges' multiple revisions where it grew gradually, a revision over over 2mb could be created and would cause the error. Once this revision exists on the Android device, it can only be deleted by uninstall, reinstall and re-sync of the profile on the device.

Therefore, if you are someone who has an affected device, you will have to bear in mind these limitations if using an Android device as a client, as this issue may not get fixed. For all other users who are not affected by this issue, the 50mb limit (up to 50% less if using encryption) instead will apply for each of the above scenarios. Even a 1mb file of plain text is pretty big though, so unless you're using OCR, the majority of users likely would not hit any of the limits.

[t0dd]

Thanks for all of this analysis.

@mrjo118

Therefore, if you are someone who has an affected device, you will have to bear in mind these limitations if using an Android device as a client, as this issue probably can't be fixed. For all other users who are not affected by this issue, the 50mb limit instead will apply for each of the above scenarios.

Hm. Assuming you are correct in your analysis, there is at least one possible solution: Joplin could ship it's own instance of sqlite. Many projects embed it. It would not be a stretch, but it would be "one more thing" to deal with.

Additionally, it sounds like there is some improvement to be made in how revision history is cleared out and how OCR data is maintained.

[mrjo118]

@t0dd You may have a point there. The documentation here GitHub - andpor/react-native-sqlite-storage: Full featured SQLite3 Native Plugin for React Native (Android and iOS) for the react native library used to make use of sqlite offers a mode to use a bundled version of sqlite instead of the device version on Android