[BUG] dev-0.4.5 fails to start, with "duplicate column name: label"

[ThisIsTheOnlyUsernameAvailable]

Describe the bug
I upgraded from the LSIO image, to the official container and using my own Influx instance. The first start saw a huge number of SQL statements (I'm assuming extracting from SQLite to Influx, but then failed. Subsequent restarts also panic. Logs below.

Expected behavior
Data should migrate successfully, and Scrutiny should then start.

Screenshots
N/A

Log Files

scrutiny2    | 2022/05/21 11:32:13 Loading configuration file: /opt/scrutiny/config/scrutiny.yaml
scrutiny2    | time="2022-05-21T11:32:13+10:00" level=info msg="Trying to connect to scrutiny sqlite db: /opt/scrutiny/config/scrutiny.db\n"
scrutiny2    | 
scrutiny2    |  ___   ___  ____  __  __  ____  ____  _  _  _  _
scrutiny2    | / __) / __)(  _ \(  )(  )(_  _)(_  _)( \( )( \/ )
scrutiny2    | \__ \( (__  )   / )(__)(   )(   _)(_  )  (  \  /
scrutiny2    | (___/ \___)(_)\_)(______) (__) (____)(_)\_) (__)
scrutiny2    | github.com/AnalogJ/scrutiny                             dev-0.4.5
scrutiny2    | 
scrutiny2    | Start the scrutiny server
scrutiny2    | [GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production.
scrutiny2    |  - using env:	export GIN_MODE=release
scrutiny2    |  - using code:	gin.SetMode(gin.ReleaseMode)
scrutiny2    | 
scrutiny2    | time="2022-05-21T11:32:13+10:00" level=info msg="Successfully connected to scrutiny sqlite db: /opt/scrutiny/config/scrutiny.db\n"
scrutiny2    | time="2022-05-21T11:32:13+10:00" level=info msg="Database migration starting"
scrutiny2    | time="2022-05-21T11:32:13+10:00" level=error msg="Database migration failed with error: %!w(sqlite3.Error={1 1 0 duplicate column name: label})"
scrutiny2    | 
scrutiny2    | 2022/05/21 11:32:13 /go/src/github.com/analogj/scrutiny/webapp/backend/pkg/database/scrutiny_repository_migrations.go:62 duplicate column name: label
scrutiny2    | [0.011ms] [rows:0] ALTER TABLE `devices` ADD `label` text
scrutiny2    | panic: duplicate column name: label
scrutiny2    | 
scrutiny2    | goroutine 1 [running]:
scrutiny2    | github.com/analogj/scrutiny/webapp/backend/pkg/web/middleware.RepositoryMiddleware({0x10b0480, 0xc000118080}, {0x10b9b50, 0xc0004148c0})
scrutiny2    | 	/go/src/github.com/analogj/scrutiny/webapp/backend/pkg/web/middleware/repository.go:14 +0xa5
scrutiny2    | github.com/analogj/scrutiny/webapp/backend/pkg/web.(*AppEngine).Setup(0xc000115f60, {0x10b9b50, 0xc0004148c0})
scrutiny2    | 	/go/src/github.com/analogj/scrutiny/webapp/backend/pkg/web/server.go:26 +0xb4
scrutiny2    | github.com/analogj/scrutiny/webapp/backend/pkg/web.(*AppEngine).Start(0xc000115f60)
scrutiny2    | 	/go/src/github.com/analogj/scrutiny/webapp/backend/pkg/web/server.go:97 +0x3ab
scrutiny2    | main.main.func2(0xc000121400)
scrutiny2    | 	/go/src/github.com/analogj/scrutiny/webapp/backend/cmd/scrutiny/scrutiny.go:112 +0x1f7
scrutiny2    | github.com/urfave/cli/v2.(*Command).Run(0xc000418240, 0xc000121280)
scrutiny2    | 	/go/src/github.com/analogj/scrutiny/vendor/github.com/urfave/cli/v2/command.go:164 +0x64a
scrutiny2    | github.com/urfave/cli/v2.(*App).RunContext(0xc000424000, {0x109a750, 0xc00003c030}, {0xc000032040, 0x2, 0x2})
scrutiny2    | 	/go/src/github.com/analogj/scrutiny/vendor/github.com/urfave/cli/v2/app.go:306 +0x926
scrutiny2    | github.com/urfave/cli/v2.(*App).Run(...)
scrutiny2    | 	/go/src/github.com/analogj/scrutiny/vendor/github.com/urfave/cli/v2/app.go:215
scrutiny2    | main.main()
scrutiny2    | 	/go/src/github.com/analogj/scrutiny/webapp/backend/cmd/scrutiny/scrutiny.go:137 +0x679

docker-compose.yaml

---
version: "2.1"
services:
  scrutiny2:
    image: ghcr.io/analogj/scrutiny:master-web
    container_name: scrutiny2
    environment:
      - SCRUTINY_API_ENDPOINT=http://localhost:8080
    volumes:
      - /appdata/scrutiny/config:/opt/scrutiny/config
    ports:
      - 8080:8080
    restart: unless-stopped

scrutiny.yaml:

version: 1

web:
  listen:
    port: 8080
    host: 0.0.0.0
  database:
    location: /opt/scrutiny/config/scrutiny.db
  src:
    frontend:
      path: /app/scrutiny-web

  influxdb:
    host: <ipaddress>
    port: 8086
    token: <token>
    org: <org>
    bucket: 'scrutiny'
    retention_policy: true

disks:
  include:
    # - /dev/sda
  exclude:
    # - /dev/sdb

notify:
  level: 'warn' # 'warn' or 'error'
  urls:
    - "discord://token@channel"
    - "telegram://token@telegram?channels=channel-1[,channel-2,...]"
    - "pushover://shoutrrr:apiToken@userKey/?devices=device1[,device2, ...]"
    - "slack://[botname@]token-a/token-b/token-c"
    - "smtp://username:password@host:port/?fromAddress=fromAddress&toAddresses=recipient1[,recipient2,...]"
    - "teams://token-a/token-b/token-c"
    - "gotify://gotify-host/token"
    - "pushbullet://api-token[/device/#channel/email]"
    - "ifttt://key/?events=event1[,event2,...]&value1=value1&value2=value2&value3=value3"
    - "mattermost://[username@]mattermost-host/token[/channel]"
    - "hangouts://chat.googleapis.com/v1/spaces/FOO/messages?key=bar&token=baz"
    - "zulip://bot-mail:bot-key@zulip-domain/?stream=name-or-id&topic=name"
    - "join://shoutrrr:api-key@join/?devices=device1[,device2, ...][&icon=icon][&title=title]"
    - "script:///file/path/on/disk"
    - "https://www.example.com/path"

collect:
  metric:
    enable: true
    command: '-a -o on -S on'
  long:
    enable: false
    command: ''
  short:
    enable: false
    command: ''

[davehope]

Moved from using the official web-only container to the master-omnibus container and get the same problem migrating from sqlite.

[ovizii]

Exact same problem here. Moved from lsio to master-omnibus container and get the same problem migrating from SQLite.

btw. my lsio scrutiny.yaml was missing this part - do I just insert it as is? Any tips for migrating from lsio to this official version?

  # if you're running influxdb on a different host (or using a cloud-provider) you'll need to update the host & port below.
  # token, org, bucket are unnecessary for a new InfluxDB installation, as Scrutiny will automatically run the InfluxDB setup,
  # and store the information in the config file. If you 're re-using an existing influxdb installation, you'll need to provide
  # the `token`
  influxdb:
#    scheme: 'http'
    host: 0.0.0.0
    port: 8086
#    token: 'my-token'
#    org: 'my-org'
#    bucket: 'bucket'
    retention_policy: true

[AnalogJ]

Hey @ThisIsTheOnlyUsernameAvailable , @davehope, @ovizii

Unfortunately it sounds as if the migration script ran into an error and your database was only partially migrated from the old v0.3.x to v0.4.x version.

Can you attach a copy of your scrutiny.db file to this issue? or email it to me at jason@thesparktree.com? I may be able to "fix" it, and send it back to you. It'll also help me figure out whats going on with the migration script.

[AnalogJ]

While I wait for you to send me your broken database files, I've made a fix to ensure that this doesn't happen to other users in the future.
I've modified the migration script to run in a transaction, so any failure will cause a rollback to occur automatically.

However, this will not fix the issue for you, as your database is already in a "failed" state.

For the 3 of you (and anyone else that is running into this issue), there are 2 options:

• shut down Scrutiny, and delete your scrutiny.db file. You'll loose all your historical data, but Scrutiny will start up with a fresh database, and work as normal. (Please email me a copy of your database before you delete it, even if you don't want me to repair it).
• wait for me to respond with your "repaired" database.

[davehope]

Thanks @AnalogJ , very kind. Database is on its way via e-mail to you.

[AnalogJ]

I've implemented a fix for this in linked commit.
Basically the issue is that InfluxDB has a retention policy for the various data buckets that Scrutiny creates.
During migration from SQLite to InfluxDB, we calculate the correct bucket to upload data to, however, if it just crosses the boundary (by minutes or hours) it will cause influxdb to throw an error.

I've fixed this by ignoring retention policy related errors.

@davehope I'll be emailing you soon with a zip file containing a migrated sqlite.db file and associated influxdb database.

[ovizii]

Hey @ThisIsTheOnlyUsernameAvailable , @davehope, @ovizii

Unfortunately it sounds as if the migration script ran into an error and your database was only partially migrated from the old v0.3.x to v0.4.x version.

Can you attach a copy of your scrutiny.db file to this issue? or email it to me at jason@thesparktree.com? I may be able to "fix" it, and send it back to you. It'll also help me figure out whats going on with the migration script.

I just sent you a copy of mine. If it's an easy fix I'd appreciate that, if not, I'll scrap it.

[davehope]

(confirmed on e-mail, but posting here too).

Very kind of you to fix the database, really not expected at-all.

[Fondlylinuxusr323]

Hi there! Could i maybe also get an email with the fix? Been struggling with this issue for a while now.