Joplin terminal server crashes when deleting a notebok with note(s) inside, through data API

[gri38]

Operating system

Linux

Joplin version

Joplin CLI Client joplin 3.0.1 (prod, linux)

Desktop version info

No response

Current behaviour

1. I run Joplin terminal as a server

2. I create a notebook NB_TO_DELETE with a note inside: NOTE_TO_DELETE

3. Through data api, I can check that setup:

   get request: path=/folders
   => [{'id': 'b43b6b4d544141f69db92376fb09da1a', 'parent_id': '', 'title': 'NB_TO_DELETE', 'deleted_time': 0}]

   get request: path=/notes
   => [{'id': '3011bb46f3a140958ae13e4a1587e71f', 'parent_id': 'b43b6b4d544141f69db92376fb09da1a', 'title': 'NOTE_TO_DELETE', 'deleted_time': 0}]

4. Then, I delete the notebook:
   delete request: path=/folders/b43b6b4d544141f69db92376fb09da1a

5. That makes the app crash with that error:

/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database-driver-node.js:23
                const output = new Error(msg.join(': '));
                               ^

Error: On query {"sql":"SELECT id,title,is_todo,todo_completed,todo_due,parent_id,encryption_applied,order,markup_language,is_conflict,is_shared,share_id,deleted_time,updated_time,user_updated_time,user_created_time FROM notes WHERE deleted_time > 0 ORDER BY undefined COLLATE NOCASE ASC","params":[]}: Error: SQLITE_ERROR: no such column: undefined: SELECT id,title,is_todo,todo_completed,todo_due,parent_id,encryption_applied,order,markup_language,is_conflict,is_shared,share_id,deleted_time,updated_time,user_updated_time,user_created_time FROM notes WHERE deleted_time > 0 ORDER BY undefined COLLATE NOCASE ASC:
    at DatabaseDriverNode.sqliteErrorToJsError (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database-driver-node.js:23:18)
    at JoplinDatabase.sqliteErrorToJsError (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database.js:36:30)
    at JoplinDatabase.<anonymous> (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database.js:141:36)
    at Generator.throw (<anonymous>)
    at rejected (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database.js:6:65)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'SQLITE_ERROR'
}

Node.js v18.20.4

we can see we have an ORDER BY undefined... which causes the crashes.

Expected behaviour

Delete the notebook and all notebooks and notes inside.

Logs

No response

[VANSH3104]

@gri38 can you explain more about it

[gri38]

Ok, I'll detail from scratch.

• start a docker container to build and run joplin terminal

docker container run --rm -ti node:18-alpine /bin/sh

• in the container:

# install joplin
mkdir /app
NPM_CONFIG_PREFIX=/app/joplin npm install --production -g joplin

# start server in backgroud: 
/app/joplin/bin/joplin server start &

# install curl
apk update && apk add curl

# get api token in TOKEN env var:
export TOKEN=$(grep '"api.token"' /root/.config/joplin/settings.json | cut -d '"' -f 4)

# now let's list the notebooks with curl:
curl http://localhost:41184/folders?token=$TOKEN
# ==> outputs:  {"items":[],"has_more":false}

# let's create a notebook and a note inside
/app/joplin/bin/joplin mkbook NB_TO_DELETE
/app/joplin/bin/joplin mknote NOTE_TO_DELETE
/app/joplin/bin/joplin mv NOTE_TO_DELETE NB_TO_DELETE

# let's check we have a notebook NB_TO_DELETE with the note NOTE_TO_DELETE inside:
curl http://localhost:41184/folders?token=$TOKEN
# ==> {"items":[{"id":"699f07047ec54c96ad4441f0940e1cea","parent_id":"","title":"NB_TO_DELETE","deleted_time":0}],"has_more":false}
curl http://localhost:41184/notes?token=$TOKEN
# ==> {"items":[{"id":"5360244c5c064f378b11dd489f704793","parent_id":"699f07047ec54c96ad4441f0940e1cea","title":"NOTE_TO_DELETE","deleted_time":0}],"has_more":false}

So we can now reproduce the bug by deleting the notebook NB_TO_DELETE of id 699f07047ec54c96ad4441f0940e1cea, which has a note inside (NOTE_TO_DELETE):

curl -X DELETE http://localhost:41184/folders/699f07047ec54c96ad4441f0940e1cea?token=$TOKEN

