search

zadam / trilium

Build your personal knowledge base with Trilium Notes
GNU Affero General Public License v3.0
27.2k stars 1.9k forks source link

database compatibility #1937

Closed ichenh closed 2 years ago

ichenh commented 3 years ago

I am trying to upgrade trilium from v0.45.7 to v0.47.2 by directly replacing the old directory with the latest one. But I found that I cannot open it on Win 10. Is it due to the problem of database compatibility or my stupid operation? How can I safely upgrade the app?

bwat47 commented 3 years ago

Do you have an AMD CPU? If so, might be the same as this user's issue: https://github.com/zadam/trilium/issues/1906

FWIW it works fine here with Windows 10 on an AMD CPU. I updated the same way you did, by replacing the old directory with the new one and it opened without issue

ichenh commented 3 years ago

Do you have an AMD CPU? If so, might be the same as this user's issue: #1906

FWIW it works fine here with Windows 10 on an AMD CPU. I updated the same way you did, by replacing the old directory with the new one and it opened without issue

@bwat47 Thank you for your reply. I use intel CPU, and this is the output in the Windows command line:

C:\Users\xxx>
App db version is 183, while db version is 170. Migration needed.
DB size: 297889 KB
App HTTP server starting up at port 37840
{
  "appVersion": "0.47.2",
  "dbVersion": 183,
  "syncVersion": 20,
  "buildDate": "2021-04-26T22:32:54+02:00",
  "buildRevision": "6f1b0b92fe8dcea736c15217c69512c7fee769cb",
  "dataDirectory": "C:\\Users\\xxx\\AppData\\Roaming\\trilium-data",
  "clipperProtocolVersion": "1.0"
}
CPU model: Intel(R) Core(TM) i7-8700T CPU @ 2.40GHz, logical cores: 12 freq: 2400 Mhz
Listening on port 37840
Created backup at C:\Users\xxx\AppData\Roaming\trilium-data\backup/backup-before-migration.db
Attempting migration to version 171
Migration with SQL script: DELETE FROM options WHERE name IN (
    'noteInfoWidget',
    'attributesWidget',
    'linkMapWidget',
    'noteRevisionsWidget',
    'whatLinksHereWidget',
    'codeNotesMimeTypes',
    'similarNotesWidget',
    'editedNotesWidget',
    'calendarWidget',
    'sidebarMinWidth',
    'sidebarWidthPercent',
    'showSidebarInNewTab',
    'hoistedNoteId'
);

Migration to version 171 has been successful.
Attempting migration to version 173
Migration with SQL script: CREATE TABLE IF NOT EXISTS "mig_entity_changes" (
                                                    `id`        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                                    `entityName`        TEXT NOT NULL,
                                                    `entityId`  TEXT NOT NULL,
                                                    `hash`      TEXT NOT NULL,
                                                    `sourceId` TEXT NOT NULL,
                                                    `isErased` INT NOT NULL,
                                                    `utcDateChanged` TEXT NOT NULL,
                                                    `isSynced` INTEGER NOT NULL);

INSERT INTO mig_entity_changes (id, entityName, entityId, hash, sourceId, isSynced, utcDateChanged, isErased)
SELECT id, entityName, entityId, '', sourceId, isSynced, utcChangedDate, 0 FROM entity_changes;

UPDATE mig_entity_changes SET isErased = (SELECT isErased FROM notes WHERE noteId = entityId) WHERE entityName = 'notes';
UPDATE mig_entity_changes SET utcDateChanged = COALESCE((SELECT utcDateModified FROM notes WHERE noteId = entityId), '2020-12-14 14:07:05.165Z') WHERE entityName = 'notes';

UPDATE mig_entity_changes SET isErased = (SELECT isErased FROM notes WHERE noteId = entityId) WHERE entityName = 'note_contents';

UPDATE mig_entity_changes SET isErased = (
    SELECT isErased
    FROM attributes
         JOIN notes USING(noteId)
    WHERE attributeId = entityId
) WHERE entityName = 'attributes';
UPDATE mig_entity_changes SET utcDateChanged = COALESCE((SELECT utcDateModified FROM attributes WHERE attributeId = entityId), '2020-12-14 14:07:05.165Z') WHERE entityName = 'attributes';

UPDATE mig_entity_changes SET isErased = (
    SELECT isErased
    FROM branches
    JOIN notes USING(noteId)
    WHERE branchId = entityId
) WHERE entityName = 'branches';
UPDATE mig_entity_changes SET utcDateChanged = COALESCE((SELECT utcDateModified FROM branches WHERE branchId = entityId), '2020-12-14 14:07:05.165Z') WHERE entityName = 'branches';

UPDATE mig_entity_changes SET isErased = (
    SELECT isErased
    FROM note_revisions
    WHERE noteRevisionId = entityId
) WHERE entityName = 'note_revisions';
UPDATE mig_entity_changes SET utcDateChanged = COALESCE((SELECT utcDateModified FROM note_revisions WHERE noteRevisionId = entityId), '2020-12-14 14:07:05.165Z') WHERE entityName = 'note_revisions';

