(Bug report) UNIQUE constraint failure on upgrade to DB version 212

[MuirlandOracle]

Trilium Version

0.57.5 -> 0.58.4

What operating system are you using?

Ubuntu

What is your setup?

Local + server sync

Operating System Version

Ubuntu 20.04.4 LTS (Docker Server)

Description

Getting an SQLite UNIQUE constraint failure when updating from 0.57.5 (Docker image tag 0.57-latest db version 197), to 0.58.4 (0.58-latest, db version 212). Trilium server is deployed containerised (Docker Compose alongside a backup container) and produces the error trace below when switching to the new container.

I haven't seen anyone else with issues here, so I suspect this might be an edge case with my specific DB, but any help with tracking down where the branchId conflict is, and what has caused this, would be appreciated :)

Error logs

Posting the Docker Compose log as it is a lot more verbose than the output in the latest log file which just shows the server restarting with no error messages. Can provide that if requested :)

trilium_1    | No USER_UID specified, leaving 1000
trilium_1    | No USER_GID specified, leaving 1000
trilium_1    | App db version is 212, while db version is 197. Migration needed.
trilium_1    | DB size: 378114 KB
trilium_1    | Trusted reverse proxy: false
trilium_1    | App HTTP server starting up at port 8080
trilium_1    | {
trilium_1    |   "appVersion": "0.58.4",
trilium_1    |   "dbVersion": 212,
trilium_1    |   "syncVersion": 29,
trilium_1    |   "buildDate": "2023-01-11T23:44:33+01:00",
trilium_1    |   "buildRevision": "bdfdc0402ddb23e9af002580f368bc52e4268b3a",
trilium_1    |   "dataDirectory": "/home/node/trilium-data",
trilium_1    |   "clipperProtocolVersion": "1.0",
trilium_1    |   "utcDateTime": "2023-01-14T21:24:20.790Z"
trilium_1    | }
trilium_1    | CPU model: Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz, logical cores: 2 freq: 2399 Mhz
trilium_1    | Listening on port 8080
trilium_1    | Created backup at /home/node/trilium-data/backup/backup-before-migration.db
trilium_1    | Attempting migration to version 198
trilium_1    | Migration with SQL script: UPDATE branches SET branchId = '_hidden__search' WHERE parentNoteId = 'hidden' AND noteId = 'search';
trilium_1    | UPDATE branches SET branchId = 'root__globalNoteMap' WHERE parentNoteId = 'singles' AND noteId = 'globalnotemap';
trilium_1    | UPDATE branches SET branchId = '_hidden__sqlConsole' WHERE parentNoteId = 'hidden' AND noteId = 'sqlconsole';
trilium_1    | UPDATE branches SET branchId = 'root__hidden' WHERE parentNoteId = 'root' AND noteId = 'hidden';
trilium_1    | UPDATE branches SET branchId = '_hidden__bulkAction' WHERE parentNoteId = 'hidden' AND noteId = 'bulkaction';
trilium_1    | UPDATE branches SET branchId = '_hidden__share' WHERE parentNoteId = 'root' AND noteId = 'share';
trilium_1    |
trilium_1    | ERROR: error during migration to version 198: SqliteError: UNIQUE constraint failed: branches.branchId
trilium_1    |     at Database.exec (/usr/src/app/node_modules/better-sqlite3/lib/methods/wrappers.js:9:14)
trilium_1    |     at Object.executeScript (/usr/src/app/src/services/sql.js:200:25)
trilium_1    |     at executeMigration (/usr/src/app/src/services/migration.js:80:13)
trilium_1    |     at Function.<anonymous> (/usr/src/app/src/services/migration.js:56:17)
trilium_1    |     at Function.sqliteTransaction (/usr/src/app/node_modules/better-sqlite3/lib/methods/transaction.js:65:24)
trilium_1    |     at Object.transactional (/usr/src/app/src/services/sql.js:242:52)
trilium_1    |     at migrate (/usr/src/app/src/services/migration.js:51:9)
trilium_1    |     at async Object.migrateIfNecessary (/usr/src/app/src/services/migration.js:117:9)
trilium_1    |     at async initDbConnection (/usr/src/app/src/services/sql_init.js:41:5)
trilium_1    | ERROR: migration failed, crashing hard

[zadam]

Hi, I think I know what could cause this. I'm going to release a new patch version soon, hang on. Sorry for the trouble.

[MuirlandOracle]

Hey, thanks for looking into this so quickly! Absolutely no rush though -- this is an amazing piece of FOSS software, and goodness knows it must take up enough of your time without adding in instant support! :)

Still getting the same error with 0.58.5 unfortunately -- tried migrating from a known-working version of the database but getting the exact same output I'm afraid.

[zadam]

The release timing was just a coincidence. I will try to investigate some more what could be causing this ...

[MuirlandOracle]

Ah, gotcha! Apologies, misunderstanding on my part :)

Slight update -- I found a bunch nodes related to the new launcher that slipped through into the bottom of the tree in my v197 DB. Deleting those allowed for an update to v198 using 0.58.5, but it failed again updating to v199 with another UNIQUE constraint fail on entity_changes:

