Docker latest version migration issue

[kristijanPetr]

when running the migrations throws an error, probably because isPinned column is already defined in Snippet model.

this is happening when pulling latest image: pawelmalak/snippet-box:latest

[pawelmalak]

Is this a new instance or are you updating an existing one? I created a new one, restarted it 10 times and can't reproduce it.

[ServerGeek82]

This is a new installation on my server running the below command.

user@hostname: sudo docker run -p 5000:5000 -v snippet_box:/app/data pawelmalak/snippet-box:latest
[2021-11-21 23:53:10.809 UTC+0] [INFO] db: Database connected
[2021-11-21 23:53:10.848 UTC+0] [INFO] db: Found pending migrations. Executing...
[2021-11-21 23:53:10.871 UTC+0] [ERROR] db: Database connection error
user@hostname:

[pawelmalak]

Can you open your database file with DB Browser for SQLite and show me content of SequelizeMeta table content and also schema of snippets table?

[ServerGeek82]

container never successfully starts, only logs are above in the container, never fully starts.

[pawelmalak]

Ok. Can you add -e NODE_ENV=development to your run command and check if something new shows up in the logs?

[pawelmalak]

I was just able to reproduce this error on my side. Can you try to mount container data to host directory with path, instead of named volume?

[ServerGeek82]

[sudo] password for w3bfly:
[2021-11-22 00:25:58.403 UTC+0] [INFO] db: Database connected
[2021-11-22 00:25:58.455 UTC+0] [INFO] db: Found pending migrations. Executing...
[2021-11-22 00:25:58.455 UTC+0] [DEV] db: Executing 01_pinned_snippets.js migration
[2021-11-22 00:25:58.455 UTC+0] [DEV] db: Executing 02_tags.js migration
[2021-11-22 00:25:58.501 UTC+0] [ERROR] db: Database connection error
DatabaseError [SequelizeDatabaseError]: SQLITE_ERROR: duplicate column name: isPinned
    at Query.formatError (/app/node_modules/sequelize/lib/dialects/sqlite/query.js:403:16)
    at Query._handleQueryResponse (/app/node_modules/sequelize/lib/dialects/sqlite/query.js:72:18)
    at afterExecute (/app/node_modules/sequelize/lib/dialects/sqlite/query.js:238:27)
    at Statement.errBack (/app/node_modules/sqlite3/lib/sqlite3.js:14:21)
From previous event:
    at /app/node_modules/umzug/lib/index.js:116:62
    at processImmediate (internal/timers.js:464:21)
From previous event:
    at /app/node_modules/umzug/lib/index.js:112:43
From previous event:
    at Umzug.execute (/app/node_modules/umzug/lib/index.js:112:9)
    at Umzug._run (/app/node_modules/umzug/lib/index.js:279:19)
    at Umzug.<anonymous> (/app/node_modules/umzug/lib/index.js:307:21)
From previous event:
    at Umzug._run (/app/node_modules/umzug/lib/index.js:306:10)
    at Umzug.up (/app/node_modules/umzug/lib/index.js:198:17)
    at /app/build/db/index.js:54:21
    at Generator.next (<anonymous>)
    at fulfilled (/app/build/db/index.js:5:58) {
  parent: [Error: SQLITE_ERROR: duplicate column name: isPinned] {
    errno: 1,
    code: 'SQLITE_ERROR',
    sql: 'ALTER TABLE `snippets` ADD `isPinned` INTEGER DEFAULT 0;'
  },
  original: [Error: SQLITE_ERROR: duplicate column name: isPinned] {
    errno: 1,
    code: 'SQLITE_ERROR',
    sql: 'ALTER TABLE `snippets` ADD `isPinned` INTEGER DEFAULT 0;'
  },
  sql: 'ALTER TABLE `snippets` ADD `isPinned` INTEGER DEFAULT 0;',
  parameters: undefined
}
user@hostname:~$

[ServerGeek82]

`user@hostname:~$ sudo docker run -p 5000:5000 -v /home/docker/snippet_box:/app/data pawelmalak/snippet-box:latest
[sudo] password for user:
[2021-11-22 00:46:31.843 UTC+0] [INFO] db: Database connected
[2021-11-22 00:46:31.882 UTC+0] [INFO] db: Found pending migrations. Executing...
[2021-11-22 00:46:31.961 UTC+0] [INFO] server: Server is working on port 5000 in production mode

`

[ServerGeek82]

Works without a named volume

and if it matters i created the volume from command line with this sudo docker volume create snippet_box

[pawelmalak]

I created one with Docker Desktop on Windows and it gave me the same error but when I provided normal path it worked. That would also explain why I wasn't able to reproduce it earlier.

Also if you want to format block of code in Markdown you can use ``` instead of a single one `.

@kristijanPetr Did you originally try to run container with a named volume?

[ServerGeek82]

so far still running nice and clean, love the interface.

[JochenFroehlich]

Will there be a fix to let the container run with named volumes?

[pawelmalak]

I'm still trying to figure out why is it happening only with named volumes and how to fix it.

[guidopetri]

After spending the last ~3 hours googling, I decided to run a couple of experiments:

1. bind mount
2. named volume pointing to local FS
3. named volume (without specific location in FS)
4. bind mount pointing to a folder owned by root:root
5. bind mount to local path owned by $USER, then switch to named volume pointing to same local FS path

1 worked as expected, and 2 failed with the above error, as did 3. The interesting one is 4: it ALSO had the same error. 5 did NOT have the error.

I believe this is because volumes are managed by Docker and thus there's a permissions error somewhere when running the migrations. This is supported by scenario 4: the bind mount doesn't have perms on the local FS, and yields the same error. I'm not sure why this is the case, however.

There's also interesting behavior if there's a bind mount on a folder owned by $USER, and $USER also owns the sqlite db within: the same error occurs. The "golden scenario" is when $USER owns the folder, and root owns the sqlite db (which is the default). I also tried creating a named volume set to bind to the local FS and setting the owners correctly, but this did not seem to work either. (it defaults to root owning the folder and the db)

One (ugly) alternative in 5 is to create the db with a bind mount, then edit the compose file/docker run command to use a named volume. This seems to work just fine, though I haven't tested if it saves changes to the db in this scenario.

Unfortunately, I can't find anyone else with this error: the only StackOverflow post on the topic is the owner of the repo themselves, it seems, and doesn't have an answer. :/