Just a head up that the next Joplin Server update could potentially include a breaking change, depending on your data.
One of the database migration is going to add an "owner_id" column to the "items" table (where all notes, notebooks, etc. are stored), and automatically populate it. Normally that shouldn't take too long but you might want to make sure you won't need the server right away when you process this.
The second database migration will add a unique constraint on items.name and items.owner_id and that's where the breaking change might be. Normally this data is already unique because that's enforced by the application but in some rare cases, due to a race condition, there could be duplicate data in there. If that happens the migration will fail and the server will not start.
If that happens, you'll need to decide what to do with the data, as it's not possible to automatically decide. You can find all duplicates using this query:
select count(*), name
from items group by name, owner_id
having count(*) > 1;
Once you have the list of IDs you have a few options:
Find the corresponding item in Joplin (it can unfortunately be anything - a note, resource, folder, etc.), then delete it and sync.
Or, just delete the data directly in the database. You'll want to delete the corresponding item_id from the user_items table too.
But really in most cases you should be fine. Especially if you don't have that many notes it's unlikely you have duplicates.
The migration is already applied on Joplin Cloud so you won't need to do anything. There was one user affected by this - I've contacted him and since he wasn't using the account anymore he told me I could delete the duplicate notes.
Right, I should have tested the query first. You actually need to remove the "id" part from it (as in my now updated post). If it returns nothing you're good to go.
Thanks for the reply. Every app out there uses a different database, not easy to get accustomed to every piece of backend.
I am running this inside the db docker
psql -h localhost -U admin joplin
psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.
joplin=# select count(*), name, owner_id
joplin-# from items group by name, owner_id
joplin-# having count(*) > 1;
ERROR: column "owner_id" does not exist
LINE 1: select count(*), name, owner_id
This output does not seem to be helpful.
joplin-# delete from items where name='NAME OF FILE';
ERROR: syntax error at or near "ERROR"
LINE 1: ERROR: column "owner_id" does not exist
I am also seeing this maybe it is an important error
2021-11-13 04:13:41: [error] BaseModel:
2021-11-13 04:13:41: [error] App: ::ffff:192.168.160.1 Error: 1 row(s) should have been deleted but 0 row(s) were deleted. ID: zoQf1NDkA42KUAmfO8klftyOgjU97sE0
at ItemModel.<anonymous> (/home/joplin/packages/server/src/models/BaseModel.ts:355:65)
at Generator.next (<anonymous>)
at fulfilled (/home/joplin/packages/server/dist/models/BaseModel.js:5:58)
at runMicrotasks (<anonymous>)
at processTicksAndRejections (node:internal/process/task_queues:96:5)
2021-11-13 04:13:43: App: DELETE /api/items/root:/temp/timeCheck606767.txt: (500) (13543ms)
2021-11-13 04:13:43: App: PUT /api/items/root:/locks/sync_desktop_476f54c00d1f40858a4990cb2124cbf8.json:/content (200) (13545ms)