UPDATE mig_entity_changes SET utcDateChanged = COALESCE((SELECT utcDateCreated FROM api_tokens WHERE apiTokenId = entityId), '2020-12-14 14:07:05.165Z') WHERE entityName = 'api_tokens';

UPDATE mig_entity_changes SET utcDateChanged = COALESCE((SELECT utcDateModified FROM note_contents WHERE noteId = entityId), '2020-12-14 14:07:05.165Z') WHERE entityName = 'note_contents';

UPDATE mig_entity_changes SET utcDateChanged = COALESCE((SELECT utcDateModified FROM note_revision_contents WHERE noteRevisionId = entityId), '2020-12-14 14:07:05.165Z') WHERE entityName = 'note_revision_contents';

UPDATE mig_entity_changes SET utcDateChanged = COALESCE((SELECT utcDateModified FROM options WHERE name = entityId), '2020-12-14 14:07:05.165Z') WHERE entityName = 'options';

UPDATE mig_entity_changes SET utcDateChanged = COALESCE((SELECT utcDateCreated FROM recent_notes WHERE noteId = entityId), '2020-12-14 14:07:05.165Z') WHERE entityName = 'options';

DROP TABLE entity_changes;
ALTER TABLE mig_entity_changes RENAME TO entity_changes;

CREATE UNIQUE INDEX `IDX_entityChanges_entityName_entityId` ON "entity_changes" (
                                                                                 `entityName`,
                                                                                 `entityId`
    );

DELETE FROM attributes WHERE noteId IN (SELECT noteId FROM notes WHERE isErased = 1);
DELETE FROM branches WHERE noteId IN (SELECT noteId FROM notes WHERE isErased = 1);
DELETE FROM note_contents WHERE noteId IN (SELECT noteId FROM notes WHERE isErased = 1);
DELETE FROM note_revision_contents WHERE noteRevisionId IN (
    SELECT noteRevisionId FROM note_revisions WHERE isErased = 1
);
DELETE FROM note_revisions WHERE isErased = 1;
DELETE FROM notes WHERE isErased = 1;

UPDATE entity_changes SET isErased = COALESCE((SELECT isErased FROM entity_changes AS sub WHERE sub.entityId = entity_changes.entityId AND sub.entityName = 'note_revisions'), 0) WHERE entityName = 'note_revision_contents';

ERROR: error during migration to version 173: SqliteError: NOT NULL constraint failed: mig_entity_changes.isErased
    at Database.exec (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\wrappers.js:9:14)
    at Object.executeScript (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:200:25)
    at Function.<anonymous> (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:17:17)
    at Function.sqliteTransaction (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\transaction.js:65:24)
    at Object.transactional (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:238:52)
    at executeMigration (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:11:9)
    at migrate (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:70:17)
    at async Object.migrateIfNecessary (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:113:9)
    at async initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:40:5)
ERROR: migration failed, crashing hard
Attempting migration to version 174
ERROR: error during migration to version 174: TypeError: The database connection is not open
    at Function.sqliteTransaction (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\transaction.js:63:9)
    at Object.transactional (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:238:52)
    at executeMigration (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:11:9)
    at migrate (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:70:17)
    at async Object.migrateIfNecessary (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:113:9)
    at async initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:40:5)
ERROR: migration failed, crashing hard
Attempting migration to version 175
ERROR: error during migration to version 175: TypeError: The database connection is not open
    at Function.sqliteTransaction (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\transaction.js:63:9)
    at Object.transactional (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:238:52)
    at executeMigration (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:11:9)
    at migrate (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:70:17)
    at async Object.migrateIfNecessary (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:113:9)
    at async initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:40:5)
ERROR: migration failed, crashing hard
Attempting migration to version 176
ERROR: error during migration to version 176: TypeError: The database connection is not open
    at Function.sqliteTransaction (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\transaction.js:63:9)
    at Object.transactional (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:238:52)
    at executeMigration (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:11:9)
    at migrate (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:70:17)
    at async Object.migrateIfNecessary (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:113:9)
    at async initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:40:5)
ERROR: migration failed, crashing hard
Attempting migration to version 177
ERROR: error during migration to version 177: TypeError: The database connection is not open
    at Function.sqliteTransaction (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\transaction.js:63:9)
    at Object.transactional (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:238:52)
    at executeMigration (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:11:9)
    at migrate (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:70:17)
    at async Object.migrateIfNecessary (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:113:9)
    at async initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:40:5)
ERROR: migration failed, crashing hard
Attempting migration to version 178
ERROR: error during migration to version 178: TypeError: The database connection is not open
    at Function.sqliteTransaction (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\transaction.js:63:9)
    at Object.transactional (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:238:52)
    at executeMigration (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:11:9)
    at migrate (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:70:17)
    at async Object.migrateIfNecessary (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:113:9)
    at async initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:40:5)