trilium_1    | ERROR: error during migration to version 199: SqliteError: UNIQUE constraint failed: entity_changes.entityName, entity_changes.entityId
trilium_1    |     at Database.exec (/usr/src/app/node_modules/better-sqlite3/lib/methods/wrappers.js:9:14)
trilium_1    |     at Object.executeScript (/usr/src/app/src/services/sql.js:200:25)
trilium_1    |     at executeMigration (/usr/src/app/src/services/migration.js:80:13)
trilium_1    |     at Function.<anonymous> (/usr/src/app/src/services/migration.js:56:17)
trilium_1    |     at Function.sqliteTransaction (/usr/src/app/node_modules/better-sqlite3/lib/methods/transaction.js:65:24)
trilium_1    |     at Object.transactional (/usr/src/app/src/services/sql.js:242:52)
trilium_1    |     at migrate (/usr/src/app/src/services/migration.js:51:9)
trilium_1    |     at async Object.migrateIfNecessary (/usr/src/app/src/services/migration.js:117:9)
trilium_1    |     at async initDbConnection (/usr/src/app/src/services/sql_init.js:41:5)
trilium_1    | ERROR: migration failed, crashing hard

At a guess a similar issue but for a different table? Your AND isDeleted = 0 worked for the v198 upgrade at least :)

[zadam]

One way to fix this would be to DELETE FROM entity_changes with some SQLite client. This table is not critical and it's content will be re-generated after the migration automatically.

The other option is to create an anonymized database and send it to me to zadam.apps@gmail.com - I should be able then to fix the migration.

[MuirlandOracle]

Aha, thanks! Tried that. It gets past 198 and fails on 199. Same UNIQUE constraint issue, this time on note_contents which I would rather suspect can not simply be cleared 😅

trilium_1    | ERROR: error during migration to version 199: SqliteError: UNIQUE constraint failed: note_contents.noteId
trilium_1    |     at Database.exec (/usr/src/app/node_modules/better-sqlite3/lib/methods/wrappers.js:9:14)
trilium_1    |     at Object.executeScript (/usr/src/app/src/services/sql.js:200:25)
trilium_1    |     at executeMigration (/usr/src/app/src/services/migration.js:80:13)
trilium_1    |     at Function.<anonymous> (/usr/src/app/src/services/migration.js:56:17)
trilium_1    |     at Function.sqliteTransaction (/usr/src/app/node_modules/better-sqlite3/lib/methods/transaction.js:65:24)
trilium_1    |     at Object.transactional (/usr/src/app/src/services/sql.js:242:52)
trilium_1    |     at migrate (/usr/src/app/src/services/migration.js:51:9)
trilium_1    |     at async Object.migrateIfNecessary (/usr/src/app/src/services/migration.js:117:9)
trilium_1    |     at async initDbConnection (/usr/src/app/src/services/sql_init.js:41:5)
trilium_1    | ERROR: migration failed, crashing hard

I can drop an anonymised database over to you if you would like?

[zadam]

I can drop an anonymised database over to you if you would like?

Yes please, that would be the best. Please send it to zadam.apps@gmail.com

[MuirlandOracle]

Sent :)

[pmixrst]

Hello,

I have the very same issue here, trying to upgrade from 0.53.2 to 0.60.4 fails at migration step 198 with ERROR: error during migration to version 198: SqliteError: UNIQUE constraint failed: branches.branchId.

Checking table branches, it appears that rows "after migration" already exists and row "before migration" still exist:

For instance, checking why UPDATE branches SET branchId = 'root__hidden' WHERE parentNoteId = 'root' AND noteId = 'hidden' AND isDeleted = 0; fails, I see these 2 rows:

1. SELECT * from branches WHERE parentNoteId = 'root' AND noteId = 'hidden' AND isDeleted = 0; yields this row:
   hidden|hidden|root|80||0|0||2022-02-12 11:45:57.524Z

2. SELECT * from branches WHERE branchId = 'root__hidden'; yields this row:
   root__hidden|_hidden|root|1000000029||0|0||2023-03-05 20:54:40.459Z

Hence the UNIQUE constraint violation... I tried to delete the rows "after migration" so migration step 198 completed but then it failed at step 199 again for another UNIQUE constraint violation.

As for what could have been caused this, my setup has 2 desktop installs (one on PC and one on Mac) and one Docker sync server. I started by upgrading the desktop versions which went well and the issue seemingly appeared when upgrading the Docker server. Could it be that "old rows" had been synced by the old server to the new desktop installs, hence the mix of "old" and "new" rows ? Supporting this hypothesis is that I have seen some rows in table attributes with deleteId 0204__migrate_bookmarks_to_clones or 0212__delete_all_attributes_of_named_notes which seem to indicate that some succesful migration was performed on the DB at some point.

How could I solve this issue ? I was thinking of starting with a fresh 0.60.4 and exporting all notes from 0.53.2 and then importing into 0.60.4, could this work or is there a better solution ?

[zadam]

Could it be that "old rows" had been synced by the old server to the new desktop installs, hence the mix of "old" and "new" rows ?

I wouldn't say so. When there are incompatible DB changes made, the sync protocol version get also incremented which will then mean that the sync will be refused (sync protocol has to be the same on both sides).

I was thinking of starting with a fresh 0.60.4 and exporting all notes from 0.53.2 and then importing into 0.60.4, could this work or is there a better solution ?

I think that would be the best & easiest solution. Export all notes with ZIP + HTML which should preserve everything except for all revisions.