How to get rid of sqlite error

Support
1

Operating system

Windows

Joplin version

3.1.24

Desktop version info

Joplin 3.1.24 (prod, win32)

Client ID: f2646541b8454befae8b217e6393f6a8
Sync Version: 3
Profile Version: 47
Keychain Supported: Yes

Revision: d581264

Backup: 1.4.2

Sync target

Dropbox

What issue do you have?

Using Dropbox for storage, also using the Android app Joplin Mobile 3.1.8.

I wanted to attach a zip, figured I'd need to put that in Dropbox so it could be found by both the Windows and the Android apps.

So I created a folder in the Dropbox tree alongside .lock, .resource, .sync, locks, temp.

The folder was named "Nieuwe map", then renamed to "foto's", then to "aanhangsels". Later, I learned this wasn't necessary, so I deleted that folder and the zip.

The Windows app now comes up every time with an error. This is part of the log.

2025-01-09 14:39:39: Synchronizer: [warn] There was some errors:
2025-01-09 14:39:39: Synchronizer: [warn] Error: On query {"sql":"SELECT * FROM notes WHERE id IN ('foto's','aanhangsels','Nieuwe map','9424a9c42e984a728848a8503e...
Code: SQLITE_ERROR

The Android app, after syncing says "Last error: [object Object]"

From this answer, I went and installed DB Browser for sqlite and I found the log and the database, but I don't want to go in there and break anything.

I do know some SQL, so I'm OK running some queries, but I'm hoping someone can point me in the right direction.

Log file

joplin_error.txt (2.36 KB)

2

It looks like by uploading folders in the directory of the Joplin data on Dropbox, you have managed to trick Joplin into thinking that those folders were actually notes (this is probably a bug), and they have been added into the database for each Joplin client. The problem was when the folder name had a single quote in the name, that one in particular caused data in the database which would now permanently break certain sql queries.

I think to remedy the issue without risking data loss, you will need to delete any folders which you added onto dropbox directly, then delete the Joplin profile on each of your devices, and then resync them. In the case of mobile, you should uninstall and reinstall the app. In the case of desktop, you will need to close Joplin completely (not just minimised) and delete the Joplin profile directly in the location relevant for your OS.

EDIT: I assumed you don't have any unsynced note changes on your devices. If you do, then you need to do a jex export of your notes on each device with unsynced changes BEFORE you delete the profile, and you'll need to manually migrate those notes back into your collection (if you import a full jex backup, the existing notes will be duplicated)

1 Like
3

Thanks, but unfortunately, that didn't work as I hoped. Did I delete the profile correctly?

I think I had pretty much everything in sync before I started the operation.

The profile I assume is the directory I land in from the Open profile directory menu item under Help. That's C:\users\myname\.config\joplin-desktop in my case (Windows 10). It contains, among other stuff, database.sqlite. I zipped the entire joplin-desktop directory, and moved the zip elsewhere.
Joplin off, stopped. Also on other machine. Removed Android app.
Then deleted the directory.
Then restarted Joplin. Went into Options - Synchronisation, Set target to Dropbox, clicked Apply, which then got greyed out. I clicked Delete data and re-download from sync target. Nothing happened.
I then realised I had to re-authorise with Dropbox, did that, and hit Synchronise. I retrieved something like 2000 items in batches of 500, but not all my notes. Lots of stuff was missing.

So I deleted the profile again, restored the zip, and then everything was back again.

Including the error.

I just re-installed the Android app too. Resyncing it was just as incomplete as the Windows app at first.

I'm now going to do the jex export/import.

Edit: I deleted the profile folder, imported the jex from local disk, that went OK, and then synced to Dropbox. I did not delete the Dropbox store first. Should I?

Now I got everything back including the error which is now even longer.

4

Yes you did delete the profile correctly. And good call to make a zip backup of the directory as well for the joplin-desktop folder, keep hold of that for now.

Looking at the code, the folder name inputs to that sql look to be either coming from the dropbox api directly (meaning the folder either still exists on dropbox, or dropbox api for some reason is caching or returning a change history of some sort), or they are contained within the sync_items table of the sqlite db. I haven't looked too closely at how the delta api resolution works, so can't determine the details at this stage.

When you delete the Joplin profile, you delete the sqlite database, and when you create a new profile and re-sync it to dropbox, it will create a brand new sqlite database on the device, derived from the data on the sync target (dropbox in your case). Therefore as you're still getting the error when deleting the profile on desktop and android, this indicates the issue is with the sync target instead of the data in the sqlite db.

I haven't been able to reproduce the issue by adding folders with a single quote using file system sync, so it points more towards a dropbox api issue, but I'm just guessing here. I can however suggest something else to try to fix your sync though. Before you do that though, please could you do 2 things:

  1. Upload the full log file from Joplin desktop to the forum after reproducing the error, not just the filtered error log. It is log.txt under the joplin-desktop directory
  2. Before deleting anything, download the entire Joplin folder from your dropbox and keep hold of it for potentially aiding to find the root cause. If you right click the root folder in the dropbox web ui, there should be an option to download the whole folder as a zip file.

Next steps to try:
As you didn't delete the dropbox store, if the sync were to partially or fully progress successfully following the jex import you did, you would end up with some or all of your notes duplicated. In order to attempt to tackle the issue from the other side (fix the sync target), you can try the following:
DISCLAIMER: Doing these steps means you will lose all your note history, only proceed if you are fine with that. Note that the zip of the original joplin-desktop folder you made will still contain the note history, if you wanted to refer back to it offline in future
-On Windows, delete the current joplin-desktop folder and replace it with the one which you originally made a zip backup of
-Open Joplin desktop and make a clean jex backup from this note data (only necessary if the jex backup you created previously was created from the new profile which did not complete sync fully, rather than the original profile)
-Go to Help > Synchronisation Status. Take a screenshot of this screen for reference
-Close Joplin and delete the profile on all devices, as before
-Delete the entire folder containing the Joplin data on dropbox (the root folder, not just the contents)
-Open Joplin desktop, which will create a new profile. Delete the Welcome notebook
-Import your jex backup
-Go to Help > Synchronisation Status. Compare the 'total items' value for each of the types in the sync status section to your screenshot. These values should all match the original, except for the revisions, which will be 0. If they are matching, you can continue
-Setup sync target to dropbox and let it sync. It should now complete the sync successfully
-Setup sync target on your other devices, and they should also sync successfully
-In future, avoid adding any files / folders to Joplin directory on the sync target

1 Like
5

"Before deleting anything, download the entire Joplin folder from your dropbox and keep hold of it for potentially aiding to find the root cause. If you right click the root folder in the dropbox web ui, there should be an option to download the whole folder as a zip file."

Regarding this, if you have time either before or after fixing your profile, could you delete the joplin-desktop folder (back it up first if it is a version which is not yet backed up), and open Joplin desktop on a new profile. Then set up file system sync and point it to the unzipped contents of your dropbox joplin folder and post on the forum whether you still get an error or if it syncs successfully (if it still errors, then please post the full log as well)

EDIT: I have now been able to recreate the error using file system sync, but not using a folder. If I create a new text file and save it as hat's.txt to the joplin folder, it breaks the sync with the same error and omits the file extension (if the file was created before the last successful sync, then the error wont occur unless you modify the file first). If you download your joplin folder from dropbox as a zip and then extract the contents to windows, if you then use this search query in windows explorer, do you get any matches?:
filename:*'*

If you do, then either delete or rename the corresponding file on dropbox, depending on whether it is actually a note or not based on the contents, and that might fix the sync

1 Like
6

hank you Mr Jo 118, I really do appreciate the support. In summary: it's fixed now. Thank you.

Full detail below.

Most recent log pre fix operation, is attached.
Joplin_log_pre-fix.txt (887.9 KB)

I downloaded the entire Dropbox Joplin folder and did dir /s on foto*, "foto's.*", foto*.*, "foto'". None found.

You wrote:

the folder name inputs to that sql look to be either coming from the dropbox api directly (meaning the folder either still exists on dropbox, or dropbox api for some reason is caching or returning a change history of some sort), or they are contained within the sync_items table of the sqlite db.

Dropbox does keep a file version history (that's why I use it), but you don't get that in the download. From this, it seems the Dropbox API doesn't return it unless you ask for it.

Following your sync_items hint, I searched for a few of the hashes in the Error query using DB Browser for SQLite. None found. I made a dump of the entire database and searched it too. None either.

So from here

  • deleted Android app
  • uninstalled app from Windows PC B and stopped Dropbox sync on its ..Dropbox\Apps\Joplin, Dropbox then deletes the directory.
  • My laptop (PC A) has all the stuff I ran up in this process. On it, I also zipped ..\Dropbox\Apps\Joplin, then threw that out of the Dropbox sync.
  • Downloaded Apps\Joplin folder from Dropbox via web interface.
  • PC A also had the JEX with the complete collection export (from the restored first pre-fix attempt zip).

(Next is your recipe:)

  • Open Joplin, but clean JEX isn't necessary, the one I have (previous step) is OK.
  • Sync status screenshot had duplicate folder names, i.e. this is after restoring the JEX and then re-syncing to the existing dropbox folder.
  • Close Joplin and delete the profile on all devices - Check.
  • Delete the entire folder containing the Joplin data on dropbox - Check. Also gone from Android Dropbox, check.
  • Open Joplin desktop, which will create a new profile. Delete the Welcome notebook - Good idea! I got 3 already. Check
  • Import your jex backup - Check
  • Go to Help > Synchronisation Status. - Check. Comparing the 'total items', the numbers don't match, but notes old and new are in here, so I'm assumming the number mismatch is owing to duplicates, triplicates, etc.
  • Setup sync target to dropbox and let it sync. It should now complete the sync successfully - Yes it does. No Errors. Hurray!
  • In future, avoid adding any files / folders to Joplin directory on the sync target - Of course. Check.
    (end of recipe.)

Once again, thanks for the support.

I did not expect Joplin maintains a local database on each client. This is important to realise when tackling sync issues. What I'm used to so far is a central database for all clients (which has it's own record locking problems too).

7

I'm glad you have resolved your issue.

I had a deeper look at how the resolution of the delta api works. On dropbox, the delta file api makes use of this endpoint HTTP - Developers - Dropbox

The list_folder api is invoked with the 'include_deleted' parameter set to true, which means it was an intentional choice by the developers to return items which no longer exist, when using dropbox sync. I suspect on dropbox, a rename is considered as a creation of the new file / folder plus deletion of the original, rather than an update. I think this design choice is probably fine, however the root of the issue is lack of sanitisation of the input data, which then is compounded by not being able to remedy it by renaming the problem item if using dropbox sync.

8

Thanks, this makes sense.

on dropbox, a rename is considered as a creation of the new file / folder plus deletion of the original, rather than an update.

Creation and renaming happened well within the 5 minute sync interval, so that would make sense. However, for me as a user, creation and rename of a folder is not visible in Dropbox. I just tried. I only see the last name.

I don't know if this should become an issue (in the Github repo, I mean). After all, it's not documented expected behaviour.

9

I wouldn't expect the rename to be visible as a deletion in the dropbox ui, just from the api when specifying 'include_deleted' as is documented by the api spec

1 Like