Duplicate notes after encryption - How to remove notes with invalid parent_id (SOLVED)

Support
1

Operating system

Windows

Joplin version

3.3.12

Desktop version info

Desktop
Joplin 3.3.12 (prod, win32)

Device: win32, AMD Ryzen 9 7950X 16-Core Processor
Client ID: 42f2f9b1c6834541be54808d1c5463a2
Sync Version: 3
Profile Version: 47
Keychain Supported: Yes
Alternative instance ID: -

Revision: 4d790b6

Automate Notes: 1.0.2
Backup: 1.4.4
Conflict Resolution: 1.2.3
Cursor Sync: 2.1.0
Freehand Drawing: 3.0.1
Inline TODO: 1.7.4
Markdown Table: Sortable: 1.2.2
Note Tabs: 1.4.0
Persistent Editor Layout: 2.2.0
Templates: 2.4.0

Laptop:
Joplin 3.3.12 (prod, win32)

Device: win32, 12th Gen Intel(R) Core(TM) i9-12900HK
Client ID: 42f2f9b1c6834541be54808d1c5463a2
Sync Version: 3
Profile Version: 47
Keychain Supported: Yes
Alternative instance ID: -

Revision: 4d790b6

Automate Notes: 1.0.2
Backup: 1.4.2
Conflict Resolution: 1.2.3
Csv Import: 1.0.1
Cursor Sync: 2.1.0
Freehand Drawing: 3.0.1
Inline TODO: 1.7.1
Markdown Table: Sortable: 1.2.2
Note overview: 1.7.1
Note Tabs: 1.4.0
Note Variables: 2.0.0
Persistent Editor Layout: 2.2.0
Templates: 2.4.0

Sync target

WebDAV

What issue do you have?

I recently activated encryption using the directions stated here:

Did initial setup on desktop, then added laptop.

Issue:
Most (if not all) notes are now duplicated.

One appears to be in correct notebook path, the other appears to be at root.

I did backup, but that was 3 days ago, and I use Joplin daily. -- So reverting would be painful.

Standard search returns 1:1 duplicates for every note (every note displayed has a duplicate). -- See Screenshot

Ctrl-P + '' returns 1:1 duplicates for every note (every note displayed has a duplicate). -- See Screenshot

In both cases, one note has proper Notebook path and the other shows no path. -- see Screenshot

Clicking on second note (the duplicate) results in a dev console error -- See Screenshot

Thanks for any help!

Screenshots

Joplin_Dup_Combined.jpg
Joplin_Dup_Combined.jpg2368×3387 399 KB

Log file

20250528_155130 log - Partial 2.txt (120 KB)

2

In my ongoing attempts to resolve the above duplicate note issue, I created a comparison of a valid Note load and a Note load of the duplicate Note - which throws an error in the dev-tools (console, debug mode).

Screenshot 2025-06-13 010330
Screenshot 2025-06-13 0103302034×869 165 KB

The duplicate (zombie) notes all appear to have a parent_id that does not resolve.

Is there a reasonable way to search the Joplin database, via either Joplin, the Joplin API, or a SQLite tool (DB4S) and remove any notes that have a parent_id that does not resolve to a viable parent?

Link to data: Joplin Debug Log - Google Sheets

3

You could use sql, but it's hard to say if something would break if you delete entries from the note table in sql to be honest, so that would be done at your own risk.

I'm also surprised that notes with a non existent parent are still showing up in the UI. You could try to creating a new profile in Joplin and syncing it from scratch. Then see if the duplicates are still present.

4

First, the underlying issue:
A jex file was used to update Andoid after encryption. Jex files create duplicate notes. See:

This is almost certainly what caused the issue in the first place.

The following is the process that I used to resolve these issues:

On all devices:

  • Shutdown all instances of Joplin, excluding the one used for troubleshooting
    • for android: airplane mode, disconnect WiFi, power down device
    • for windows: closed all Joplin instances, power down device or set firewall block
    • for linux: closed all joplin instances (pid), power down device or set firewall block

On single Windows Desktop being used for troubleshooting:

  • Backup of Joplin database locally

    • use backup plugin to create full backup
    • ALSO, make copy of 'database.sqlite'

  • For the following, I used:

    • DB Browser for SQLite (DB4S)
    • Microsoft Excel
    • Notepad++

  • open 'database.sqlite' and extract the following tables as csv (actually tsv)

    • notes
    • folders

  • Use MS Query to load extracted files into Excel, with the following settings:

    • File Origin: 650001: Unicode (UTF-8)
    • Delimiter: Tab
    • Data Type Detection: Based on entire dataset

  • Searched 'folders: id' for existence of each 'notes: parent_id'

    • denote any 'notes' records that have non-existent 'parent_id'
    • created a sql 'delete' command for each of these notes
      • Excel formula: = "DELETE FROM notes WHERE id = '" & A2 & "';"
      • example result: DELETE FROM notes WHERE id = '670fe381e9f248218fef3067b6392eb8';

  • Update 'database.sqlite'

    • Deleted zombie notes using SQL commands
    • compact database
    • check database

  • Create Joplin backup using plugin

    • Start local Joplin
    • Confirm that duplicates were removed
    • Create local backup using plugin
      • This is now the master copy. All other sources need to be cleared.

  • Delete/clear local Joplin Content: Part 1

    • from within Joplin, delete all notes and notebooks
    • empty the trash folder
    • quit Joplin

  • Delete/clear local Joplin Content: Part 2

    • rename local profile directory from 'resources' to 'resources_old'

  • Update 'database.sqlite'

    • backup database file (again)
    • compact database
    • check database

  • Reset the cloud data

    • delete cloud database data
      • used WinSCP / webdav
      • renamed original parent folder
      • created new empty parent folder with original name
    • start local copy of Joplin
    • re-enabled Joplin net connection
    • Initiate database re-upload from local Joplin using standard Joplin sync
      • cancel after 20 or so notes
      • -- This will setup encryption keys and cloud folder structure.
    • on cloud
      • keep json files and folder structure
      • remove .md files
      • remove any resource files

  • Reset the local copy of Joplin

    • Sync local client using:

  • Confirm where you are at

    • local joplin should contain
      • No notes
      • No notebooks
      • previous sync settings, including encryption keys (in options)
      • previous plugins
    • local Joplin Profile directory should contain
      • notably smaller database.sqlite folder
      • empty 'resources' folder
    • cloud folder structure should contain
      • basic folder structure
      • info.json file
      • empty 'resources' folder
    • Local sync should result in
      • almost immediate completion
      • nothing exchanged with server
      • sync status showing
        • Note: 0/0
        • Folder: 0/0
        • etc. (all 0/0)

  • Follow instructions here: joplin-plugin-backup/FULLRESTORE.md at master · JackGruber/joplin-plugin-backup · GitHub

  • Error check, general confirmations.

1 Like