ERROR: migration failed, crashing hard
Attempting migration to version 179
The database connection is not open
The database connection is not open
Migration to version 179 has been successful.
Attempting migration to version 180
ERROR: error during migration to version 180: TypeError: The database connection is not open
    at Function.sqliteTransaction (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\transaction.js:63:9)
    at Object.transactional (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:238:52)
    at executeMigration (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:11:9)
    at migrate (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:70:17)
    at async Object.migrateIfNecessary (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:113:9)
    at async initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:40:5)
ERROR: migration failed, crashing hard
Attempting migration to version 181
ERROR: error during migration to version 181: TypeError: The database connection is not open
    at Function.sqliteTransaction (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\transaction.js:63:9)
    at Object.transactional (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:238:52)
    at executeMigration (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:11:9)
    at migrate (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:70:17)
    at async Object.migrateIfNecessary (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:113:9)
    at async initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:40:5)
ERROR: migration failed, crashing hard
Attempting migration to version 182
ERROR: error during migration to version 182: TypeError: The database connection is not open
    at Function.sqliteTransaction (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\transaction.js:63:9)
    at Object.transactional (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:238:52)
    at executeMigration (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:11:9)
    at migrate (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:70:17)
    at async Object.migrateIfNecessary (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:113:9)
    at async initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:40:5)
ERROR: migration failed, crashing hard
Attempting migration to version 183
ERROR: error during migration to version 183: TypeError: The database connection is not open
    at Function.sqliteTransaction (C:\portable\trilium-windows-x64\resources\app.asar\node_modules\better-sqlite3\lib\methods\transaction.js:63:9)
    at Object.transactional (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:238:52)
    at executeMigration (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:11:9)
    at migrate (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:70:17)
    at async Object.migrateIfNecessary (C:\portable\trilium-windows-x64\resources\app.asar\src\services\migration.js:113:9)
    at async initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:40:5)
ERROR: migration failed, crashing hard
The database connection is not open
TypeError: results is not iterable
    at Object.getMap (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:145:23)
    at Object.getOptionsMap (C:\portable\trilium-windows-x64\resources\app.asar\src\services\options.js:72:29)
    at Object.initStartupOptions (C:\portable\trilium-windows-x64\resources\app.asar\src\services\options_init.js:93:38)
    at initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:42:31)
TypeError: results is not iterable
    at Object.getMap (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql.js:145:23)
    at Object.getOptionsMap (C:\portable\trilium-windows-x64\resources\app.asar\src\services\options.js:72:29)
    at Object.initStartupOptions (C:\portable\trilium-windows-x64\resources\app.asar\src\services\options_init.js:93:38)
    at initDbConnection (C:\portable\trilium-windows-x64\resources\app.asar\src\services\sql_init.js:42:31)

I also tried to upgrade the app version by version, and I found that the newest version working well is v0.45.10 (corresponding to version 172 shown in the output?). But you are right that it works fine to directly upgrade an old version app to the latest one with the default database. So the problem is probably the database compatibility. One way to temporarily solve this problem is to export notes from the old version app and import them to the latest version app. But I failed since the app gets stuck when exporting to HTML format files.

zadam commented 3 years ago

@ichenh hi, thanks for the bug report and logs. Normally the migration should be fully automatic and without issues, but there's some unexpected value(s) in your database which cause trouble for the migration scripts. I should be able to fix the migration scripts to handle this situation and will release it as part of 0.47.3. In the meantime keep using the old version.

razinj commented 3 years ago

I can confirm that this is still an issue, just tried to update from zadam/trilium:0.45-latest to zadam/trilium:0.47-latest (even 0.47.3) but I'm still always greeted with the setup page, once I get back to the 0.45 version it works just fine.

I tried to find in the docs how to import a database but I can't find it, is there a way to do it ?

zadam commented 3 years ago

@razinj Can you include the logs? It's possible it's a different migration issue ...

ichenh commented 3 years ago

@razinj I don't know what happened, but I've successfully migrated the desktop app to the latest version and synced it to the server app. Maybe it's another kind of issue, so it's better to show your logs.

razinj commented 3 years ago

Hello @zadam @ichenh, I actually found how to export a backup and I created a new instance so basically I don't have log files now, however I will explain my setup and what I have tried to come up to that conclusion.

I have Trilium as a docker instance based on the zadam/trilium:0.45-latest image ran with the docker run command in order for me to upgrade/update the instance I stopped and deleted that instance (but I do have a persisted volume for the data directory) then I replaced the image with the newer one, ran it back again afterwards I went to the URL where the app lives (it's running behind a NGINX reverse proxy) I'm greeted with the setup page (as if I don't have data, user or anything -- and yes I'm sure it's the same persisted volume's directory).

Also, thank you for the quick response -- I should've waited for you, anyways the issue is solved and I didn't lose any data I had there. (thank you btw for this project :)).