Woops it doesn't crash ;-)

If I input again the curl delete, it will crash:

/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database-driver-node.js:23
                const output = new Error(msg.join(': '));
                               ^

Error: On query {"sql":"SELECT `id`,`title`,`is_todo`,`todo_completed`,`todo_due`,`parent_id`,`encryption_applied`,`order`,`markup_language`,`is_conflict`,`is_shared`,`share_id`,`deleted_time`,`updated_time`,`user_updated_time`,`user_created_time` FROM `notes` WHERE deleted_time > 0 ORDER BY `undefined` COLLATE NOCASE ASC","params":[]}: Error: SQLITE_ERROR: no such column: undefined: SELECT `id`,`title`,`is_todo`,`todo_completed`,`todo_due`,`parent_id`,`encryption_applied`,`order`,`markup_language`,`is_conflict`,`is_shared`,`share_id`,`deleted_time`,`updated_time`,`user_updated_time`,`user_created_time` FROM `notes` WHERE deleted_time > 0 ORDER BY `undefined` COLLATE NOCASE ASC:
    at DatabaseDriverNode.sqliteErrorToJsError (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database-driver-node.js:23:18)
    at JoplinDatabase.sqliteErrorToJsError (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database.js:36:30)
    at JoplinDatabase.<anonymous> (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database.js:141:36)
    at Generator.throw (<anonymous>)
    at rejected (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database.js:6:65)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'SQLITE_ERROR'
}

Node.js v18.20.4

But I would say it's normal: the notebook doesn't exist anymore.

It shouldn't crash, but the bug is less serious.

When I reported the bug, I wasn't using curl directly, but python package joppy in version 0.0.5... I'll have to do some tests again !

[gri38]

I've just tested again with joppy python... and it works !! I don't understand, I'm sure I didn't delete several times when I opened the bug.

Today my tests has been done with:

Joplin CLI Client

Copyright © 2016-2024 Laurent Cozic
joplin 3.0.1 (prod, linux)

Client ID: ea804479ba4044f4a9cd32eed5051bc3
Sync Version: 3
Profile Version: 47
Keychain Supported: No

It seems it's the same as last time. Is there any chance I get a fix by installing joplin again ?
I'm confused

[gri38]

OK !!! I produced the bug again ;-)

You do exactly as explained above but the note should have some content.
So after the note is created, I edit the note with joplin terminal interface, and I add 2 lines on the note content)

Then, the deletion makes joplin terminal server crash:

23:18:01: Request: DELETE /folders/600e3bf0c64048aba93a9508b3365c4f?token=0ee9398aed01075b61d13cb055989b02a5fabd0aeb3ae2d1697fdf6ec7e8d7b2c30673e2bf0221361a1768d7a582723cd33ae0b3cdfa189524d6fab628c3fd5b
/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database-driver-node.js:23
                const output = new Error(msg.join(': '));
                               ^

Error: On query {"sql":"SELECT `id`,`title`,`is_todo`,`todo_completed`,`todo_due`,`parent_id`,`encryption_applied`,`order`,`markup_language`,`is_conflict`,`is_shared`,`share_id`,`deleted_time`,`updated_time`,`user_updated_time`,`user_created_time` FROM `notes` WHERE deleted_time > 0 ORDER BY `undefined` COLLATE NOCASE ASC","params":[]}: Error: SQLITE_ERROR: no such column: undefined: SELECT `id`,`title`,`is_todo`,`todo_completed`,`todo_due`,`parent_id`,`encryption_applied`,`order`,`markup_language`,`is_conflict`,`is_shared`,`share_id`,`deleted_time`,`updated_time`,`user_updated_time`,`user_created_time` FROM `notes` WHERE deleted_time > 0 ORDER BY `undefined` COLLATE NOCASE ASC:
    at DatabaseDriverNode.sqliteErrorToJsError (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database-driver-node.js:23:18)
    at JoplinDatabase.sqliteErrorToJsError (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database.js:36:30)
    at JoplinDatabase.<anonymous> (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database.js:141:36)
    at Generator.throw (<anonymous>)
    at rejected (/app/joplin/lib/node_modules/joplin/node_modules/@joplin/lib/database.js:6:65)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'SQLITE_ERROR'
}

Node.js v18.20.4

[1]+  Done(1)                    /app/joplin/bin/joplin server start